Using writetable for a very large file

I have a table with around 1323559 lines which I want to write into a .xlsx file.

When I use:

writetable(T,'blabla.xls');

Error: Error using writetable (line 124)
The data block starting at cell 'A1' exceeds the sheet
boundaries by 1323559 row(s) and 0 column(s)

When I use:

writetable(T,'data.xlsx','Sheet',1,'Range','A1:A1323559');

Error: Error using writetable (line 124)
Range input exceeds bounds for this file extension. Row values must be between 1 and 1048576 and
columns values must be between 'A' and 'XFD'.

How do I fix this so I can print the table to an excel file?

Answer 1

[Walter Roberson]

You are asking to write out more rows than are permitted in Excel; the maximum is 2^20, which is 1048576 . If you did somehow manage to write out a .xls or .xlsx file containing all of the rows, then most programs would be unable to read the data.

You should be considering splitting the data into multiple sheets, or using a different file format.

[alaa]

so what is the best file format that is able to wrok with large data (around 1163264 or more rows) ?

can you pls suggest certian types , that are compatable with MATLAB?

[Walter Roberson]

You would keep the data as .mat files if you did not need to have external programs read the file, so the question becomes which file formats can the external program read?

Also what is the size of the data in bytes? With that many rows, if the data needs double precision, then beyond approximately 230 columns you would start needing more than 2 gigabytes to store the data in binary format, which could be a problem for some formats.

[Tiago]

If you do need to export data this large to Excel try exporting as csv!

[Judith Orinda]

This question solved one of my problems! Thank you

[Walter Roberson]

Excel cannot read csv files that have more than 2^20 rows.