Workflow very slow if output is a an Excel binary *.xlsb file

[TheEmeraldKnight]

Hi there,

I often work with large datasets and so rely on binary files to keep file sizes to a minimum. Recently, I created a workflow with the output set to a binary file, but it was taking way too long to process. To address this, I switched the output format to *.xlsm, which improved the speed but caused the file size to increase significantly.

I suspect that Alteryx struggles with writing to Excel's binary file format because it uses a compressed (zipped) binary structure, unlike the XML-based format of *.xlsx files. I’ve also noticed that the Output Data tool options differ when working with these formats.

Is there a way to continue using binary files while ensuring the workflow runs efficiently?

Regards,

Robert

[caltang]

Have you tested the performance if you were to write out the output via Python tool in Alteryx? If you are keen to keep BaseA (Normal vanilla Alteryx tools).

There are some discussions on this as well:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Write-large-dataset-in-xlsb-fi... 

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Excel-file-xlsb-exported-shows...

I don't have much else to add - but if you can write the output via Python, I am assuming it works better.

[TheEmeraldKnight]

Hi Calvin,

Thanks for your prompt response.  I don't know Python and I am only just learning Alteryx so that's over my head at this stage I'm afraid.  Would have a workflow you could share?  I need to consolidate various workbooks (usually the first sheet) into a standalone workbook where each sheet is populated from the workbook being imported.

I can code in VBA so could write a *.vbs file to convert the file using the Run Command tool to execute it I guess but it's not a great solution in my humble opinion.

Many thanks,

Robert

[caltang]

Actually @TheEmeraldKnight Robert, I must apologize as I stand corrected. I did some digging and it seems difficult to convert it to .XLSB via Python. The closest I've seen is by people converting it to .xlsx (easily) then using a new converter to convert the .xlsx into .xlsb. Read more here: https://stackoverflow.com/questions/63572036/write-output-in-xlsb-file-format-excel-binary-file-form...

Since you are a beginner and looking to focus on Alteryx, then I would strongly suggest for you to stick to BaseA tools. Let's review your workflow if you can - let's see what we can speed up. 

First checks are:

1. Why .xlsb?
2. Are you using the AMP engine? 
3. How big is your dataset?
4. Can you drop some columns which are not needed?
5. Are you open to an intermediary step (perhaps converting your output to .yxdb, then using a chain app to convert it .xlsb thereafter - the gains are minimal based on my own test)

[TheEmeraldKnight]

Hi Calvin

1. Why .xlsb?
   The workbooks have macros so we must use either *.xlsm or *.xlsb as *.xlsx cannot house macros by design.  We use *.xlsb where possible to keep the file size down to a minimum.
2. Are you using the AMP engine?
   Yes I think so.
3. How big is your dataset?
   I work for a multinational organization so they can be quite large.  Just one of the tabs is for a daily TB.  The last imported file had 27 columns x >123k rows!!
4. Can you drop some columns which are not needed?
   Maybe.
5. Are you open to an intermediary step?
   Yes and I really appreciate the suggestion and sample workflow.  I've not yet used an Alteryx Database workflow so let me have a look and revert if required.

Many thanks,

Robert