vectorization for nested loop where inner loop is variable depending on value of element in outer loop

Hello,

I just learned about vectorization after trying to find a faster way to loop through all the sheets of all the files I need.

Here is basically what I'm trying to do:

for k = 1:arrayOfAllMyFilePaths
%get filepath at k
%get number of sheets in file using filepath
      for m = 1:lengthOfNumberOfSheetsInFile
      %read some data in each sheet

[Bob Thompson]

What exactly are you looking for help with? Can you post the code you have already? Have you run into any errors (post those as well if you have)?

The process you're looking to implement is definitely doable, I just want to make sure I am answering your actual question, rather than something you have already figured out on your own.

[Stephanie M]

Thanks so much!

So basically: the nested loop I have takes forever.

I need to read in several pieces of data from each sheet of each file that I have. I have x number of files. Each file has a different number of sheets. Oh yeah, my other problem is I'm trying to add all that data I read in the loop to a matrix... I'm sure I'm not doing it correctly. (I labelled that part "Other big problem" in a comment)

Here's my code:

%I made a cell array of all the file paths called totalFileList for k = 1:length(totalFileList)

    %get a file path to one of the complete data files
    stringPath = totalFileList{k};

    %get a list of the sheets in that file
    [~, sheets] = xlsfinfo(stringPath);
    currentFileSheets = sheets;

    %loop through all the sheets
    for m = 1:length(currentFileSheets)

        %get the name of the sheet
        sheetName = currentFileSheets{m};

        %read in data
       data = xlsread(stringPath,m,'J7');
       %Other big problem: trying to put all the reads into one 
       matrix
       i = i + 1;
       dataList(i) = data;     
    end
end

[Stephanie M]

Sorry, the first line of my code starts above the code section. not sure why It was cut off

[Bob Thompson]

I suspect part of the slowness is due to the fact that you are using xlsread within the nested loops. Each time you use xlsread MATLAB opens excel, opens the file, extracts the data, closes the file, and closes excel.

Unfortunately, the only way I know of getting around this is to use the Actxserver(COM) capability. COM will allow you to open excel once, open a document once, and then read all the data you need from the sheets before closing the document once and opening the next. COM has a whole different set of commands though that can be a bit difficult to track down.

I found this to get you started: https://www.mathworks.com/matlabcentral/answers/16299-is-there-instead-solution-of-xlsread

[Stephanie M]

Thanks, Bob! I'll try that. Theoretically though, would I be able to vectorize this?

[OCDER]

Is there a requirement to vectorize this? Not everything should be vectorized or can be vectorized. Mathworks did improve their for loop speeds, and the bottleneck is probably hard drive read speed, as opposed to CPU for calculations (which is what vectorization takes advantage of). For reading files, for loop is good.

From website: https://www.mathworks.com/help/matlab/matlab_prog/vectorization.html

"MATLAB® is optimized for operations involving matrices and vectors. The process of revising loop-based, scalar-oriented code to use MATLAB matrix and vector operations is called vectorization."

[Stephanie M]

Good point, I think I'll stick with the for loop and use Active X to speed things up.