Writing to multiple Excel Sheets in the same files - Using Block Until Done correctly

[haphan_tran]

I encountered this situation at one of my clients where they have to write a bunch of Sheets to the same Excel files. The workflow will look somewhat similar to below (simplified version with only 2 data streams and one formula tool for each stream)

Because Alteryx is trying to write to all files simultaneously, the file is being open by the first stream blocked the 2nd write stream. Users will get this error message:

Error: Output Data: Unable to open file for write: [File path] - Can't open file: [File path]: The process cannot access the file because it is being used by another process.

I saw a lot of people on Alteryx Community suggest user to use Block Until Done. It is the correct solution, it is just that without guidance, my client did this (and I believe some of you will too)

User will just drag the Block Until Done tool right in front of the output, and this tool will do absolutely nothing, because nothing is blocked, and 2 data streams are processed in parallel, and the error will still occur. So how can you correctly solve this issue?

You have an option to run this workflow on a faster machine (better CPU/RAM, how much better will depend on the complication of the workflow and the amount of data). I ran this workflow in my Gaming PC and it is fine, but on my 11Gen Intel Lenovo laptop, it fails. This option is not feasible for most situation because getting a better laptop in corporation environment is a pain, and it’s not gaming laptop either, so you may still have the issue.

Next option and actually easiest way to fix this is to turn off AMP engine (not working all the time, will depend on your workflow and amount of data)

Click the blank area in canvas → In Workflow Configuration Panel, Click Runtime Tab → uncheck “Use AMP Engine”

Another option is to change the Output options of the Output tool to Overwrite File (Remove) instead of Overwrite Sheet.

These options are available, but has some drawbacks: turn off AMP will not utilize the processing resource effectively, while remove file may remove some static data in other worksheets of output file. And I feel that those options is going too deep into internal of Alteryx, which not easy to understand for Business users, and can still cause intermittent issue. The advantages is that those options are easy to do, and you don’t have to think much.

Block Until Done to the rescue

Now to the important part, if you are like me (nerdy), and want to fix this issue logically, you can use the tool Block Until Done.

As you see from the tool icon, your data need to come in as one stream, then the tool will process stream one first, then second, then third. But your data come from 2 or more worksheets, you can you put it under one stream then use this tool?

The answer is that you will use Dynamic Input Tool to read from file path, and all your sources will be in a list of file path, then process each worksheet one by one.

To start, move all existing to the right of the canvas to make room for new tools. most of the existing tools will stay, only the Input tools will become Dynamic Input Tool.

You will have one input to get all the Sheet names configured as below to get the file path as well

Next you will put in the formular for Full_path

Here's the formula expression you can use to concatenate the file path with the sheet name:

Replace([FileName],"<List of Sheet Names>", [Sheet Names]) + "$"

Now you can add the Block Until Done and process each sheet. If you have more than 3 sheets, you will need to use 2 or more Block Until Done chaining together.

Here is the configuration for Dynamic Input Tool

Here is the final workflow

Please feel free to comment below if you have any concern or other options to fix this issue.

[harsh_alang]

For your awareness, there is a new functionality "Control Containers" that is being released in 23.1 in May. It works like tool containers but you can logically determine sequence of containers so you can write to multiple sheets in same file.

[EdP]

Additional suggestions can be found here:  https://knowledge.alteryx.com/index/s/article/Error-writing-Excel-file-to-Shared-Drive

[robert_west_PNW]

Control Container is such an awesome tool! Here is an example, one input, output is two sheets within the same file, the filter determines which sheet the result is saved.