MATLAB Answers

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

2 views (last 30 days)
Ying Wai Tang
Ying Wai Tang on 17 Nov 2020
Commented: Ying Wai Tang on 25 Nov 2020
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.

  6 Comments

Show 3 older comments
Stephen Cobeldick
Stephen Cobeldick on 25 Nov 2020
"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
Ying Wai Tang on 25 Nov 2020
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

Sign in to comment.

Answers (1)

Peter Perkins
Peter Perkins on 19 Nov 2020
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.

  1 Comment

Ying Wai Tang
Ying Wai Tang on 20 Nov 2020
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!

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!