Looping through different sheets/sheet names in each loop to access data
26 views (last 30 days)
Show older comments
Hi,
I am trying to load data from 12 Excel Documents into matlab. Each Excel document contains a varying number of sheets, with the first sheet of every document named 'Summary' and then the sheets are named Trial1, Trial2, Trial3 etc.... Some documents go up to 29 trials, others only 20.
I am struggling now, as I need to loop through automatically and access the sheets one at a time.
The loop here is first looping over all of my subjects, which it does correctly.
In the second loop, first I create a 1x29 cell (SheetNames), that contains all of the names of my sheets. Now I'm trying to use my index (iSheetData) to access the sheet name that corresponds to 1 in my SheetName cell on the first iteration which is the summary sheet, then 2 on the second iteration which is Trial1 etc.... the loop index will change everytime and mean that I will access the next sheet on the next loop. I am now stuck on this with the message:
Error using readtable (line 216)
Sheet name '({SheetNames}.(iSheetData}' does not exist or is not supported. To
check if the sheet is supported, specify the sheet by its worksheet index.
If someone could please help!
DirectoryPath_Project = 'M:\Work Packages\WP2\Task 2.1.2 - Data UNIBO\Standardization\EPFL\EPFL Data-Rewire-Stroke\Original'; % folder to the project's data
cd(DirectoryPath_Project)
FilePatients = dir(DirectoryPath_Project); % Patient1,Patient2,Patient3,Patient4,Patient5,Patient6,Patient7,Patient8,Patient9,Patient10,Patient11,Patient12
ExcelFilePatients = dir( '*.xls');
FilePatientsSize = size(FilePatients,1);
% Other directories
DirectoryPath_Standardized = 'M:\Work Packages\WP2\Task 2.1.2 - Data UNIBO\Standardization\EPFL\EPFL Data-Rewire-Stroke\Standardized';
%Create loop over all subjects
for iExcelSubject = 1:size(ExcelFilePatients,1);
GaitReport = ExcelFilePatients(iExcelSubject).name; %loads gait file
[~,SheetNames] = xlsfinfo(GaitReport); %Takes all sheet names from the excel document
nSheets = size(SheetNames);
for iSheetData = 1:nSheets
SheetData = readtable(GaitReport,'sheet','({SheetNames}.(iSheetData}');
end
end
0 Comments
Accepted Answer
dpb
on 11 Feb 2020
Edited: dpb
on 11 Feb 2020
You've embedded the text string '({SheetNames}.(iSheetData}' in the readtable call instead of using the variable Sheetnames. Just dereference the cellstr array:
SheetData = readtable(GaitReport,'sheet',SheetNames{iSheetData});
NB:
If the data are in sequential sheets from the beginning, then you can ignore what the name of the sheet is and just iterate over the number:
SheetData = readtable(GaitReport,'sheet',iSheetData);
NB2:
nSheets = size(SheetNames);
returns a 2-element vector, not just the number of elements in the array. More robust code would be
nSheets=numel(SheetNames);
While is tempting to use length (and here it will return the right answer), it is a dangerous function because it returns max(size(x)) not necessarily the number of rows in x if x is not a column vector or columns if not row vector.
More Answers (0)
See Also
Categories
Find more on Environment and Settings in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!