How can I speed up "writetable" for large tables?

I need to write a huge table (around 5000000x50) on an excel file. For now, I'm using "writetable", but it takes a lot of time (20-30 minutes). How can i speed up the process by considering that the table has both strings and numbers and the type of element in the table can change each time?

Thank you

Answer 1

[Jan]

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

Total number of rows and columns on a worksheet: 1,048,576 rows by 16,384 columns

This should mean, that 5'000'000 exceeds the maximum number of rows. So if the creation of the table takes 20-30 min, I'm surprised, that it works at all. Please check this. If it does not write the data completely, there is no reason to increase the speed.

[Elvira123]

I'm using Matlab version 7.3 (variable can exceed 2GB) and I can create and use big tables. It writes all data correctly on the excel file, it just takes a lot of time. So, I'm surprised too.

[Walter Roberson]

writetable() will write the data that large for xlsx, but excel cannot read anything that big.

[Elvira123]

Effectively, I split results on various excel files, but I want to speed up the process.

[Walter Roberson]

If you have MS Windows with Excel installed, experiment with disabling (uninstalling) Excel. That will trigger writetable() to use internal routines that are different speed than going through the interface to Excel.

Note that .xslx files are fundamentally a zip'd directory of xml text files, so writing a lot of data takes time to write and time to internally zip afterwards.

Answer 2

[Jeremy Hughes]

As a more programatic solution based on Walter's suggestion: If you're in a recent release (I think 18b), you can provide the 'UseExcel' parameter to disable communication with the Excel process. This generally speeds up writing since Excel likes to inspect the data and "do things" like evaluate formulas and try to convert text to dates as you write the data.

In general, I wouldn't suggest Excel for storing large datasets, but that really depends on what you're doing with them and what data you have.

Without any more context, my reccomendation would be to use CSV. It can be read by Excel, and a lot of other software as well.