Write in Excel file huge matrix

Hi everyone,

I am looking for a way to write/import HUGE matrix in an Excel file, or equivalent software. The concerned matrix are cell arrays, have more than 14.000.000 rows and a couple columns, containing strings. I tried different things:

-the famous 'xlswrite' but it only writes 65.000 rows. I tried to iterate with a for loop, but Excel is not able to find higher indexed cells like 'A500000', so that solution does not work this way.

-use a csv file but the problem is the same: even if Matlab tells me it works, when I open the csv file, I can only see around 1.000.000 rows, the rest is not displayed.

-use commands with 'actxserver' but nothing to do, the matrix are too heavy and my computer was about to crash.

Excel is not a requirement, but clearly the most practical software to show and manipulate my results. Any ideas?

Regards,

Antoine

[KSSV]

How about saving it in a mat file? Or a netCDF format?

[Antoine Guilleux]

Here is what I got trying to save in a mat file:

save('test_inputs.mat','inputs')
Warning: Variable 'inputs' was not saved. For variables larger than 2GB use MAT-file version 7.3 or later.

And I don't know why... But I'll try netCDF format (never used it before, let's check this!)

[Antoine Guilleux]

Ok fine Stephen, thanks! I'll try something else then, my mentor told me to try with FlexPro for example, it can also be used for that apparently (but don't know its limits yet...).

[Stephen23]

"And I don't know why"

Because the .mat file standards pre 7.3 have a limit of 2^31 bytes per variable, or smaller:

https://www.mathworks.com/help/matlab/import_export/mat-file-versions.html

Answer 1

[Stephen23]

Excel simply cannot display that many rows, no matter how much you want it to.

"...even if Matlab tells me it works, when I open the csv file, I can only see around 1.000.000 rows, the rest is not displayed."

Excel 2007 and later versions only support up to 1048576 rows, well short of what you need. There is no way to get Excel to show more rows than it supports. So this problem has nothing to do with MATLAB, it is simply how Excel is made:

https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

"use commands with 'actxserver' but nothing to do, the matrix are too heavy and my computer was about to crash"

I would not recommend using any Excel document for storing this much data. A simple CSV file would be much more reliable, or multiple CSV files. You should to use something that does not load all of the data into memory at once... such as MATLAB tall arrays:

https://www.mathworks.com/help/matlab/import_export/tall-arrays.html

Or use matfile with a version 7.3 or later .mat file (limited only by the total number of bytes, not by how many rows of data).

You should read what the MATLAB documentation advises about big data:

https://www.mathworks.com/help/matlab/large-files-and-big-data.html

https://www.mathworks.com/videos/tackling-big-data-with-matlab-1481575727948.html