Readtable can not handle with multiple sheets?

I tried to get data via Readtable from Excel. Readtable is extremely useful when you want to include variable names and row names with data values. It works perfectly for any ranges on the same sheet. But it failed when I tried to read data from a range on a different sheet. Does someone have similar experience and what would be good work aorund to deal with this? Ofcourse, I can use readmatrix or readcell, but then I will need to manupulate things to get the information of variable names etc.

[Guillaume]

Can you describe the problem in more details, in particular what you mean by a range on a different sheet? When it comes to reading from spreadsheets, the only difference between readtable, readmatrix and readcell is how they return the data. They all use the same underlying reading code and have the same options for selecting ranges and sheets.

[John Hoop]

Thanks! You are absolutely right! The problem is now solved. I initially only use Range as argument and not Sheet, which works fine for readtable. This is because readtable uses the first sheet as argument by default (which I did not notice). In fact, the specification of Sheet is always required even I thought it was not the case. This was also the cause of my problem.

Answer 1

[Emmanuel Olivar]

You can use readtable documentation:

https://la.mathworks.com/help/matlab/ref/readtable.html

and check their arguments:

https://la.mathworks.com/help/matlab/ref/readtable.html#namevaluepairarguments

If you need read an specific sheet of your file use the 'sheet' argument:

myTable = readtable('My_file.xlsx','Sheet','SheetName')

[John Hoop]

Hi Emmanuel, Thanks! You are right. I can assign the specific sheet to it. It works! I thought it was not possible to have "sheet" as an extra argument. But, it can. This is really great!

Answer 2

[monkeyquant]

In my case, I use the first sheet (or any sheet) to identify sheet name, ranges (first cell and last cell) for each sheet. I even identify variable names in MATLAB from Excel to assign values in Excel Ranges to MATLAB table, Cell, or Struct. I however use table since it seems MATLAB promotes readtable over other approaches. Specifically, it seems xlsread or actxserver doesn't support excel files from OneDrive or SharePoint (I must be wrong and hope I am wrong on this statement becasue actxserver is very familar to Excel VBA users.) I then loop through the number of tables (ranges) to import. Instead using array2table or cell2table or similar operation to assign variable's name, I use assignin to assign ranges to predefined variables assigned to a cell array or some other types against all advices (sorry guys... I want to experience the drawbacks you guys mentioned - I know it is rebellious!!, lol.) To make simple, I specify ReadRowNames column number, and ReadVariableNames true.

for i = 2:n
    % just in cases your cell addresses contains Excel Cell absolute sign'$'
    firstCell = string(erase(lqPivot{1, (i-1)}, '$'));
    lastCell = string(erase(lqPivot{2, (i-1)}, '$'));
    
    % defined tDelimiter = ':' earlier
    % Use strcat because [ ] or + doesn't work with cell array somehow
    rngArea = strcat(firstCell, tDelimiter, lastCell);
    
    % I take this approach, so I don't need to change other codes for now (oh, well)
    assignin('caller', varNames{i}, readtable(tFullName, 'Sheet', tSheetNames{i}, 'Range', rngArea,'ReadRowNames',1, 'ReadVariableNames', true));
end

I then save these variables into mat file with again dynamically assigned. I do this for my own purpose to manage tables which will be used in main calculation processes including cash flow generation. I cannot reveal my specific reason behind against all advices. I don't let users plug parameters specified by financial instuments or similar products. Such data will be managed by certain people and they will validate such data with the legal documents.

I am going to deploy MATLAB portion to compiler and users will see one-sheet Excel and multiple-sheet Excel contains all data. Unfortunately I couldn't find any way to utilize ListObjects and its name in readtable MATLAB function. It seems it take ranges only, not even Named Range (global variable in Excel). You need a little work to create range with first cell address and last cell address imported from Excel. I hope this help and any of you keeps throw any ideas.