Create loop for multiple excel sheets/documents

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

Answer 1

[Turlough Hughes]

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.

[Turlough Hughes]

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]

Hi Turlough,

Thanks for that!

I do indeed, they are in the same folder