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

Question

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

TheEmeraldKnight · Answer

Hi Calvin

* 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.
* Are you using the AMP engine?
Yes I think so.
* 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!!
* Can you drop some columns which are not needed?
Maybe.
* 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

caltang · Answer

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-format-using-pandas-and-pyx

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:

* Why .xlsb?
* Are you using the AMP engine? 
* How big is your dataset?
* Can you drop some columns which are not needed?
* 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)

For Robert.yxzp

TheEmeraldKnight · Answer

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