How to import data to Excel through Matlab

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.

Answer 1

[the cyclist]

Have you tried the xlsread() and xlswrite() commands?

[Poul Reitzel]

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]

@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]

@Fangjun Jiang: In this way Matlab interprts the .dat file as having only one column.

Answer 2

[Fangjun Jiang]

Or use the Excel COM server.

http://www.mathworks.com/help/techdoc/matlab_external/bqdwu3j.html#bqd_o32

[Poul Reitzel]

Definately looks like there's some potential here.. i will have a look later as soon as i have time! :)

Answer 3

[William]

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.

[Poul Reitzel]

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]

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]

No. The program that generates the data is made by Georgia Tech. I can't edit the source code.

Answer 4

[Image Analyst]

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;

[Poul Reitzel]

Cheers, I will have a look into it and get back with the result.

[the cyclist]

Should this be expected to work on a Mac, or only on Windows?

[Poul Reitzel]

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]

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."

Answer 5

[Poul Reitzel]

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

Answer 6

[Sarah Palfreyman]

See extractFileText in Text Analytics Toolbox