this code is taking a lot of time to run,

Is there anyway i can write this code efficiently and reduce runtime? I am trying to import data from multiple files in a folder

clc;clear;

testfiledir = 'C:\Users\bidgu\OneDrive\Desktop\manpower';
matfiles = dir(fullfile(testfiledir, '*.xlsm'));
nfiles = length(matfiles);
datafi  = cell(nfiles);
for i = 1 :nfiles
%    fid = fopen( fullfile(testfiledir, matfiles(i).name) );
   datafi{i} = readtable(fullfile(testfiledir, matfiles(i).name));
%    fclose(fid);
end

[Adam Danz]

I doubt this will have a significant impact on execution time but one mistake you can fix,

datafi  = cell(nfiles);
% should be 
datafi  = cell(nfiles,1); % or cell(1,nfiles);

You could run the profiler to get a timing report that may reveal what parts of your code are taking the most time.

https://www.mathworks.com/help/matlab/ref/profile.html

[dpb]

Well, you are creating a cell array of tables; one element for each file. That's bound to be creating quite a lot of overhead and then you'll have to dereference the cell to be able to access each table to use it, besides.

More typical would be to read each file and do whatever is wanted to be done with it, then go on to the next. Or, if it is necessary to have the data from all files to do the analyis, then read the files sequentially but, presuming they contain similar variables, concatenate them into array and then build a table from the full file for analysis.

W/o further info on objective and file structure, more specifics are pretty hard to give.

[Walter Roberson]

Depending on which MATLAB release you are using, adding the option

 'UseExcel', false 

to the readtable() might help. This will not help for R2019b, as that is already the default in R2019b, but it was not the default in some of the earlier releases.

Other than that, if you have the Parallel Computing Toolbox, then you might be able to do the readtable() in workers. readtable() does do a bunch of file I/O and using multiple workers usually does not help much on file I/O (because you are limited by disk speeds). However, .xlsx and .xlsm files require a fair bit of text processing to extract the data from the binary form, and that work can potentially take advantage of multiple CPUs.

The main way to speed up reading a lot of files is to use binary files instead of spread-sheets. spread-sheets are convenient for interchange, but raw binary is much more efficient.

[dpb]

PV Answer moved to Comment -- dpb

I want all the data from those files at a time and need to store it in an array.

The above program does what i need but not finding an effective way.

I need the data from all those files at a time and to do anaysis on it

[dpb]

"I need the data from all those files at a time and to do anaysis on it"

Then go back to the suggestion I made earlier -- and perhaps Walter's could possibly help some -- and read the files as data; put together and then make a table (or not; depending upon the analyses desired, the array form may be more efficient).

As noted also, it's not possible to help much w/o example of the file(s) and some more specific idea of what it is that is the end result desired (beyond reading them all). Attach sample file if expect anybody to be able to do anything other than simply pontificate.

[Prajwal Venkatesh]

what command should i use to read it as any other format of data and then to concatenate it?

Answer 1

[J. Alex Lee]

If all the files have the same format, I have seen big improvements using detectImportOptions() on the first file re-use those options for all subsequent ones (expecially if the excel files are formatted oddly):

https://www.mathworks.com/help/matlab/import_export/importing-tabular-data-into-a-matlab-table-with-customized-options.html

This is a cumbersome solution, but if you really need speed and will re-use a lot, may be worth it to interact directly with the Excel com server object, which in my experience is much faster to read and write data (at least within a single file).

https://www.mathworks.com/help/matlab/matlab_external/example-reading-excel-spreadsheet-data.html

Answer 2

[Prajwal Venkatesh]

I have a certain set of words stored in my program and I need to check if anyone of those words are in the excel files of my folder.

I currently have 22 excel files in the folder and I need to check in all sheets , all excel files if that word is present.

That is the reason I need to import all files in the folder all together in a single array to access it.

I cannot concatenate because it is huge data.

[Walter Roberson]

Pre-process the files into a binary search tree, and then searching would be fast.

https://www.mathworks.com/matlabcentral/fileexchange/45123-data-structures

[dpb]

"...I cannot concatenate because it is huge data."

What's the working definition of "huge"?

Excel is NOT the tool for really large files as you're learning. Walter's suggestion or other alternatives are clearly the way you need to explore going forward.

Still, it would be beneficial to SEE at least one of these files...we still have essentially zero knowledge to work on beyond the most general of descriptions.

[Prajwal Venkatesh]

Can i use an activex server to speed up the process?

[Prajwal Venkatesh]

[Prajwal Venkatesh]

I get this dialog box and when i click ok for 5 to 6 times then i the program runs and i get my results faster.

Now is there anyway i can get rid of this dialog box that pops up?

Thank You

[Prajwal Venkatesh]

Resolved the dialog box issue