How to increase Excel export speed?

[hannah_s]

[ FlexSim 20.2.3 ]

During an experiment I write values to ~30 different tables and at the end of the experiment I export all of the tables to Excel. Initially when I ran the export (8 months ago) the export would take 5-10 minutes. Over time the export time has slowly increased to where it now takes an hour to export the tables. Is there a way to speed up the Excel export process? Why was it running so quickly before and so slowly now? This problem is not unique to my computer. My coworker faces the same issue as well. It's also not an issue with the model. The version of the model that ran quickly before runs slowly now.

[JordanLJohnson · Accepted solution]

As far as why it is slowing down, it's hard to say without a model. Are the file sizes of the Excel Workbooks bigger in later versions? Did the slowdown happen when you switched versions of FlexSim? If there is a bug here, it would be good for us to fix.

In the meantime, you need a way to export faster. Statistics Collectors and Calculated Tables are optimized for Excel Export, and can export data in seconds instead of minutes or hours.

So the easiest way to export all your data fast would be to make one Calculated Table per Global Table, with a query like this:

SELECT * FROM GlobalTable1

You'll probably need to click the "Enable Direct Editing" button on the Calculations tab of the Calculated Table, to type in a query like that.

Then, you'll need to change your Excel Export object to export the global tables.

Finally, before exporting, it would probably be good to make sure the Calculated Tables are up-to-date. Run this script to re-run all queries in all Calculated Tables, before you export:

treenode calcTablesFolder = Model.find("Tools/CalculatedTables");
var calcTables = calcTablesFolder.subnodes;
for (int i = 1; i <= calcTables.length; i++) {
  treenode calcTable = calcTables;
  function_s(calcTable, "update", 1);
}

Then click the Export button. Here's an example model:

FastExportDemo.fsm

[hannah_s]

Thanks for the response! How do you set up a calculated table without a statistics collector?

[JordanLJohnson]

Calculated Tables can query any Global Table (or Statistics Collector) in the model. The most common use is, as you say, to query a Statistics Collector. But a Statistics Collector is not required to create a Calculated Table. You can just add one from the toolbox. Then, you can query a Global Table like this:

SELECT * FROM GlobalTable1

You can write that query on the Calculations tab of the Calculated Table, and click the "Enable Direct Editing" button.

[ryan_c10]

Hi @Hannah S, was Jordan Johnson's answer helpful? If so, please click the "Accept" button at the bottom of their answer. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always unaccept and comment back to reopen your question.