MATLAB: Can readtable read variable opt in a loop

readtablexlsm

I have a lot of xlsm files to process.

Essentially I have a loop that searched for sheet name and column name that satisfies certain criteria and retrieve and store the sheet name and column, for example in a particular loop, perhaps,

SheetName = “1”; Col = “BH”;

Can I set these variable as options to readtable?

Best Answer

Yes.

https://www.mathworks.com/help/matlab/ref/readtable.html#d120e1047964

'sheet' and 'range' are both valid for "spreadsheet" (.xls, .xlsx) files

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

You can rename an Excel sheet using the "Name" property of a sheet object. For example the following renames the first sheet of an Excel file:

filename = 'C:\SomeExcelFile.xls';
% Open Excel Automation server
Excel = actxserver('Excel.Application');
% Make Excel visible
Excel.Visible=1;
% Open Excel file
Workbook = Excel.Workbooks.Open(filename);
% Get the list of sheets in the workbook
Sheets = Excel.ActiveWorkbook.Sheets;
% Rename the first sheet
Sheets.Item(1).Name = 'This is sheet 1';

The following code then saves the Workbook, quits Excel and removes the COM server:

% Save the file
Workbook.Save();
% Quit Excel, remove the COM server and delete the related objects
Excel.Quit();
Excel.delete();
clear Excel;
clear Workbook;
clear Sheets;

MATLAB: Help with readtable opts

Don't try to create the options object from scratch--that rarely, if ever, works because there's a whole lot of other stuff besides just the one or two items that is contained within it and trying to get all the defaulted elements fully consistent with the few you specify is a Herculean task (and best not left to mere mortals :) ).

Use something like:

% query first file for import options 
opts=detectImportOptions(fullfile(d, filedir(1).name));

Then use this import options object for each file inside the loop.

I tried it on your sample and it correctly identified the variable type on its own...

>> opts=detectImportOptions('data2.csv')
opts = 
  DelimitedTextImportOptions with properties:
   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
                EmptyLineRule: 'skip'
                     Encoding: 'windows-1252'
   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'
   Variable Import Properties:   Set types by name using setvartype
                VariableNames: {'time', 'Var1', 'Var2' ... and 8 more}
                VariableTypes: {'datetime', 'double', 'double' ... and 8 more}
        SelectedVariableNames: {'time', 'Var1', 'Var2' ... and 8 more}
              VariableOptions: Show all 11 VariableOptions 
  Access VariableOptions sub-properties using setvaropts/getvaropts
   Location Properties:
                     DataLine: 4
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0
>> t=readtable('data2.csv',opts);
Warning: The DATETIME data was created using format 'MM/dd/uuuu HH:mm' but also matched 'dd/MM/uuuu HH:mm'.
To avoid ambiguity, supply a datetime format using SETVAROPTS, e.g.
  opts = setvaropts(opts,varname,'InputFormat','MM/dd/uuuu HH:mm'); 
> In matlab.io.internal.text.TableParser/readData (line 75)
  In matlab.io.text.TextImportOptions/readtable (line 223) 
>> t(1:5,:)
ans =
  5×11 table
            time               Var1        Var2      Var3    Var4    Var5    Var6    Var7    Var8    Var9    Var10
    ____________________    __________    _______    ____    ____    ____    ____    ____    ____    ____    _____
    05-Jun-2018 12:00:00          1561    -200.25    2.5     2.4     6.5     7       0.1     5.6     6.8     6.1  
    05-Jun-2018 12:00:00    1.5613e+05    -200.25    2.5     2.4     6.5     7       0.1     5.6     6.8     6.1  
    05-Jun-2018 12:00:00         15661    -200.25    2.5     2.4     6.5     7       0.1     5.6     6.7     6.1  
    05-Jun-2018 12:00:00          1656    -200.25    2.5     2.4     6.5     7       0.1     5.6     6.8     6.1  
    05-Jun-2018 12:00:00          1661    -200.25    2.5     2.4     6.5     7       0.1     5.6     6.8     6.1  
>>

NB: the warning on the datetime conversion; this will probably go away on its own when you use real data that will contain unambiguous day/month values, but you can set the actual format explicitly and be sure.

You must do this with the setvartype function; see documentation for examples, syntax.

It appears from the data that perhaps it would be a better choice to make Var1 and perhaps Var6 categorical instead of double; you'll know their meaning better than we.

Best Answer

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

MATLAB: Help with readtable opts

Related Question