MATLAB Answers

0

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

Asked by Elvira123 on 22 Jan 2019
Latest activity Answered by Jeremy Hughes on 23 Jan 2019
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

  0 Comments

Sign in to comment.

2 Answers

Answer by Jan
on 22 Jan 2019

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.

  4 Comments

Show 1 older comment
writetable() will write the data that large for xlsx, but excel cannot read anything that big.
Effectively, I split results on various excel files, but I want to speed up the process.
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.

Sign in to comment.


Answer by Jeremy Hughes on 23 Jan 2019

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.

  0 Comments

Sign in to comment.