Write large dataset in .xlsb-file

Question

Hey,

i'm currently working with a large amount of data, which originally comes from SQL and is prepared by myself in a lot of steps to around 370k rows with 120 columns. At the end i want to write the data to an .xlsx-file for using it afterwards. I heard that using .xlsb is also useful for big data, because it reduces the needed memory, so i tried both.

I've now experienced the following:

* Writing the data to a .xlsx-File lasts about 8 min and returns a 190MB output (with Alteryx Workflow and Analytic App)
* Writing the data to a .xlsb-File lasts about 39 min and returns a 110MB output (with Alteryx Workflow)
* Writing the data to a .xlsb-File via an Analytic App leads to an incredibly long runtime, which I canceled after over an hour

Can anyone explain why Alteryx has such problems with .xlsb and are there any workarounds to deal with it? Since using an Anlaytic App is a must have, but a runtime in this dimension is not acceptable.

Thanks & best regards,

Mathias

MathiasL · Answer

Yes, but in my opinion this is not a good solution because the user then needs an extra step before watching on the final product.

And in my case i would need two .csv-files because currently i have two tabs of data.

jdminton · Answer

Have you considered exporting to .csv and using Power Query to bring the data into Excel? It would allow for the data to be stored in .csv and formatting to remain in Excel template.

MathiasL · Answer

Thanks for your tips. I have a Block Until Done tool placed in my workflow before the data is written to the files, because the data will be written into two separate tabs in the file and if i wouldn't place it there, i get an error because of writting two different informations in one file at the same time.
A .csv file is no option, because formatting the data is also a must have and .csv doesn't store the formatting information.