Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How to write to multiple tabs separately without errors

Ozzie
Alteryx
Alteryx
Created

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 youhave to do when writing to the same file but different tabs is make sure youuse 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.

No ratings
Comments
PeteCR91
5 - 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!

SpencerS
5 - 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

PeteCR91
5 - 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.
MsBindy
8 - Asteroid

This is so helpful! 

serhoshu1118
8 - 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

asteryx
8 - 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

 

Garrett
11 - Bolide

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

 

Better late than never?

asteryx
8 - 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

 

nhunter
7 - 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.

Fgill
7 - 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.
asteryx
8 - 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.

ak123
5 - 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!

 

asteryx
8 - 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.

Andy_Katona
8 - 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?

JJH
7 - Meteor

How can I use Block Until Done when outputting 4 different data streams from a Macro output?

You can only input 1 data stream into Block Until Done.

alexlyle
8 - Asteroid

A coworker and I are getting this error. I created a flow that updates multiple worksheets in the same excel workbook. It works fine for me. I emailed the file to her. We updated all of the mapping locations. She gets this same error message. I am using

clipboard_image_0.png

 to specify the sheet. Why does it work for me and not her? We both have the latest available versions.

asteryx
8 - Asteroid

Quick thought: Are you both writing to the same Excel file? Maybe it's locked for editing by you while she's trying to write to it.

alexlyle
8 - Asteroid

No. The file I write to is on my desktop. The file she writes to is on hers

raedjamil
5 - Atom

Hello everyone,

 

I am now automating a process and have run into the issue of writing multiple tabs into the same excel sheet. I tried using the Block Until Done tool however it was of no use in my case (or I have placed it in the incorrect place). As you can see in the picture below, my workflow separates into 3 different flows at some point; each of these will have a unique output that I will need to write into a different excel sheet.

Please note the following:

- The excel sheet does not exist in the first place and I was to create it in the first flow and then write to it in the following 2 flows (In case this is not feasible then I can overwrite and existing excel sheet)

- The Block Until Done tool does not seem to help in running a sequence for each of the paths (run the second after the 1st if fully done, and the 3rd after the second is fully done), which is leading to the error below.

- Error: Unable to open file for write: C:\Users\rmjamil001\Desktop\12.xlsx Error Opening file
: The process cannot access the file because it is being used by another process.

 

Thanks alot for your help!

 

raedjamil_0-1585834572541.png

 

 

GaryMolloy
6 - Meteoroid

I just  used the "Block until Done" tile and the workflow then output each tab as expected. A potential problem remained in that the design had a few branches and then could have presented a problem.

 

This is not really good enough and a more elegant solution is required.

RyanDonovanNG
6 - Meteoroid

The block Until Done doesn't always work because I save my files on a Shared Drive where I am guessing there is some lag when files saved. There fore I have to do the following trickery.  Alteryx should implement a block until done with "Time lag" feature that we can choose seconds to wait.  The left workflow does work fine if I save files to my laptop but not a Server location.Capture.JPG

AlexSTeryx
8 - Asteroid

Hi @JJH , @raedjamil and all the others!

I like the tool / macro introduced by @Ozzie indeed! Bookmarked it for future use. 

For some of the cases described above maybe the macro, named "Parallel Block Until Done",  our Alteryx consultant @DavidSta gave to us could be of help. 

AlexSTeryx_0-1647267418072.png

 

Basically it works like this (at least as far as I understood): the upper string goes though the "1" anchors and writes into the excel table, only when this is done the workflow will continue working on the string going through the "2" anchors. In case there are more than two strings you've to build a cascade with the macros.

(PS: picture above is no real workflow, just a bunch of tools to illustrate the set up.)

Unfortunately, I cannot upload the macro in this dialogue, but maybe this link  -> Crew-Macro-Pack will finally lead to it.

Best

Alex