Multiple Alteryx workflow outputs to one excel file on different sheets

[Barclaysusercds]

Hello,

I have an Alteryx workflow with outputs at different routes (highlighted in yellow). Currently each output has a different file name. Is there a way to make all outputs output to the same excel file on different sheets? When I chose an existing file path for a new output it asks if I want to replace that file. I didnt find anything on this page https://help.alteryx.com/20221/designer/output-data-tool.

Thanks

[binuacs]

@Barclaysusercds If you want all the outputs in one excel and different tabs you need to have the same excel file and different sheet names in the output tool. Also you should use the block until tool before writing to each output

[IraWatt]

Hey @Barclaysusercds,

Try the example workflow I've attached It allows you to write to the same file with different sheets:

[Barclaysusercds]

@IraWatt

I used the same file path for all the workbooks.

Changed 3. Output options to "create new sheet"

Ticked the "Take file/table name from field" 

In some instances I had to rename the "append suffix to file/table name" because it already existed.

Ran workflow

I got errors that the sheet already exists

[IraWatt]

@Barclaysusercds You can try using this option:

However, looking at your workflow if all your sheets have different schemas I think @binuacs's solution works better

[binuacs]

@Barclaysusercds Attaching a sample workflow which uses the Block Until Done Tool

[Barclaysusercds]

@binuacs Thanks but if you see my original post and screen shot the workflow has different steps before we get to the output. Where would I put the block until done tool?

[ddiesel]

Hi @Barclaysusercds!

There are several different methods to accomplish this, but here is my preference when the format or schema of each output sheet is different.

1) Create a Sheet Name with the Formula tool on each stream of data. 

2) Format each sheet with the Table tool. With this tool you can control the formatting  and choose the columns for each sheet individually. Group by Sheet Name to bring the sheet name to a separate column. Uncheck Sheet Name in the Per Column Configuration if you do not want the sheet name to appear in the final output.

3) Union all records together

4) Use Vertical Layout with Section Breaks. For Section Name, use Sheet Name.

5) Render with a static file name, or this is where you can incorporate a dynamic file name.

Please try the attached workflow and let us know if it works for you.

Thanks,
Deb

[Barclaysusercds]

Hi Debs

Take the below image, I want to out L, J, R into one output file on different tab. At the moment I can only link L to the output. I want to link two blue lines to that same output on different tabs. I'd rather not create tables. Is this possible?

[ddiesel · Accepted solution]

@Barclaysusercds 

Gotcha! Thanks for drawing it 😊

Let's try this:

1) After the join, create a FileName that includes the SheetName for each stream.  Three pipes "|||" go between the file name and sheet name.

2) Union all three streams back together

3) Output with the "Change entire file path" option

Let us know if this works.

Thanks,

Deb