Need to speed up xlsread and xlswrite process..... not sure how though

Hello all, I have been trying to put together a large matlab code to run a bunch of excel data points from an ever growing list of excel files.

Each excel file has 4 columns of 74000 data points that have to be read, then the raw data is written into a main workbook with a new worksheet created for each excel file read. To make it simple we will refer to the raw data files as A's and the MAIN as B. So it takes the raw data from EACH A's and then writes it to B. It then does a couple calculations for each column and then writes each of those to B as well.

This is all extablished to run in a loop for each excel file in a main folder. This however... take a long time to run. Each file adds a lot of data to the excel file. So everytime it has to open the file for a read or write... it takes longer and longer to process. To run just 12 files you are looking at over 4 hours or more of run time to calculate it all.

This is only goinn to get worse as the number of files increases. Granted, i have a check in it to verify if that file was processed and skips it, but the time to open each time will grow as more files are added.

I need a work around for this. Please see the code below. I have heard of activex or something like this... but do not know anything about it or even where to begin.

%%Software desired to behave as follow:
% Develop Matlab Program for NVH. It will need to perform as
% follows:
% 1. Open (one at a time) all files of raw data in sepcified folder with specific file
%       extension on them. 
% 2. For Each File read the second worksheet "Untitled" and pull the data
%       from columns A-D startings at cell 2 down to 74001.
%       Store the data to be written
% 3. Create new worksheet in "Data Analysis.xlsm"
% 4. Copy layout of "Template" into each new Sheet
% 5. Rename new sheet to match the name of the file opened in Step 1.
% 6. Write the data read into specific columns. A to C, B to G, C to K,and
%       D to O. Start all at the second cell.
% 7. Perform FFT for each new row and Copy the Data into the Appropriate
%       Rows. For data in C put in F, For G put in J, For K put into N, 
%       and for O put into R.
% 8. Repeat this process for each file.
% 9. Look into adding a check to see if the file has already been copied by
%       checking the worksheet names for a match, if it does, skip, 
%       otherwise do the process.
%%CODE  (Meat of the Program) 
%%Prompts operator to verify Excel Conversion
clear,clc
% clear and erase all variables from prior runs

addpath(genpath('C:\Users\Headstrong2740\Dropbox'));
%ensures the path is set to access needed folders and files
verify= questdlg('Did you already convert the .tdms files to .xlsx files?',...
    'Excel Verify',...
    ' Yes ',' No ',' No ');

switch verify
    case ' Yes '
        %%Some Variable Declaring
        folder='C:\Users\Headstrong2740\Dropbox\''\ACC_Excel';
        folder2='C:\Users\Headstrong2740\Dropbox\''\ACC_DATA';
        folder3='C:\Users\Headstrong2740\Dropbox\''\Raw Data';
        % declares the folder that all the data files are located in

          mainfile='Desktop\Data Analysis.xlsm';  
          % Declares File to write to

          sheet= 'Untitled';
              % Name of the worksheet that will be copied
          %%This next chunk will sort all the files into needed locations
          % This is done to make things a little easier to manage and keep things
          % running smoothly.

          d2=dir(fullfile(folder2,'*tdms_index'));   
          d3=dir(fullfile(folder2,'*tdms'));
          d4=dir(fullfile(folder2,'*xlsx'));
          % Reads through the folder2 and takes inventory of each type of files
          % based on the file extensions( .tdms for example). 

          for i=1:length(d4)
              source = fullfile(folder2,d4(i).name);
              movefile(source,folder)
          end
          % moves all the .xlsx files to the Excel folder

          for i=1:length(d3)
              source = fullfile(folder2,d3(i).name);
              movefile(source,folder3)
          end
          for i=1:length(d2)
              source = fullfile(folder2,d2(i).name);
              movefile(source,folder3)
          end
          % Moves all the .tdms or .tdms_index files to the Raw Data folder.

           d1=dir(fullfile(folder,'*xlsx'));
              % defines a variable to represent each excel file as it is encountered   

          %%The Second half of the program. This will take some time... 
          % The first time this runs will take several hours due to the large number
          % of excel files that have to be worked through. However if run on a daily
          % bases at the end of the shift, it should only take about 1-2 hours or
          % less to run.

          for i=1:length(d1);
              [A,B] = xlsfinfo('Desktop\Data Analysis.xlsm');
              sheetvalid=any(strcmp(B,d1(i).name));
              % looking to see what worksheets have been created in the workbook

              if sheetvalid  == 0    
                      %check to see if file has been processed already, if yes (1)
                      %skips down to "else" and starts over with the next file
                  [numA,txtA,rawA] = xlsread(d1(i).name,sheet,'A2:A74001');
                  [numB,txtB,rawB] = xlsread(d1(i).name,sheet,'B2:B74001');
                  [numC,txtC,rawC] = xlsread(d1(i).name,sheet,'C2:C74001');
                  [numD,txtD,rawD] = xlsread(d1(i).name,sheet,'D2:D74001');
                  % reading the file and storing the data for each Axis (4 in total)

                  [num,txt,raw] = xlsread(mainfile,'Template','A1:X74001'); 
                  % Copies the Template sheet for formatting purposes

                  xlswrite(mainfile,raw,d1(i).name)
                  % Duplicates the "Template" Sheet into a new Sheet with the Machine
                  % name
                  xlswrite(mainfile,numA,d1(i).name,'C2:C74001')
                  xlswrite(mainfile,numB,d1(i).name,'G2:G74001')
                  xlswrite(mainfile,numC,d1(i).name,'K2:K74001')
                  xlswrite(mainfile,numD,d1(i).name,'O2:O74001')
                  % writes the appropriate RAW data into each row
              %FFT complex
                  fftA=fft(numA);
                  fftB=fft(numB);
                  fftC=fft(numC);
                  fftD=fft(numD);
                  FFTA=cellstr(num2str(fftA));
                  FFTB=cellstr(num2str(fftB));
                  FFTC=cellstr(num2str(fftC));
                  FFTD=cellstr(num2str(fftD));
                  % Caluclates the FFT and stores it as a cell array
                  xlswrite(mainfile,FFTA,d1(i).name,'F2:F74001')
                  xlswrite(mainfile,FFTB,d1(i).name,'J2:J74001')
                  xlswrite(mainfile,FFTC,d1(i).name,'N2:N74001')
                  xlswrite(mainfile,FFTD,d1(i).name,'R2:R74001')
                  % Writes the new data to excel file
              % FFT Magnitude    
                  % Fs = W5  sa=W6   D=W2  t=W3   (notes  for setting up next
                  % equations
                  magA=(2/7400).*fftA;
                  magB=(2/7400).*fftB;
                  magC=(2/7400).*fftC;
                  magD=(2/7400).*fftD;
                  FFTmagA=cellstr(num2str(magA));
                  FFTmagB=cellstr(num2str(magB));
                  FFTmagC=cellstr(num2str(magC));
                  FFTmagD=cellstr(num2str(magD));
              % Calculates the magnitude of the FFT
                  xlswrite(mainfile,FFTmagA,d1(i).name,'E2:E74001')
                  xlswrite(mainfile,FFTmagB,d1(i).name,'I2:I74001')
                  xlswrite(mainfile,FFTmagC,d1(i).name,'M2:M74001')
                  xlswrite(mainfile,FFTmagD,d1(i).name,'Q2:Q74001')

              else  % Skip it if its already there
              end

          end
      case ' No '
          OperatorInstruction
  end

Answer 1

[Image Analyst]

What is length(d1)? In other words, how many files are there? For each file you're using, you call xlsread() 5 times and xlswrite() 12 times. That means for each file you're launching Excel 17 times and shutting down Excel 17 times. While this will be much, much slower than ActiveX, it shouldn't take 4 hours unless you have hundreds of files. It sounds like you've perhaps run across my ActiveX demo (attached here also), so feel free to adapt my code to convert your code to ActiveX and you will definitely get some speed up.

[James]

There is right now only 12 files in there, and as each file processes, the Main excel file gets larger (4 sets of data per RAW file). Each set of those raw data has 2 calculations applied and then written. But as this grows and more files are added, it could easily get to several thousand files with 4x74000 data points of raw data... then two calculations becomes 8 sets with 74000 cells to each set of data points..... as you can see.... The main excel file gets very large... very quickly.

[Image Analyst]

Yep, it will be much faster. Just replace all your xlswrite() with xlswrite1(), and open an activex server before your loop and call Excel.Save and Excel.Quit after the loop and it will go much faster.