How to loop through excel sheets, perform calculations, and compile results

Question

I have roughly 70,000 sheets that all have to have calculations done, and then all results compiled into a new sheet (which would be 70,000 lines long). It needs to be sorted by date.

I'm VERY very very poor at matlab, but I've what I need the script to do for each excel sheet, I'm just unsure how to make it do them for all. Thank you!!! (I took out some of the not important code)

%Reading in excel sheet

     B = xlsread('24259893-008020361800.TorqueData.20160104.034602AM.csv');



%Creating new matrix

    [inYdim, inXdim] = size(B);

    Ydim = inYdim;

    [num,str,raw]=xlsread('24259893-008020361800.TorqueData.20160104.034602AM.csv',strcat('A1:C',num2str(Ydim)));



%Extracting column C

    C=raw(:,3);

    for k = 1:numel(C)

      if isnan(C{k})

        C{k} = '';

    end

end


%Calculations 

    TargetT=2000;

    AvgT=mean(t12);

    TAcc=((AvgT-TargetT)/TargetT)*100 ;

    StdDev=std(B(ind1:ind2,2));

    ResTime=t4-t3;

    FallTime=t6-t5;

    DragT=mean(t78);

    BreakInT=mean(t910);

    BreakInTime=(t10-t9)/1000;

    BreakInE=BreakInT*BreakInTime*200*.1047;






%Combining results 

    Results=[AvgT TAcc StdDev ResTime FallTime DragT BreakInT BreakInTime BreakInE]

I think I need to do something along the lines of:

filenames=dir('*.csv')

and I found this that may be useful:

filenames=dir('*.csv');

    for file=filenames'

    csv=load(file.name);





with stuff in here


 end

Possible duplicate of [Loop through files in a folder in matlab](http://stackoverflow.com/questions/11621846/loop-through-files-in-a-folder-in-matlab) — sco1, Jul 05 '16 at 19:16
That's where I got the last bit from, but it gives me an error that not all sheets are the same dimension. And won't I need to do stuff with i and j? I don't know how to do that part either. — Megan Warner, Jul 05 '16 at 19:20
If you tried something and it didn't work, don't you think that would be useful information to include in your question? You asked "how do I do this" and the answer is "loop through the files in the directory." We cannot divine other issues that you have encountered if you do not tell us about them. See [ask] and the guidance on how to provide a [mcve]. — sco1, Jul 05 '16 at 19:24
I assumed it did not work because it was incomplete (ie, I did not know how to complete it), not because it was an inaccurate thing to do. — Megan Warner, Jul 05 '16 at 19:43

score 0 · Answer 1 · answered Jul 05 '16 at 19:20

0

You have the right idea, but you need to index your file names in order to be able to step through them in the for loop.

FileDir = 'Your Directory';
FileNames = {'Test1';'Test2';'Test3'};
for k=1:length(FileNames)
     file=[FileDir,'/',FileNames{k}]); 
     [outputdata]=xlsread(file,sheet#, data locations);
     THE REST OF YOUR LOOP, Indexed by k
end

How you choose to get the file names and directory is up to you.

answered Jul 05 '16 at 19:20

K.G

71
6

Thank you very much! I'll try this – Megan Warner Jul 05 '16 at 19:44
Hi, so I got my code to go through all my files (well, I'm testing it on 10 for now). I'm unsure what you mean by FileNames. I can't possible list all 70,000 :( – Megan Warner Jul 06 '16 at 12:33
I just typed those 3 file names out as an example. You can get all of your file names using the `dir` command. Make sure you write that as `STRUCT=dir;` to make sure the answer is stored (STRUCT can be whatever name you want). They will be stored in a structure with the file names in the column STRUCT.name. You can then turn it into a cell array by `FileNames={STRUCT.name};` This will generate the variable `FileNames` for all of the files in your directory. Having file names of different lengths should not be an issue – K.G Jul 06 '16 at 15:49

How to loop through excel sheets, perform calculations, and compile results

1 Answers1