Looping through different sheets/sheet names in each loop to access data

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

Answer 1

[dpb]

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.

[Cameron Kirk]

Absolute Legend!

Thanks so much for this I've been struggling for a couple of days

Have a great day!