For loop through different excel files AND different worksheets (Matlab)-CodePudding

I try to loop not only through different excel sheets(~125), but also through different excel files (~12). I managed to write a code for the sheets, but now I am struggling how to scale that up to different excel files. The excel-files all have the same structure and number/name of sheets. Can anyone help me? Thanks a lot in advance!!

foldername = 'Raw_data';
 
cd(foldername);

fnames = dir('*raw.xlsx');  

%% extraction of sheet name
[~, sheet_name] = xlsfinfo('Test_raw.xlsx');
%% additional array for merging later
cali=[1; 2; 5; 10; 20; 50; 100; 200; 500; 1000]; 


%for i=1:length(fnames)             %I guess ?

    for k=1:numel(sheet_name)       %operation for all sheets
        
    %extract data of one excel file, but different sheets
    [~,~,raw{k}]=xlsread('Test_raw.xlsx',sheet_name{k},'A5:A14');

    x=vertcat(raw{:});
   
    end
  


B = reshape(x,10,k); 
numind = cellfun(@isnumeric, B);        %identifies numeric values
B(~numind) = {NaN}                      %NOT num. values to NaN 
b =cell2mat(B); 
final_data = [cali b]; 

%end

CodePudding user response：

You want to loop through all your excel files. You already gathered all the filenames in fnames. You basically did setup your for-loop, the only thing missing is replacing 'Test_raw.xlsx' in xlsread with fnames(i).name.

for i=1:length(fnames)             %I guess ?

    for k=1:numel(sheet_name)       %operation for all sheets

        %extract data of one excel file, but different sheets
        [~,~,raw{k}]=xlsread('Test_raw.xlsx',sheet_name{k},'A5:A14');

        x=vertcat(raw{:});
    end
end

Be careful that you have to adapt your final_data variable. For just all the data from all the files in it you could use this variable as a cell-array containing an element for each file. It is good practice allocating this array before entering the loop

final_data = cell(length(fnames),1);

%% here go the loops

clear B
B = reshape(x,10,k); 
numind = cellfun(@isnumeric, B);        %identifies numeric values
B(~numind) = {NaN}                      %NOT num. values to NaN 
b =cell2mat(B); 
final_data{i} = [cali b];

B, numind and b will be temporary working variables that are being overwritten each loop. Because of this, clearing them before their next use can be good practice.

After the loop, you can access your data with e.g. final_data{5} to access the fifth file.