Saving Multiple Outputs to the Same Excel File in One Workflow

[mkeiffer]

Hi everyone,

I am trying to save three different outputs to the same Excel, using different names for each worksheet for each unique output.  I a trying to do this in one single workflow.  However, no matter what I try, it seems I always get an error message on the 3rd and final output to the Excel file. I have tried using Block Until Done and also Control Containers, but nothing seems to work.

The error message I get either says the Excel file cannot be found or it is in use by another process.  It is interesting that I am able to get the first two outputs to work, but not the third output. 

I have attached the packaged workflow and the practice data files that I am using.  Any help with this workflow would be greatly appreciated.

Thanks in advance!

Mike

[caltang]

I’ve solved something similar here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Replacing-DCS-with-Alteryx/m-p...

You can download the workflow there and you can use it too.

[caltang]

Did something else for you @mkeiffer 

Though the trade off by doing this method is that your data types all become String. But if you use Alteryx to read the outputs, you can easily change the data types back. 

This method has always worked for me and is something I use daily as well.

Alternatively, you can use the Block Until Done feature, but I've been experiencing a lot of issues with that tool not working properly and I've even submitted a ticket to Alteryx support for their help.

So far this is the workaround I've been using. Hope it helps!

[mkeiffer · Accepted solution]

@caltang  Thanks so much for your help and for taking the time to post the workflows.  I was able to get my desired output by using a couple of different approaches.  I used the Block Until Done tool in one workflow and I also used the Control Containers tool in my other solution.  

I actually prefer the Control Container solution as I can now also determine the order in which to export my multiple results into one single Excel file.  As i move forward, I believe the Control Container tool will be one of my Go To tools.

I am posting just the workflows with the solutions.  You can get the actual source data files from my the Workflow package that I attached with my initial post.  

Hope everyone finds this to be helpful! 

[caltang]

Glad it helps somewhat! Thanks for posting your workflows too, but do note that control containers are only available v2023 onwards. It’s not backwards compatible with older versions as it doesn’t exist. So not everyone can enjoy using it…

[cswagner]

Hi,

I'm using control containers as well and experiencing the same issue... often only 2 of the 3 containers successfully write to the same Excel file.

All 3 of containers write to the different tabs in that file.

The containers are linked -> 1's out to 2's in, 2's out to 3's in. 

The 2nd container often complains the "process cannot access the file because it is being used by another process".

The Workflow usually succeeds after 1-2 reruns, but that's not a great end-user experience (especially when I port this to Alteryx Server).

I'm wondering if, more than just linking control containers, there needs to be a formal close file event/action (like most coding languages offer), before container 1 can pass context/activity thread to the next container.

Does Alteryx Support/Dev team have any thoughts on this problem (not on my conjectured solution)?

[SarahGouldman]

Interesting 🤔 I had this same issue but I was using regular tool containers. It was throwing the "..being used by another process" but every now and then it would work! Once I swapped the regular tool containers for Control containers and connected them in the right order, I haven't had the issue. I like your idea about closing a file before being passed to the next container. It might be neat to have an option within the Control Container configuration to specify what actions to take before considering that container "done" - though, if the control container functionality was working for you as intended, I reckon it wouldn't be an issue 😅