Create loop for multiple excel sheets/documents
2 views (last 30 days)
Show older comments
Cameron Kirk
on 16 Dec 2019
Commented: Cameron Kirk
on 16 Dec 2019
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
0 Comments
Accepted Answer
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
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
More Answers (0)
See Also
Categories
Find more on Spreadsheets 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!