Readtable function has some kind of limitation on the number of rows and columns?

Hello i am using the function readtable to read a table from excel that has ~1.000.000 rows and 42 columns but it doesnt work, matlab has some kind of limitation on the number of rows and columns?

Any way that i can load my data?

Thanks Tiago

A = readtable('test.xlsx);
x = A{:,1}; % 1st column is data time
y = A{:,2:end};

Even with 10 rows and 40 columns, when i execute the readtable, it creates me a table 0x0 no idea why.

A = readtable('u3300.xls');

Answer 1

[Guillaume]

Stepping through the xlsread code and looking at the content of the excel file in excel VBA editor reveals the reason why you get an empty table:

The excel file has actually two worksheets. Sheet 1, the one that matlab reads by default, has the attribute xlSheetVeryHidden meaning it can't be unhidden through the standard excel hide/unhide interface, only through VBA. Note: that very hidden sheet is called AspenPDXLAddin, so presumably is a left over of an Aspen process add-in.

You can blame who you want here. Maybe matlab shouldn't try to read hidden or very hidden worksheets, maybe Aspen shouldn't hide their worksheets, or maybe excel shouldn't allow worksheets to be very hidden. Anyway, the workaround is simple, specify the worksheet to read:

readtable('U-3300 Process.xlsx', 'Sheet', 'U-3300 Process')

However, you then have the problem that excel reports that the used range of the 'U-300 process' worksheet is '$1:$969121', so matlab tries to use that range and ends up trying to allocating a 969121 x 16384 array which would require >127 GB of memory. So of course, out of memory error. Again, you can share the blame as you want. Maybe matlab shouldn't use the UsedRange property of the worksheet as it's known to be unreliabl, but then excel should provide a reliable way of getting the used range. Or maybe Aspen shouldn't have filled the sheet in such a way that the used range is calculated incorrectly. The reason the used range is reported as such is because column XFD of the spreadsheet has a custom width. (You can quickly see the used range in excel by pressing ctrl+end).

In the end, you need to both provide the sheet and the range for matlab to read that file correctly:

readtable('U-3300 Process.xlsx', 'Sheet', 'U-3300 Process', 'Range', 'A1:AP969121')

Note that it takes a while because excel takes a long time to open the file. Then readtable spend a long time finding out the type of the columns. If possible, I would recommend a different format than excel to get the data out of the Aspen software. Otherwise, you may be better off using xlsread, then converting to table.

[Tiago Dias]

ok i will try that to specity the Sheet and the range, didnt think of that.

I also tried to load as an csv file but i got the same memory low "error"

[Tiago Dias]

With the sheet and the range my compute doesnt make it Error using readtable (line 135) Out of memory. Type HELP MEMORY for your options.

So i will try the xlsread option.

Can you,please, tell me if it run for you until the end? so i can know at least that is not something wrong with the format of the data?

[Tiago Dias]

xlsread with saying the nanme of the sheet and the range and it doesnt work as well :S

[Guillaume]

Using readtable, I gave up while debugging as it was spending too long just finding out the type of the columns.

Using xlsread then yes, I get the content of the file. It takes a while! There are 3 main steps to xlsread:

1. Ask excel to open the file. That takes about a minute on my machine
2. Read the worksheet content in a cell array 5000 rows at a time. That takes about 2 minutes on my machine.
3. Split the content of the cell array into numeric and textual data. Another minute on my computer.

Note that step 2 will allocate a 969121 x 42 cell array, which when filled requires about 4 GB of memory on its own. Most of it is converted into a matrix at step 3. But you need at least that much free memory for step 2 to succeed.

[Tiago Dias]

A = xlsread('U-3300 Process.xlsx','U-3300 Process','A1:AP969121')

So it is just like this?

If it is not to much to ask, what is the specification of your computers in terms of processor, and Memory RAM that i guess is the important things to run this part.

[Walter Roberson]

Good sleuthing, Guillaume!

[Guillaume]

@Tiago, yes just like that.

The computer I was using is not that high performance: core i7-3770 with 16 GB of ram. Matlab R2017b on Windows 7.

[Tiago Dias]

thanks for your help, my laptop has 8gb ram i will try to run on it to see if it works

[Guillaume]

Note: if memory is really an issue, it would be possible to write an xlsread alternative that would use less memory (at the expense of time) by reading chunks of the spreadsheet then immediately converting them into a matrix rather than the method used now that reads everything into a cell array (requiring 4 GB) then converting the whole cell array into a matrix (which only requires 400 MB) at the end.

[Tiago Dias]

And how to do that? because yes memory is a problem, because after loading all the data and making the plots for each variable, i end up with 500 mb of memory to to things that i need to do like cleaning out points and stuff.

I will still try to use the xlsread, because i jsut tried it the readtable on my laptop it works but i have 500 mb of memory available for the rest

Answer 2

[Guillaume]

"but it doesnt work" is a useless statement on its own. In what way it doesnt work. You get an error? (if so, which one?), you get an incorrect result (in what way?), your computer explodes?

Matlab does not have any limitation on the size of a table other than your computer memory. A million row and 42 columns of numbers is certainly not a limitation, it only requires ~336 MB.

[Tiago Dias]

Clearly, I was very rude in my question, and I apologize for that.

It doesn't work in the way that the excel table is not loaded. It loads a Table 0x0 and I don't know why, since my other table has 44 columns and ~ 500rows and it loads. "

[Tiago Dias]

and when i use a range:

clear;
clc;
close all;

 B = readtable('U3300process.xlsx','Range','A1:AF500000');

In the command window says:

Error using readtable (line 135)
Out of memory. Type HELP MEMORY for your options.

Error in teste (line 5)
B = readtable('U3300process.xlsx','Range','A1:AF500000');

[Walter Roberson]

The xlsx file format does have limits, which are 1048576 rows and 16384 columns. "~1000000" rows is getting close to that limit. However, encountering that limit would not result in "Out of memory": for that you just do not have enough memory in your system to store all of the temporary values.

Note that on systems that are not MS Windows or which do not have Excel installed, then xlsx files are processed as text, and the range operation is handled by parsing the entire file and then discarding the parts that are not wanted.

[Tiago Dias]

The computer i am using has Windows and Excel installed, is there a way to know how many memory should i have to process this excel sheet?

https://www.dropbox.com/s/mh62y91hv826tv2/U-3300%20Process.xlsx?dl=0

[Walter Roberson]

I do not know of any way to predict the amount of memory needed for that configuration. I am not very familiar with the details of how ActiveX works in such situations.

[Walter Roberson]

Note: sheet 1 of the above xlsx file is empty; the data is in sheet 2.

[Tiago Dias]

thats not a problem, readtable reads whatever is on excel, and i can just load the first three columns, the fourth acuses low memory and it aborts there

Answer 3

[Peter Perkins]

You have not provided any sample of what's in your spreadsheet, so pretty hard to say what's wrong. Best guess is you have a lot of text, which is causing memory issues. In recent versions of MATLAB, you can use the 'TextType' and 'DatetimeType' to use less memory in the imported file.

[Tiago Dias]

I dont know if i am allowed to put links or not, since my excel sheet is over the limit to attach.

https://www.dropbox.com/s/mh62y91hv826tv2/U-3300%20Process.xlsx?dl=0

How to use the TextType and DatetimeType to try to resolve the issue?

[Tiago Dias]

even with this excel sheet it creates me a table 0x0 no idea why, becuase my first column is time, and i choose in the excel "date" and the numbers as numbers

[Walter Roberson]

Your u3300.xls file is corrupt according to a couple of different utilities, but Excel itself is willing to read it. You could try looking at https://blogs.msdn.microsoft.com/officeinteroperability/2011/07/12/microsoft-office-binary-file-format-validator-is-now-available/ for a tool that might be able to tell you what the problems are with the file.