MATLAB Answers

How to import data to Excel through Matlab

53 views (last 30 days)
Poul Reitzel
Poul Reitzel on 14 Oct 2011
Answered: Sarah Palfreyman on 30 Apr 2018
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.

  0 Comments

Sign in to comment.

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 14 Oct 2011
I use xlsread to read to Excel file, yes. Finally, I use xlswrite to write my filtered data back to the same file from which i read the unfiltered data. Here are the steps I go though
1. Copy/paste output data from GTStrudl to a file and save it as test.dat
2. Open Excel and import data in test.dat file to 'sheet 1'
3. Save Excel as strudldata.xlsx
4. Close Excel
5. Open Matlab and run my filter program
6. Open Excel and read filtered data in 'sheet 2'
I would like to skip step 2, 3 and 4 by controlling what happens in Excel via my Matlab program. I hope this can clarify my question.
Fangjun Jiang
Fangjun Jiang on 14 Oct 2011
@Poul, You might want to consider building a direct link between GUStrudl and MATLAB. If not, MATLAB can import data directly from Clipboard. If you run uiimport, you'll see that option at the first dialog. Once you go through the uiimport() once, it can generate the code for you.
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 14 Oct 2011
Might work. I have had trouble reading the .dat file into Matlab though. Here is an outtake of what's stored in the .dat file (you will see that it's quite messed up, but consists of a mix of strings and numbers)
JOINT CHORD LOAD FINAL----SIZE FX TORSION Pd-AX Md-IPB Md-OPB ACT-NF ACTUAL/ULTIMATE
GEOMETRY BRACE SIN(ANG) OD TK FY MY Puj-AX Muj-IPB Muj-OPB ULT-NF PROVISION NAME
PART AX IY SY FZ MZ
========================================================================================================================
A3-1 24 L1A0-UN 1205.00 55.000 2.3E+05 3.5E+07 2.3E+05 1.4E+08 2.9E+07 0.311
K 32 0.9978 49753.55 -5.1E+07 1.6E+07 9.5E+09 4.9E+09 E.3-1
2.0E+05 3.3E+10 5.5E+07 14191.86 1.3E+08
A3-1 24 L1A0-UN 1205.00 55.000 -2.3E+05 2.6E+07 2.3E+05 2.9E+08 7.3E+07 0.311
K 306 0.9978 77574.80 7.1E+07 1.6E+07 9.5E+09 4.9E+09 E.3-1
2.0E+05 3.3E+10 5.5E+07 21052.30 -2.9E+08
**** WARNING_MSLPSN -- -OVERLAP- APPLIES ONLY TO K-JOINTS; USER SPECIFIED OVERLAP FOR JOINT A3-1 WILL BE IGNORED.
A3-1 24 L1A0-UN 1205.00 55.000 -9.5E+05 -1.5E+07 9.5E+05 1.3E+08 1.4E+08 0.380
K+Y 55 0.8330 36624.68 -1.0E+08 1.9E+07 1.2E+10 6.1E+09 E.3-1
2.0E+05 3.3E+10 5.5E+07 16332.99 1.6E+08
William
William on 17 Oct 2011
Try reading the data in as a string and chopping it up within a subprogram. That way you might be able to sort it better.
could you modify the program that generates this data?
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

Show 1 older comment
the cyclist
the cyclist on 17 Oct 2011
Should this be expected to work on a Mac, or only on Windows?
Poul Reitzel
Poul Reitzel on 19 Oct 2011
Only on Windows. I've been busy at work so far so haven't had the chance to test this. Im on it and I'll get back with my result :)
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

  0 Comments

Sign in to comment.


Community Treasure Hunt

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

Start Hunting!