Write in Excel file huge matrix

Question

Antoine Guilleux 2018-8-14

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/414758-write-in-excel-file-huge-matrix

编辑： Stephen23 2018-8-14

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

4 个评论
显示 2更早的评论隐藏 2更早的评论

Antoine Guilleux 2018-8-14

编辑：Antoine Guilleux 2018-8-14

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 2018-8-14

编辑：Stephen23 2018-8-14

"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 2018-8-14

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/414758-write-in-excel-file-huge-matrix#answer_332692

编辑：Stephen23 2018-8-14

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