Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multiple Alteryx workflow outputs to one excel file on different sheets

Barclaysusercds
8 - Asteroid

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

 

Barclaysusercds_0-1653296772141.png

 

9 REPLIES 9
binuacs
20 - Arcturus

@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

 

binuacs_0-1653297340495.png

 

IraWatt
17 - Castor
17 - Castor

Hey @Barclaysusercds,

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

IraWatt_0-1653297534567.png

 

Barclaysusercds
8 - Asteroid

@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

 

Barclaysusercds_0-1653301706355.png

 

IraWatt
17 - Castor
17 - Castor

@Barclaysusercds You can try using this option:

IraWatt_0-1653301892189.png

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

binuacs
20 - Arcturus

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

 

binuacs_0-1653302676661.png

 

Barclaysusercds
8 - Asteroid

@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
13 - Pulsar
13 - Pulsar

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.

 

 

Capture.JPG

 

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

 

Thanks,
Deb

Barclaysusercds
8 - Asteroid

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?

 

Barclaysusercds_0-1653908147661.png

 

ddiesel
13 - Pulsar
13 - Pulsar

@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

Capture.JPG

 

Let us know if this works.


Thanks,

Deb

 

Labels