Output multiple workflows into one excel sheet.

[mszpot89]

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.

[john_miller9]

@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

[mszpot89]

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]

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

[mszpot89]

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]

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]

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

 

[danrh · Accepted solution]

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

Good luck!

[mszpot89]

@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

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

[danrh]

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: