community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

How to write to multiple tabs separately without errors

Alteryx
Alteryx
Created on

Have you ever tried writing to multiple tabs within the same workflow and have received an error like this:

 

“Error: Output Data: Unable to open file for write: FULL_FILE_PATH.xlsx Error Opening file: FULL_FILE_PATH.xlsx: The process cannot access the file because it is being used by another process.”?

 

This is occurring because Excel, like many applications, will get confused if multiple processes are happening to it at once which is why you can’t have the same excel file open while you are reading it in to Alteryx. To avoid simultaneous writing, there is an awesome tool called the Block Until Done Tool which will prevent this.

 

The Block Until done tool will wait until all processes upstream are finished before sending work downstream. This tool has three outputs. What is useful about it to us in this case is each output will be processed in sequential order. To illustrate:

 

exceltaberror.jpg

 

This will always error out because the output tools in Alteryx are being used at the same time thus Excel will get confused.

 

BUDexcel.jpg

 

In contrast this will always work because the second stream will not kick off until the first stream is finished.

 

So all you have to do when writing to the same file but different tabs is make sure you use a Block Until Done to prevent it from writing to the excel file simultaneously.

 

For more information on the Block Until Done Tool click here

Comments
Atom

Thank you so much for this-- you've just condensed three separate logs (my stupid workaround of this problem) into one log that I can reasonably graph! Thanks!

Atom

Wow I have no shame, I'll ask the most basic question.  How do set up the output excel file to have multiple sheets, sheet 1, sheet 2, etc...  This is the solution I'm looking for to combine multiple flows into one excel rather than several.  thank you

Atom
No worries. You'll just need to add multiple "write to excel" blocks. Note: This assumes that you already have an excel file saved somewhere (even if it's blank). If you don't have one, you're not going to be able to do this.

When you select your excel file, there should be a drop-down menu where you can select "file browse." Do that, then select the file you want to write to. It will prompt you to select a tab to write to, and you can generate a new tab with your own naming convention if you'd like to. Repeat as needed.
Asteroid

This is so helpful! 

Asteroid

Hi there! I have a similar problem that can't seem to be solved with the Block Until Done tool. I set up one workflow that has multiple outputs exported to different tabs on the same Excel file. The ones that I chose to "Overwrite Sheet" worked, while the ones with "Append to Existing Sheet" gave me the error of "unable to open to write for file: ... the file is being used by another process." I put the Block Until Done tool before the Output tool and the same error happened. Does anyone know what's wrong? I attached a screenshot, in case that is of any help. 

 

Thanks!

 

Capture.PNG

Asteroid

Hi All,

I think I'm having the same problem as @serhoshu1118. If the workflow uses a filter to separate the data into two sets, each of which goes to a different sheet in the same workbook, the Block Until Done can't be used.

Unless there is a better solution (anyone?), it looks like the only option is to put the each output tool into its own Tool Container and disable one while the first is run, and then re-run the whole workflow with the first output disabled and the second one enabled.

 Capture.PNG

 

Alteryx Certified Partner

@asteryx - you can use Parallel Block Until Done (from the CReW Macro Pack) to accomplish this.

 

Better late than never?

Asteroid

Returning to this thread as i think I've found a solution I wasn't aware of before.

In the Output tool, there's an option to specify the "table" to write to via a field in the stream.

I added a formula tool to each stream that named the sheet it is intended for. Then I used a Union tool to send them all in to the same output tool.

The records get written to the same workbook, but go to the sheet specified in the field.

Outputsnip.PNG

 

Meteor

I would suggest that this is a more common use case

 

Capture1.PNG

It would be very useful if it where possible to build the "block until done" functionality into the filter tool because the following looks really ugly to my eye.

Capture2.PNG

Perhaps there is a better alternative?

 

Unfortunately asteryx's solution above doesn't work because my output streams will have different data formats and so joining them with a union will add a large number of empty columns to each worksheet.

Meteor
Block until done only waits for the first icon on the 1 to finish - after that execution can be based on icon creation (id number ) or other things. THe reason this example works for you is because the data set is so small you built in a half second delay.
Asteroid

Just a little brainstorming in case it helps...

If your outputs have different schema, could you first output them to separate yxdb files and then use a final step to write each to the same Excel file?

The final step might work if it's a batch macro that runs the write once per yxdb.

Atom

 

I have a related question. How can I control the order of the tabs in my Excel output? My workflow has 3 separate flows and each flow generates a tab in the same Excel output. Then I have a Summary tab that summarizes results from each flow. When I look at the sequencing of the flow in the results window, I can see the tabs are getting generated in this order: A, B, C, Summary.  But in the actual ouptput that gets posted, I see A, B, Summary, C.

 

Thanks in advance for your help!

 

Asteroid

I'm just guessing, but I suppose it could be writing based on which ever one is ready first.

If you're using my suggestion of adding the sheet name and writing from a single output tool, I don't see a solution, but if you go back to the other approach with a separate output tool for each sheet, it might work with a Stop Until Done tool.

Asteroid

So if I read this trail correctly. If I am writing 12 tabs in a single Excel file I may need to add 6 "Block Until Done", correct?