필터 지우기
필터 지우기

Read and write multiple excel files.one after the other

조회 수: 6 (최근 30일)
KRUNAL
KRUNAL 2014년 7월 23일
답변: Tony Castillo 2019년 11월 7일
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?

채택된 답변

dpb
dpb 2014년 7월 24일
편집: dpb 2014년 7월 24일
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
KRUNAL
KRUNAL 2014년 7월 24일
What should be added in the
dat =...
Should be it like all output variable names (like eg in my case )
dat = {'Y' 'Z'}
dpb
dpb 2014년 7월 24일
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.

댓글을 달려면 로그인하십시오.

추가 답변 (8개)

dpb
dpb 2014년 7월 23일
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
KRUNAL
KRUNAL 2014년 7월 24일
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

댓글을 달려면 로그인하십시오.


KRUNAL
KRUNAL 2014년 7월 24일
편집: KRUNAL 2014년 7월 24일
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 2014년 7월 24일
편집: KRUNAL 2014년 7월 24일
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
KRUNAL
KRUNAL 2014년 7월 25일
Hey can you tell me what is the command to access data field of a file.For example in my code line 9 says
dat1=dat1=xlsread(d1(i).name);
This gives me name field of my file at d1(1).But I want to access its data. So what code/command should I write for this?
dpb
dpb 2014년 7월 25일
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.

댓글을 달려면 로그인하십시오.


KRUNAL
KRUNAL 2014년 7월 28일
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
dpb
dpb 2014년 7월 28일
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...

댓글을 달려면 로그인하십시오.


KRUNAL
KRUNAL 2014년 7월 28일
Can it be done by GUI?
  댓글 수: 1
dpb
dpb 2014년 7월 28일
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.

댓글을 달려면 로그인하십시오.


KRUNAL
KRUNAL 2014년 7월 28일
No problem. Thanks for your valuable and timely responses dpb !

KRUNAL
KRUNAL 2014년 8월 5일
Hey dpb, do you know if and how I can write the same program using ActiveX?
  댓글 수: 4
dpb
dpb 2014년 8월 5일
The doc's for Excel will be associated with Excel, not Matlab. It's not a Matlab question of how Excel methods/properties work; that's stuff MS supplies with Excel. Matlab simply uses the Windows OS stuff. Here's a link that looks like should be a decent starting point but as I say, I've never done enough of this to have more than the most cursory of actual knowledge--I had others I could hand this sort of coding if needed off to and I'm disinterested in learning more than nothing about the subject now. :)
KRUNAL
KRUNAL 2014년 8월 5일
ok thanks !

댓글을 달려면 로그인하십시오.


Tony Castillo
Tony Castillo 2019년 11월 7일
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')

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by