How to import data to Excel through Matlab

41 views (last 30 days)
I am designing a program in Matlab to filter output data from a program called GTStrudl.
Currently I copy/paste my data from GTStrudl to a .dat file, and then import that .dat file into Excel by executing Excel and using the "data connection" option in the Excel environment (the reason for opening the file in Excel is, that if I paste the data from GTstrudl straight into Excel it will all end up in one column). The .dat file is very unstructured, contains a mix of numbers and letters, up to about 170000 rows and 18 columns.
I then read the Excel file in my program in Matlab, filter the data, and write it back to the same Excel sheet, in which the raw unfiltered data is. However, I would like to avoid the data import in Excel. Is there any way to make Matlab open Excel (in the "background"), import my data from the .dat file and save and close? The final goal is to compile the program to an .exe file with a GUI so the whole filter proces can be done in one interface.

Answers (6)

the cyclist
the cyclist on 14 Oct 2011
Have you tried the xlsread() and xlswrite() commands?
  3 Comments
Poul Reitzel
Poul Reitzel on 19 Oct 2011
@Fangjun Jiang: In this way Matlab interprts the .dat file as having only one column.

Sign in to comment.


Fangjun Jiang
Fangjun Jiang on 14 Oct 2011
  1 Comment
Poul Reitzel
Poul Reitzel on 14 Oct 2011
Definately looks like there's some potential here.. i will have a look later as soon as i have time! :)

Sign in to comment.


William
William on 14 Oct 2011
If you use cvswrite(file_name, data_matrix)? it will create an excel sheet and then write "data_matrix" into it. you need not open excel up for this to work.
  3 Comments
Poul Reitzel
Poul Reitzel on 17 Oct 2011
No. The program that generates the data is made by Georgia Tech. I can't edit the source code.

Sign in to comment.


Image Analyst
Image Analyst on 17 Oct 2011
Poul:
Try this demo. It launches Excel, creates a workbook, creates worksheets, sends over some data, then saves the workbook and exits. It uses the ActiveX protocol, which is what you need to do if you're going to read and write stuff to Excel with any speed. The only thing you'll need to run the demo, other than what's below, is xlswrite1() from the File Exchange (link is supplied in the code). Save the code below as ExcelDemo.m and run it.
% Demo macro to write numerical arrays and cell arrays
% to two different worksheets in an Excel workbook file.
% Uses xlswrite1, available from the File Exchange
% http://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1
% By ImageAnalyst
function ExcelDemo
clc;
close all;
clear all;
fullFileName = GetXLFileName();
if isempty(fullFileName)
% User clicked Cancel.
return;
end
Excel = actxserver('Excel.Application');
if ~exist(fullFileName, 'file')
message = sprintf('I am going to create Excel workbook:\n\n%s\n\nClick OK to continue.\nClick Exit to exit this function', fullFileName);
button = questdlg(message, 'Creating new workbook', 'OK', 'Exit', 'OK');
drawnow; % Refresh screen to get rid of dialog box remnants.
if strcmpi(button, 'Exit')
return;
end
% Add a new workbook.
ExcelWorkbook = Excel.workbooks.Add;
% Save this workbook we just created.
ExcelWorkbook.SaveAs(fullFileName,1);
ExcelWorkbook.Close(false);
end
% Open up the workbook named in the variable fullFileName.
invoke(Excel.Workbooks,'Open',fullFileName);
% Create some sample data.
myData = magic(20);
myOtherData = rand(10);
% Then run the new xlswrite1 function as many times as needed or in a loop
% (for example xlswrite1(fullFileName, yourArrayName, XL_CellLocation).
xlswrite1(fullFileName, myData, 'mySheetName', 'B2');
ca = {'Column Header 1', 'Column Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'B1');
ca = {'Row Header 1'; 'Row Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'A2');
xlswrite1(fullFileName, myOtherData, 'myOtherSheetName', 'B2');
% Delete all empty sheets in the active workbook.
DeleteEmptyExcelSheets(Excel);
% Then run the following code to close the activex server:
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit;
Excel.delete;
clear Excel;
message = sprintf('Done!\nThis Excel workbook has been created:\n%s', fullFileName);
msgbox(message);
% End of main function: ExcelDemo.m -----------------------------
%--------------------------------------------------------------------
% Gets the name of the workbook from the user.
function fullExcelFileName = GetXLFileName()
fullExcelFileName = []; % Default.
% Ask user for a filename.
FilterSpec = {'*.xls', 'Excel workbooks (*.xls)'; '*.*', 'All Files (*.*)'};
DialogTitle = 'Save workbook file name';
% Get the default filename. Make sure it's in the folder where this m-file lives.
% (If they run this file but the cd is another folder then pwd will show that folder, not this one.
thisFile = mfilename('fullpath');
[thisFolder, baseFileName, ext] = fileparts(thisFile);
DefaultName = sprintf('%s/%s.xls', thisFolder, baseFileName);
[fileName, specifiedFolder] = uiputfile(FilterSpec, DialogTitle, DefaultName);
if fileName == 0
% User clicked Cancel.
return;
end
% Parse what they actually specified.
[folder, baseFileName, ext] = fileparts(fileName);
% Create the full filename, making sure it has a xls filename.
fullExcelFileName = fullfile(specifiedFolder, [baseFileName '.xls']);
% --------------------------------------------------------------------
% DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
% This function looped through all sheets and deletes those sheets that are
% empty. Can be used to clean a newly created xls-file after all results
% have been saved in it.
function DeleteEmptyExcelSheets(excelObject)
% excelObject = actxserver('Excel.Application');
% excelWorkbook = excelObject.workbooks.Open(fileName);
worksheets = excelObject.sheets;
sheetIdx = 1;
sheetIdx2 = 1;
numSheets = worksheets.Count;
% Prevent beeps from sounding if we try to delete a non-empty worksheet.
excelObject.EnableSound = false;
% Loop over all sheets
while sheetIdx2 <= numSheets
% Saves the current number of sheets in the workbook
temp = worksheets.count;
% Check whether the current worksheet is the last one. As there always
% need to be at least one worksheet in an xls-file the last sheet must
% not be deleted.
if or(sheetIdx>1,numSheets-sheetIdx2>0)
% worksheets.Item(sheetIdx).UsedRange.Count is the number of used cells.
% This will be 1 for an empty sheet. It may also be one for certain other
% cases but in those cases, it will beep and not actually delete the sheet.
if worksheets.Item(sheetIdx).UsedRange.Count == 1
worksheets.Item(sheetIdx).Delete;
end
end
% Check whether the number of sheets has changed. If this is not the
% case the counter "sheetIdx" is increased by one.
if temp == worksheets.count;
sheetIdx = sheetIdx + 1;
end
sheetIdx2 = sheetIdx2 + 1; % prevent endless loop...
end
excelObject.EnableSound = true;
return;
  4 Comments
the cyclist
the cyclist on 19 Oct 2011
My question was ambiguous. I was asking Image Analyst whether his solution would work on a Mac. Unfortunately, in the meantime, I've learned that the answer is almost certainly "no."

Sign in to comment.


Poul Reitzel
Poul Reitzel on 19 Oct 2011
I have included an extract of my code below where the Excel sheet to write the filtered data to is created. The VBA code i want to run in Excel is included below the extract from Matlab. Is it possible to run that piece of VBA code in Matlab? If yes, then how would it look (just in general if you're in a hurry) ------------
Extract from my code in Matlab:
% UI: Name the .xlsx file to write to
[xlfile, xlpath] = uiputfile('*.xlsx', 'Save Workspace as'); Inputsheet=1;
if isequal(xlfile,0) || isequal(xlpath,0)
disp('User pressed cancel')
else
disp(['User selected ', fullfile(xlpath, xlfile)])
end
filename = [xlpath xlfile]; % file directory
xlswrite(filename,1) % Write a number to the file to make it physical
% Make sure the desired output sheet is clean
% Retrieve sheet names
[~, sheetNames] = xlsfinfo(filename);
% Open Excel as a COM Automation server
Excel = actxserver('Excel.Application');
% Open Excel Workbook
Workbook = Excel.Workbooks.Open(filename);
% Clear the content of the desired output sheet
cellfun(@(x) Excel.ActiveWorkBook.Sheets.Item(x).Cells.Clear, sheetNames(Inputsheet));
------------------
VBA code from Excel
Sub Makro2()
'
' Makro2 Makro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\isc.ISC1\Desktop\Matlab Programs\GTstrudl Output Postprocessing\WD-ULS-PUNCHING-JACKET-WEAK-LRFD.DAT" _
, Destination:=Range("$A$1"))
.Name = "WD-ULS-PUNCHING-JACKET-WEAK-LRFD_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(11, 7, 11, 19, 8, 10, 8, 10, 8, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Sarah Palfreyman
Sarah Palfreyman on 30 Apr 2018
See extractFileText in Text Analytics Toolbox

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!