Create loop for multiple excel sheets/documents

1 view (last 30 days)
Hello everyone!
So I'm trying to extract some data from an multiple seperate documents, with each document containing multiple sheets
1) I have 12 documents in total, each with a varying amount of sheets (on average each one contains 24)
2) These documents obviously contain various column headings for the different variables
I have used the following code:
[~,SheetNames] = xlsfinfo('GaitAnalysisReportP1.xls');
nSheets = 25(SheetNames);
for i=1:nSheets
Name =SheetNames{ii};
data=[Data, xlsread['GaitAnalysisReportP1.xls',Name)];
end
S.Data=Data
To create sheetnames from one subjects data,
How can I loop this now to extract all of the sheets from all of my subjects? (P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12)
Thanks

Accepted Answer

Turlough Hughes
Turlough Hughes on 16 Dec 2019
Edited: Turlough Hughes on 16 Dec 2019
One approach would be to use a nested structure with the first level down having a fieldname for each of the files, and then each file will have a level down for the individual sheets, so that might go something like this:
fils=dir('C:\...\GaitAnalysisReportP*.xls');
foldername=fils(1).folder;
for c=1:length(fils)
fullfname=fullfile(foldername,fils(c).name);
[~,sheet_name] = xlsfinfo(fullfname); %get the sheetnames of the current file
for k = 1:numel(sheet_name)
[data, vnames{k}] = xlsread('examplefile.xlsx',sheet_name{k}); % load
s.(fils(c).name).(sheet_name{k}) = array2table(data,'VariableNames',vnames{k}); %format into a table
end
end
I haven't tested this, let me know how it goes.
  2 Comments
Turlough Hughes
Turlough Hughes on 16 Dec 2019
I'm also assuming you have a folder which has all the files and that they are called
GaitAnalysisReport1.xls
GairAnalysisReport2.xls
etc
Cameron Kirk
Cameron Kirk on 16 Dec 2019
Hi Turlough,
Thanks for that!
I do indeed, they are in the same folder

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!