Readtable can not handle with multiple sheets?

510 Ansichten (letzte 30 Tage)
John Hoop
John Hoop am 6 Mai 2020
Bearbeitet: monkeyquant am 3 Nov. 2022
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.
  2 Kommentare
Guillaume
Guillaume am 6 Mai 2020
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
John Hoop am 6 Mai 2020
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.

Melden Sie sich an, um zu kommentieren.

Akzeptierte Antwort

Emmanuel Olivar
Emmanuel Olivar am 6 Mai 2020
You can use readtable documentation:
and check their arguments:
If you need read an specific sheet of your file use the 'sheet' argument:
myTable = readtable('My_file.xlsx','Sheet','SheetName')
  1 Kommentar
John Hoop
John Hoop am 6 Mai 2020
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!

Melden Sie sich an, um zu kommentieren.

Weitere Antworten (1)

monkeyquant
monkeyquant am 3 Nov. 2022
Bearbeitet: monkeyquant am 3 Nov. 2022
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.

Community Treasure Hunt

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

Start Hunting!

Translated by