Read and write multiple excel files.one after the other

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?

Answer 1

[dpb]

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).

[KRUNAL]

How can I change sheet with reference to each file read,calculated and then written to the same file?

[dpb]

However your logic says it should be. Anything you can write down an algorithm for defining what sheet and area is desired for a given file you can code; you just have to have the rule that defines what that is. If it's the same for each sheet it could be hardcoded; if it changes then there has to be some rule that says how.

But, I don't know what that would be from here, only you know that.

[KRUNAL]

What should be added in the

 dat =...

Should be it like all output variable names (like eg in my case )

 dat  = {'Y' 'Z'}

[dpb]

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.

Answer 2

[dpb]

http://matlab.wikia.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F

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...

[KRUNAL]

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

Answer 3

[KRUNAL]

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 ?

Answer 4

[KRUNAL]

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');

[KRUNAL]

tr is basically the file where I have written the above code

[dpb]

Don't know about that error, if I take

sheet = 'sheet1';
a = {'BHS_S'};
xlswrite('work.xls',a,sheet,'AA1');

it works fine here. Note I removed much of the extra obfuscation and complexity you've added--there's no need for the extra [] nor is there any real point in cell(1,1), you're not filling a large array so the direct assignment will be ok. Also, there's no need for the explicit reference when just assigning a single cell-string. Also note there's no need for the copy.

Your subdirectories, etc., obviously don't exist here so I just used a hardcoded file name; you'll have to debug that part.

Try the above from the command line and if you still get an error, it's a problem w/ Excel/Microsoft it would seem.

[KRUNAL]

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]

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.

Answer 5

[KRUNAL]

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?

[dpb]

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...

Answer 6

[KRUNAL]

Can it be done by GUI?

[dpb]

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.

Answer 7

[KRUNAL]

No problem. Thanks for your valuable and timely responses dpb !

Answer 8

[KRUNAL]

Hey dpb, do you know if and how I can write the same program using ActiveX?

[dpb]

"IF", I'm sure you can. "How" is look up all the myriad of object properties and methods in the Excel documentation--I "know nuthink" of it, specifically, though, so I'd be at the same learning point as you as noted above.

[KRUNAL]

I am sorry, but I didn't understand what you have typed above. Are you trying to say to see the documents related to active X with excel in Matlab. If so, I would like to tell you that I tried observing it, but I am unable to find out how can I read data from two files and write it to the third file using active X

[dpb]

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. :)

http://msdn.microsoft.com/en-us/library/bb211430%28v=office.12%29.aspx

[KRUNAL]

ok thanks !

Answer 9

[Tony Castillo]

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')