Error with using xlsread on 9000 files

Hello dear friends,

I have a problem with using xlsread on a large amount of files (over 9000 excel files). When I try to read every single one of them, I get a random error after about 5000 files. When I manually continue with the exact same file that causes the error, it will work just fine. But I am working on automatically reading all the files without having to manually correct each time I try to load them into my workspace.

So what I tried to do is that I used try and catch, something like this (pseudo-code):

i=1;
while i<MaxNumberOfFiles
try
data(i) = xlsread(...);
i=i+1;
catch
end
end

But it won't work since it's caught in the loop without end (always catches the same error). I think it might have something to do with restarting the whole "Matlab-running-the-process" when I manually correct the error and it works. Did anyone experience something like that before and could I avoid the error by using something like a timeout or memory-refreshing?

Looking forward to your ideas

Roman

[Ingrid]

have you tried if the problem also occurs if you use textscan? I always use this one because you can specifically close the file again (fopen and fclose) and you have more possibilities of manipulating how the data is read in

[Roman]

To be honest, I quit using textscan becaus for my problem I found xlsread to be much easier to use. I always have a column of dates and two columns of numeric data, which can be easily seperated using xlsread.

And leaving out the one error I have everytime I run the program, I am very happy with xlsread.

Nevertheless thank you for your answer Ingrid.

Answer 1

[Stephen23]

Here is one way of reading semi-colon delimited CSV [sic] files quickly using textscan, which has the advantage that the file can be closed via fclose. The data file you supplied (attached below) is complicated by the use of a comma , as the decimal radix point, so I read the second column as strings to convert to numeric later. As such it would be easier if the file was saved as a true CSV file (with , delimiter and . radix point).

In any case, this will read your sample data file:

% Read CSV file data:
fid = fopen('SampleFile.csv','rt');
hdr = regexp(fgetl(fid),';','split');
C = textscan(fid,'%s%s%f','Delimiter',';');
fclose(fid);
% Convert decimal comma to period:
C{2} = str2double(strrep(C{2},',','.'));
% Convert to numeric matrix and serial date number:
mat = horzcat(C{2:3});
dtn = cellfun(@datenum8601,C{1});

Note that the last line requires my FEX submission datenum8601, which will convert those beautiful ISO 8601 date strings into serial date numbers.

[Roman]

Thank you very much Stephen!

Since I have read everything with xlsread until now, everything was in cells anyways, so it's no problem dealing with that. I will try your solution on the whole 9000 files and tell you if I can successfully read all of them.

[Roman]

Works perfectly fine, thank you! And it's way faster, too.

Answer 2

[Walter Roberson]

Some MATLAB versions have failed to close xls files after reading them, which causes the session to run out of open file descriptors. If that is happening to you then a periodic close('all') can be useful (but of course that will close any file you might have wanted left open, such as if you were writing results to a file, so you need to take that into account.)

[Image Analyst]

I'm pretty certain the "non-closing" of Excel between calls to xlsread() started with R2015b, because that's when it started breaking my apps.

What version do you have and what operating system? If you're using Windows, you can use ActiveX to read the files and close the workbook and use the same file descriptor (I think) if what Walter says is true.

[Roman]

Thank you for your answers, sounds like that could be it.

I am currently using R2015a. I am saving the results in a cell-file, so that should not be affected by the close all, should it?

I will give it a try and tell if it worked.

[Guillaume]

If the problem is indeed that workbooks are not being closed, then it's fairly easy to force close them (assuming they've not been modified):

excel = actxserver('Excel.Application');
excel.Workbooks.Close

will close all open workbooks.

It would be useful to know what the actual error is, though.

[Walter Roberson]

Image Analyst, R2015b is the version where they started deliberately caching Excel file connections. But versions before that have sometimes just forgotten to close open files.

[Roman]

So, I ran the program once again, to create the error:

Error using xlsread (line 247)
Error registering event(s), Advise failed

Error in Einlesen (line 94)
    [numbers,dates,intraday_forecasts{i,2}] = xlsread(filename);

("Einlesen" is the name of the script I'm running) I will try again using Guillaume's code now.

[Roman]

Unfortunately

excel = actxserver('Excel.Application');
excel.Workbooks.Close

didn't work.

I will give "close all" a try.

[Jan]

@Roman: Please explain "didn't work" with details. Did you get an error message? Where did you insert these 2 lines?

[Stephen23]

Roman's "Answer" moved here:

Thanks for the answers so far, but neither close all nor excel.Workbooks.Close works.

The first produces the same random error (after about 5000 files), the latter produces the same random error, just in an earlier stage (after about 2000 files).

Any more ideas?

[Stephen23]

Use textscan with fclose afterwards. It is not hard.

If you upload a sample of your file then we can show you how to use textscan. Click the paperclip button, then both Choose file and Attach file buttons.

[Roman]

@Jan: My code looks like this:

i=1;
while i<MaxNumberOfFiles

data(i) = xlsread(...);
i=i+1;

excel = actxserver('Excel.Application');
excel.Workbooks.Close

end

and the other option:

i=1;
while i<MaxNumberOfFiles

data(i) = xlsread(...);
i=i+1;

close all

end

Both brought me the same error code as mentioned above.

@Stephen:

Unfortunately I can't publish those files since they are restricted. Moreover I doubt the problem results from the files themselves, but is a Matlab/Excel problem.

[Roman]

@Stephan:

Let me describe you the excel files:

I have 3 columns: First one are dates in string-form, Second and third one are numerical (doubles). All of the columns have the same length.

So I read a date(string) and two numbers(doubles) per row.

[Stephen23]

What file format are the files? What delimiter is used? Are the columns fixed width? Are there leading spaces? Are the strings surrounded by quotation marks? Do the date strings contain space characters? What format are the date strings? Are there trailing spaces?

So much that we don't know, because a "description" does not give enough information. That is why I asked for an upload of a sample file.

You don't have to upload one of your restricted files: just create one that has the same layout, with some fake data. It does not even have to be the same number of rows, just fake a bit of data that looks the same and is arranged in the same way. Then we have something to test our suggestions on.

[Roman]

@Stephan:

Great idea. I will do so tomorrow and post it here.

Thanks for your help.

[Roman]

@Stephan:

Here is a samplefile of how my data looks like:

[Stephen23]

Thank you. I wrote an Answer using your CSV file.

Answer 3

[Image Analyst]

Processing 9000 files with xlsread() will basically take .... well .... forever. This is because it must launch Excel, do the reading, and then shut down Excel. You know how long it takes to launch Excel? Well imagine doing that 9000 times. There is no way you should be doing that with R2015a. I use Active X for anything over about 3 files. You must use ActiveX if you want to get done this year. I attach a demo.

[Roman]

Well it takes about 20 minutes which I consider to be quite okay, if it would work without my interference.

[Image Analyst]

You might try readtable() instead of xlsread(). It generates a table instead of a cell array, which has way less memory so it might be faster than xlsread(). Worth trying anyway. Time with tic and toc.