how to read big excel file faster?

Hi

I have a big excel file like 200MB and I am using xlread(). It is easier to use xlread(). But as the number of variables in the code increases over the time. It takes really long to open the excel file and close again using xlread(). The excel format is attached.

Is there other alternative way to read big excel file faster? Thank you so much. I appreciate it.

[Bob Thompson]

It takes some learning, but you can use matlab to open a portal to excel. The basic opening command is:

actxserver('Excel.Application')

The benefit of this environment is that it does not open and close the excel file every time you do something like xlsread and xlswrite do. There are whole different set of commands which can take a while to learn to operate efficiently though.

[Walter Roberson]

In relatively recent versions, xlsread() holds on to information about the last file it opened, so that it does not need to open it again unless you ask to read a different file.

[Bob Thompson]

Ooooooh, that's a fancy update. As much as I do like it though the project I was working at the time needed the extra formatting and plot creation (within excel) that could be done through the COM connection.

But yeah, Walter knows more than I do.

[bim]

I packed the demo of @Image Analyst into a simple static class.

It keeps Excel open in the background while you read data from multiple files.

It has the limitation of only reading contiguous tables and only columns A-Z.

[Walter Roberson]

@bim

https://www.mathworks.com/matlabcentral/fileexchange/22365-function-for-faster-data-transfer-matlab-excel?s_tid=srchtitle -- reading caching the connection

https://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1 -- writing caching the connection

But in sufficiently new versions of MATLAB (r2017b-ish) xlsread() and xlswrite() were modified to cache the connection so these functions are only needed for older releases (or for the case where you want to get ahold of the activex handle for fancier operations.)

Answer 1

[roudan]

Thanks Walter and Bob, are you guys talking about the same thing like using actxserver('Excel.Application') when Walter was saying ActiveX? Any more examples? Thanks

[Walter Roberson]

Yes, calling actxserver() is what I was referring to.

See https://www.mathworks.com/matlabcentral/fileexchange/22365-function-for-faster-data-transfer-matlab-%3C-%3E-excel

[roudan]

Thank you Walter. I appreciate it.

Answer 2

[Walter Roberson]

If this is just a one-time read of the whole file, and you are doing this on MS Windows with Excel installed, then xlsread() is about as fast as you can get. xlsread() does have some overhead for matters such as figuring out worksheet names, so it is possible to set up for reading a bit faster by hard-coding that kind of information, but once that is set up, the ActiveX connection works about as fast as could be.

You could also experiment with readtable(). For xls files the binary format is examined and parsed somewhat efficiently, but the code is at the MATLAB level so using ActiveX would typically be more efficient because Excel is compiled. For xlsx files when Excel is not available, readtable() uses regexp() to parse the text after having to go through a series of set-up steps, and although regexp() is one of the faster operations in MATLAB, this is still going to be slower than using ActiveX to Excel.

[roudan]

Hi Walter,

Today I just open the excel file using xlread(), I got an error like this.

Error using xlsread (line 247) Error: Not enough storage is available to complete this operation.

Previously I can open the big file just speed is slow. Now I added many more global variables. Now I cannot even open it because of space issue. Is it because of many global variables which take up space even they are assigned to values? Thanks for your help. I appreciate it.