MATLAB Answers

Read and write multiple excel files.one after the other

75 views (last 30 days)
KRUNAL
KRUNAL on 23 Jul 2014
Answered: Tony Castillo on 7 Nov 2019
I have 2 folders each containing 4 excel files. Then I need to do arithmetic calculation between 1st excel file of folder 1 with 1st excel file stored in folder 2 and write result in 1st blank excel file that is stored in folder 3(Arithmetic operation I am able to do). This process I want to do for all the files in the two folders. Is it possible? If so what code do I need to add to my existing code. Should I use 'For' loop?

Accepted Answer

dpb
dpb on 24 Jul 2014
Edited: dpb on 24 Jul 2014
Basically, what I'm saying is
SEfile = '<file1location>';
LBfile = '<file2location>';
dstfile = '<destinationfilelocation>';
d1=dir(fullfile(SEfile,'somesuitablewildcardpattern');
d2=dir(fullfile(LBfile,'somesuitablewildcardpattern');
for i=1:length(d1)
dat1=xlsread(d1(i).name); % read the first
dat2=xlsread(d2(i).name); % and the second...
% do whatever calculations on data desired here
dat=... % results of those calcs
xlswrite(fullfile(dstfile,'desiredoutputfilename',dat);
end
Salt the specifics of the sheet names and areas to suit and fixup output file naming convention as desired (with some modification of the input names one would presume).
  4 Comments
dpb
dpb on 24 Jul 2014
What ever you want to do with the data you're read in...cut 'n paste what you want from your other scripts into the general outline provided. That was the place to do whatever calculations you want with the two data sets as the comment says and whatever else is to be done with the two files' individual datasets.

Sign in to comment.

More Answers (8)

dpb
dpb on 23 Jul 2014
I am extremely partial to the dir solution; in your case return two directory structures; one for each of the two directories and iterate over them simultaneously in a loop. You'll of course have to ensure you've go the consistent number of files in the two subdirectories and that you've got the proper ones at the same time but those are details...
  1 Comment
KRUNAL
KRUNAL on 24 Jul 2014
Yes,I do have same number of files in both the input sub-directories as well as output directories.But using the code you suggested works only to read data but not write data. Reading files in a loop and then writing the overwrites previous read data. So I tried adding output files directory in the loop too but it is not working as per the link you mentioned earlier. Could you suggest me what can be done. I have posted my code below that is working properly. It reads data from two files both located in two different folders and writes that data in the 3rd file located in 3rd folder(all three files have the same name).Then it performs the required mathematical calculation and shows the result in the same 3rd file in some other column. Please look at it and try to help me for making it possible for multiple files

Sign in to comment.


KRUNAL
KRUNAL on 24 Jul 2014
Edited: KRUNAL on 24 Jul 2014
Do you mean making nested loop? By the way the files, I am trying to read(one from each folder)have the same name.Should I then use recursive dir for it? I tried using the using dir based on your suggested link,but I am not able to work it through for each files to read them in the third file. This is what I am currently able to do with one file each from two folders and sending data to the 3rd excel file in 3rd folder.I am reading one column each from two excel files that are present in two different folders, writing them into the third excel file that is present in the 3rd folder.Then I am subtracting the two columns(eg weight value copied from file(1)-weight value copied from file(2)) and result is stored in 3rd column and based on the result, it gives comments in the 4th column The code below is not entirely what I am saying but almost the same:
clc;
clear all;
InputFolder = 'inputfolderdir';
SEfile = '<file1location>';
LBfile = '<file2location>';
dstfile = '<destinationfilelocation>';
sheet = 'sheet1';
a= cell(1,1);
a(:,1) = [{'BHS_S(SE)'}];
b =a (:,1);
xlswrite(dstfile,b,sheet,'B1');
c= cell(1,1);
c(:,1) = [{'BHS_S(LB)'}];
d =c (:,1);
xlswrite(dstfile,d,sheet,'C1');
ch= cell(1,1);
ch(:,1) = [{'Change in BHS_S)'}];
chng =ch(:,1);
xlswrite(dstfile,chng,sheet,'AA1');
e= cell(1,1);
e(:,1) = [{'BHS_P(SE)'}];
f=e (:,1);
xlswrite(filename,f,sheet,'E1');
g= cell(1,1);
g(:,1) = [{'BHS_P(LB)'}];
h =g (:,1);
xlswrite(dstfile,h,sheet,'F1');
ch2= cell(1,1);
ch2(:,1) = [{'Change in BHS_P)'}];
chng2 =ch2(:,1);
xlswrite(dstfile,chng2,sheet,'AC1');
v1=xlsread(sfile,'AA:AA') ;
xlswrite(dstfile,v1,sheet,'AA2');
v2=xlsread(lfile,'AB:AB') ;
xlswrite(dstfile,v2,sheet,'AB2');
for i=2:11
X = xlsread(dstfile);
X(:,3) = X(:,2)-X(:,1);
Y = X(:,3);
xlswrite(dstfile,Y,sheet,'AC');
Z = (X(:,3));
ind = Z >= 350; %1's where you want to check values again.
Z = cell(size (Z)); %cell array for different length text.
Z(ind)= {'check values again'}; %for the indexes (marked with logical 1)
Z(~ind)= {'no change'}; %for !ind (inverse)
xlswrite (dstfile,Z,sheet,'AD');
Can anyone now suggest how can I implement this for multiple files ?

KRUNAL
KRUNAL on 24 Jul 2014
Edited: KRUNAL on 24 Jul 2014
I wrote the following code :
clc;
clear all;
SEfile = '\\psf\Home\Downloads\06_Data\Events\STG04_SE\';
LBfile = '\\psf\Home\Downloads\06_Data\Events\STG04_LB\';
dstfile = '\\psf\Home\Downloads\06_Data\Events\STG04_F\';
d1=dir(fullfile(SEfile,'*.xlsx'));
d2=dir(fullfile(LBfile,'*.xlsx'));
for i=1:length(d1)
dat1=xlsread(d1(i).name); % read the first
dat2=xlsread(d2(i).name); % and the second...
% do whatever calculations on data desired here
sheet = 'sheet1';
a= cell(1,1);
a(:,1) = [{'BHS_S'}];
b =a (:,1);
xlswrite(dstfile,b,sheet,'AA1');
c= cell(1,1);
c(:,1) = [{'BHS_P'}];
d =c (:,1);
xlswrite(dstfile,d,sheet,'AB1');
v1=xlsread(sfile,'AA:AA') ;
xlswrite(dstfile,v1,sheet,'AA2');
v2=xlsread(lfile,'AB:AB') ;
xlswrite(dstfile,v2,sheet,'AB2');
for i=2:11
X = xlsread(dstfile);
X(:,3) = X(:,2)-X(:,1);
Y = X(:,3);
xlswrite(dstfile,Y,sheet,'AC');
Z = (X(:,3));
ind = Z >= 350; %1's where you want to check values again.
Z = cell(size (Z)); %make a cell array so you can have different length text.
Z(ind)= {'check values again'}; %for the indexes (marked with logical 1) put in this text.
Z(~ind)= {'no change'}; %for !ind (inverse) put no change.
xlswrite (dstfile,Z,sheet,'AD');
end
%dat= % results of those calcs
xlswrite(fullfile(dstfile,'PostRock_Varnum4-1H_LOC1_STG04Event2_EventByRecvr_20140709_114624'));
end
It gives me the following error :
Error using xlswrite (line 220)
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: SaveAs method of Workbook class failed
Help File: xlmain11.chm Help Context ID: 0
Error in tr (line 17)
xlswrite(dstfile,b,sheet,'AA1');
  4 Comments
dpb
dpb on 25 Jul 2014
dat1=dat1=xlsread(d1(i).name);
Hopefully that is
dat1=xlsread(d1(i).name);
instead.
dir returns a structure array whereas dat1 will be a double array, not a structure per the documentation for xlsread
You address it with the subscripts for the locations desired.
Read and work thru the tutorial information in the "Getting Started" section of the documentation at
http://www.mathworks.com/help/matlab/getting-started-with-matlab.html, particularly starting with the "Matrices and Arrays" and "Array Indexing" sections.

Sign in to comment.


KRUNAL
KRUNAL on 28 Jul 2014
Hey dpb,thanks. I am just planning to add one more feature to it. Like, in this one. After the result I need to manually open the workbook to check my output. I want to do something which will automatically open the workbook and show the lines where changes will be required. Can you suggest me which will be the best way to do it?
  1 Comment
dpb
dpb on 28 Jul 2014
That undoubtedly would be thru active-x interaction and that level of dealing with Excel is beyond my pay grade...I don't use Excel. So, maybe somebody else will see this or start a new thread w/ that question or check on an Excel group for the actual stuff you need as it really isn't a Matlab question, per se...

Sign in to comment.


KRUNAL
KRUNAL on 28 Jul 2014
Can it be done by GUI?
  1 Comment
dpb
dpb on 28 Jul 2014
You can put anything in the GUI callbacks you want, so "yes" it can be done. It'll still be ActiveX interacting w/ Excel, however, and on that I'm no help; you can read the interminable doc on methods, etc., as easily as I as I'd be starting from dead zero, too.

Sign in to comment.


KRUNAL
KRUNAL on 28 Jul 2014
No problem. Thanks for your valuable and timely responses dpb !

KRUNAL
KRUNAL on 5 Aug 2014
Hey dpb, do you know if and how I can write the same program using ActiveX?
  4 Comments

Sign in to comment.


Tony Castillo
Tony Castillo on 7 Nov 2019
Hello all,
I need your help so as to overcome and issue I am experiencing now, I need to write dowm in and excel spreadsheet a matriz of 100 columns by 18 rows. Previously, I only needed to make it 27 times, because of it I have been writing my 27 column by 18 rows matriz, doing the procedure shown below, but rigth now it is not efficient.
I do hope you can give me a key to enhance this.
sheet = 1;
Vector= [VL IL IbToda Pos Neg Vb pl PLavg pg EneIN EnCONV socmin socmax DeltaSOC Avg_SOC n MAPE RMSE FF]';
if i==1
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'b2:b20')
elseif i==2
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'c2:c20')
elseif i==3
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'d2:d20')
elseif i==4
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'e2:e20')
elseif i==5
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'f2:f20')
elseif i==6
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'g2:g20')
elseif i==7
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'h2:h20')
elseif i==8
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'i2:i20')
elseif i==9
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'j2:j20')
elseif i==10
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'k2:k20')
elseif i==11
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'l2:l20')
elseif i==12
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'m2:m20')
elseif i==13
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'n2:n20')
elseif i==14
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'o2:o20')
elseif i==15
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'p2:p20')
elseif i==16
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'q2:q20')
elseif i==17
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'r2:r20')
elseif i==18
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'s2:s20')
elseif i==19
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'t2:t20')
elseif i==20
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'u2:u20')
elseif i==21
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'v2:v20')
elseif i==22
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'w2:w20')
elseif i==23
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'x2:x20')
elseif i==24
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'y2:y20')
elseif i==25
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'z2:z20')
elseif i==26
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'aa2:aa20')
else
xlswrite('HOUSE_REAL.xlsx',Vector,sheet,'b2:ab20')
end
filename = 'HOUSE_REAL.xlsx';
A = {'VL(V)','IL(A)','IbToda(A)','Ib(+)','Ib(-)','Vb(V)',...
'Pico de consumo (W)','Potencia Media(W)','Pico de generación (W)',...
'Energía de entrada diaría (Wh)','Energía convertida diaría (Wh)',...
'SOC min (%)','SOC max (%)','DeltaSOC (%)','Avg_SOC (%)', ...
'Eficiencia (%)','MAPE (%)','RMSE','Fill Factor'}';
B={'REAL'};
xlRange = 'A2';
xlswrite(filename,A,sheet,xlRange)
xlswrite(filename,B,sheet,'A1:ab1')

Community Treasure Hunt

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

Start Hunting!