Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Output multiple workflows into one excel sheet.

mszpot89
9 - Comet

Is there a way to organize one output excel sheet which will store few tables?

Each table is a separate workflow's result. Other thing is that sometimes the row number changes in some workflows (per month).

That would need some formula to insert first table, insert space and next set.

 

 

 

image.png

10 REPLIES 10
john_miller9
11 - Bolide

@mszpot89  Two steps I can think of to accomplish this:

1. You can output to defined range  in an excel workbook --> see video from @MarqueeCrew in this thread:

https://community.alteryx.com/t5/Alteryx-Connect-Gallery/Export-an-range-to-Excel/m-p/92094#M3206

 

2. Make sure to use the Block Until Done tool to allow for multiple writes to the same file.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Block-Until-Done/ta-p/33417

 

This should get you where you need to be

 

Multiple Writes to Excel.PNG

mszpot89
9 - Comet

What if one workflow will return more rows then previous month? That will shift rows and take extra space form the next one.

 

I'm thinking about a pattern that would append workflow 1 results to sheet 1 and then omit 2 rows and paste workflow's 2 results etc

danrh
13 - Pulsar

You might look into the reporting tools.  I used Table, Union, and Render to get the kind of thing you're looking for.

image.png

mszpot89
9 - Comet

I forgot to mention that my report is made out of 20 workflow results and these are split into 4 sheets.

I tried the |||Sheet1 syntax n Reporting/Render but it's not working.

danrh
13 - Pulsar

How do you determine which workflows go on which sheets?  If you have a field in your data sets that references which sheet it ends up on, group by that field in your Table tools, then add a Layout tool with "Each Group Of Records" selected as the Layout Mode, grouped by the sheet field, and Orientation being "Vertical with Section Breaks".

mszpot89
9 - Comet

@danrh - the sheets are determined by made-up column "grouping" (added with formula tool).

It seems that I forgot to add the Table module from Reporting section.

 

I'm starting to get closer to my expected results. Will play with it tomorrow and let you know.

 

I need to find a way to insert a header for each table and separate them with space as now returned data is jammed one onto other.

 image.png

danrh
13 - Pulsar

This likely isn't the prettiest way to do this, but hopefully it gets your wheels spinning:

image.png

Good luck!

mszpot89
9 - Comet

@danrh - great thanks! it almost works perfectly :)

 

fyi - to get sheet name also changed in excel edit the Visual tool's Section Name to Sheet

image.png

 

 

last issue is columns' name being truncated, is there a way to work around this?

image.png

danrh
13 - Pulsar

Look into the "Paper Size" in the Report Style of your Render tool's configuration.  The default is 8.5x11, if you expand this it should give more room for your headers:

 

image.png

Labels