MATLAB Answers

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

51 views (last 30 days)
Elvira123
Elvira123 on 22 Jan 2019
Answered: 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.

Answers (2)

Jan
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
Walter Roberson
Walter Roberson on 23 Jan 2019
writetable() will write the data that large for xlsx, but excel cannot read anything that big.
Elvira123
Elvira123 on 23 Jan 2019
Effectively, I split results on various excel files, but I want to speed up the process.
Walter Roberson
Walter Roberson on 23 Jan 2019
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.


Jeremy Hughes
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.

Sign in to answer this question.