How do I run my MATLAB program through multiple excel files in the folder?

Good day all,

I would like to run my simple MATLAB program on multiple excel files. Foilowing is an example of the code:

H1=xlsread('.xlsx','Steps','A:A');
H2=xlsread('.xlsx','Steps','C:C');
H3=xlsread('.xlsx','Steps','E:E');
H4=xlsread('.xlsx','Steps','G:G');
H5=xlsread('.xlsx','Steps','I:I');
H6=xlsread('.xlsx','Steps','K:K');
H7=xlsread('.xlsx','Steps','M:M');

P1=xlsread('.xlsx','Steps','S:S');
P2=xlsread('.xlsx','Steps','T:T');
P3=xlsread('.xlsx','Steps','U:U');
P4=xlsread('.xlsx','Steps','V:V');
P5=xlsread('.xlsx','Steps','W:W');
P6=xlsread('.xlsx','Steps','X:X');
P7=xlsread('.xlsx','Steps','Y:Y');

IH1=trapz(H1)/1000;
IH2=trapz(H2)/1000;
IH3=trapz(H3)/1000;
IH4=trapz(H4)/1000;
IH5=trapz(H5)/1000;
IH6=trapz(H6)/1000;
IH7=trapz(H7)/1000;

IP1=trapz(P1)/1000;
IP2=trapz(P2)/1000;
IP3=trapz(P3)/1000;
IP4=trapz(P4)/1000;
IP5=trapz(P5)/1000;
IP6=trapz(P6)/1000;
IP7=trapz(P7)/1000;

A=[IH1,IH2,IH3,IH4,IH5,IH6,IH7];
B=[IP1,IP2,IP3,IP4,IP5,IP6,IP7];
C=mean(A);
D=std(A);
E=mean(B);
F=std(B);

Results=[C,D,E,F];

col_header={'1','2','3','4','5','6'};

filename='RESULT.xlsx';
xlswrite(filename,col_header,'Sheet1','A1');
xlswrite(filename,A.','Sheet1','A2');
xlswrite(filename,B.','Sheet1','B2');
xlswrite(filename,Results,'Sheet1','C2');
    

clear

This set of code is working, however I need to change the filename every time i execute the code on the different excel files in the folder. Are there anyway to change this code so that it is able to automatically loop through all of the excel files in the folder? And how can i save the excel output files from xlswrite as different names so that it would not overwrite the previous excel output if I run this on multiple excel at once? Thank you.

[Stephen Cobeldick]

https://www.mathworks.com/help/matlab/import_export/process-a-sequence-of-files.html

[Ying Wai Tang]

thanks for the link. If I would like to export the result from the matlab program onto an excel file, how do i save them as different name so that they would not overwrite each other?

[Stephen Cobeldick]

"how do i save them as different name so that they would not overwrite each other?"

You can use sprintf to generate the filename, e.g.:

data = cell array of the imported/processed data
for k = 1:numel(data)
    fnm = sprintf('result_%d.xlsx',k);
    xlswrite(fnm,data{k})
end

[Ying Wai Tang]

May I know what is the c in xlswrite(fnm,C{k}) means?

[Stephen Cobeldick]

"May I know what is the c in xlswrite(fnm,C{k}) means?"

It is the cell array that contains your data arrays. I changed the name to make the meaning clearer.

[Ying Wai Tang]

Oh I see. Thank you very much for your clarification. So basically the following is the code that I am running now. It runs with no errors, however the result is not what I wanted. The context is that I have a list of excel files which i would like to get variables A,B,C,D,E and F (which I have defined in the code below) for all of these files. With A & B being a 1x7 matrix. The output of this program basically just puts all my variables in different excel files. So I have 7 files with one distinct variable in each of this files, rather than 7 variables in one output file for the 6 different excel files that I have placed within the folder for the code to be executed on. Would you kindly see what might be the problem here?

clc
clear

s= dir('*.xlsx'); 
numfiles = length(s);
for k = 1:numfiles 
  filename=s(k).name;
end


H1=xlsread(filename,'Steps','A:A');
H2=xlsread(filename,'Steps','C:C');
H3=xlsread(filename,'Steps','E:E');
H4=xlsread(filename,'Steps','G:G');
H5=xlsread(filename,'Steps','I:I');
H6=xlsread(filename,'Steps','K:K');
H7=xlsread(filename,'Steps','M:M');

P1=xlsread(filename,'Steps','S:S');
P2=xlsread(filename,'Steps','T:T');
P3=xlsread(filename,'Steps','U:U');
P4=xlsread(filename,'Steps','V:V');
P5=xlsread(filename,'Steps','W:W');
P6=xlsread(filename,'Steps','X:X');
P7=xlsread(filename,'Steps','Y:Y');

IH1=trapz(H1)/1000;
IH2=trapz(H2)/1000;
IH3=trapz(H3)/1000;
IH4=trapz(H4)/1000;
IH5=trapz(H5)/1000;
IH6=trapz(H6)/1000;
IH7=trapz(H7)/1000;

IP1=trapz(P1)/1000;
IP2=trapz(P2)/1000;
IP3=trapz(P3)/1000;
IP4=trapz(P4)/1000;
IP5=trapz(P5)/1000;
IP6=trapz(P6)/1000;
IP7=trapz(P7)/1000;

A=[IH1,IH2,IH3,IH4,IH5,IH6,IH7];
B=[IP1,IP2,IP3,IP4,IP5,IP6,IP7];
C=mean(A);
D=std(A);
E=mean(B);
F=std(B);

Results={A,B,C,D,E,F};

for k = 1:numel(Results)
    fnm = sprintf('result_%d.xlsx',k);
    xlswrite(fnm,Results{k})
end
    

% clear

Answer 1

[Peter Perkins]

There is. First step: use readmatrix or readtable, stay away from the much older xlsread. Ditto xlswrite. Then use something like s = dir('*.xlsx'); to get a struct, and iterate over the file names in s.name.

[Ying Wai Tang]

good day sir, thanks for the response. I will be trying your suggestions. Would you mind going into a little more details of each of the functions you mentioned? For example, what does s.name do? Thanks!