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

30 次查看（过去 30 天）
Elvira123
Elvira123 2019-1-22
回答： Jeremy Hughes 2019-1-23
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

请先登录，再回答此问题。

回答（2 个）

Jan
Jan 2019-1-22
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.
  4 个评论
显示 2更早的评论
Elvira123
Elvira123 2019-1-23
Effectively, I split results on various excel files, but I want to speed up the process.
Walter Roberson
Walter Roberson 2019-1-23
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.

请先登录，再进行评论。

Jeremy Hughes
Jeremy Hughes 2019-1-23
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.

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by