How to Save Different Imported Excel Sheet Data to Same Variable

Hello,

I have several Excel sheets that I plan to import into Matlab one at a time to analyze individually (i.e. I'll import an Excel table, run my script. Then delete everything in the Workspace, import another Excel table, re-run my script. Etc.).

To preserve the original table, I save the table to a variable called 'T1'.

Each imported Excel sheet will have a different name, something like "Apple_Table_01", "Banana_Table_02", etc.

Currently, I have something like this:

T1 = Apple_Table_01

and then I'll erase 'Apple_Table_01' and type in 'Banana_Table_02' for the next time I need to run the case.

What I want is someting like this:

T1 = (Imported Table)

where (Imported Table) will automatically recognize to use the Excel table, no matter the name.

Is this possible?

I can explain further if this doesn't make sense.

Thanks.

Answer 1

[Cris LaPierre]

I would create a list of my sheets, and then use a loop to load them. I would assign the output of the readtable command to T1.

sheets = ["Apple_Table_01","Banana_Table_02"];
for s = 1:length(sheets)
    T1 = readtable("fileName.xlsx","Sheet", sheets(s));
    ...
end

[Jon]

Thanks for the input.

I guess with that method, you would have to know the names of all the Excel sheets beforehand, not as much as hands-free as I was hoping but I figured it might have been a tall order

[Image Analyst]

Once you know the name of the workbook, you can get the sheetnames from the sheetnames function.

help sheetnames

  SHEETNAMES(FILENAME) returns the sheet names from the given spreadsheet FILENAME
    FILENAME: If the file is not on the MATLAB path, you must specify the
    full path to the file on the local machine or as an URL for a remote file
 
    Example:
    --------
    sheets = sheetnames('testData.xlsx');% File is on MATLAB path
    sheets = sheetnames('C:\Users\username\Desktop\testData.xlsx'); % Absolute path to file
    sheets = sheetnames('s3://bucketname/path_to_file'); % Remote s3 file

    Documentation for sheetnames
       doc sheetnames

    Other uses of sheetnames

       matlab.io.datastore.SpreadsheetDatastore/sheetnames

[Cris LaPierre]

Also note that you can use numbers instead of names as the sheet name-value pair.

• Worksheet to read, specified as the comma-separated pair consisting of 'Sheet' and a positive integer indicating the worksheet index or a character vector or string containing the worksheet name. The worksheet name cannot contain a colon (:). To determine the names of sheets in a spreadsheet file, use sheets = sheetnames(filename). For more information, see sheetnames.
• Example: 'Sheet', 2
• Example: 'Sheet', 'MySheetName'

You can find a description of this and other input arguments for spreadsheets here: https://www.mathworks.com/help/matlab/ref/readtable.html#mw_0a3219fd-d40a-46a9-b230-7e249002f7b1