Outputting to different tabs in excel withoug union

[adnan2234]

Hello everyone.

We are using our sales and purchase data to do some calculations and find out the fees. At the end of flow we need to output the data into excel. We need to create two tabs, one for sales one fro purchases. If we union sales and purchases data and then use the attached flow, then it does the job for us. Problem is that a lot of columns don't match between purchase and sales so they will be blank.

Is there a way to output to two sheets using render tool but without using the union tool. I'm attaching a test flow.

Any help will be greatly appreciated! 

Adnan.

[Thableaus]

Hi @adnan2234 

You could use a Directory Tool + a Batch macro to render each tab at once, preserving the set of columns of each file.

This example here explains how to read multiple sheets with a batch Macro. You just need to adapt it to your case.

Cheers,

[danilang]

Hi @adnan2234 

The Block Until Done tool on the Developer tab almost does what you're looking for but only has one input connector so you'd have to union your records.  Luckily, The Crews Macro pack(Link) contains a tool called Parallel Block Until Done.  It takes 2 inputs which can be completely separate, hence the Parallel in the title.  The tool blocks the second output until the first is completely finished, including pesky things like closing files if you're trying to write to 2 tabs in the same Excel file.  It's what we use when in your situation.

Dan

[darryl5280]

On the “Render” Tool’s Configuration under “Group Data into Separate Reports”

“Modify Filename” change to “Replace Entire Path with Group” and just get a field with the whole path and filename in it with “|||” before the Sheet Name like: [Path] + [Filename] + “|||” + [Sheet Name].

And you could send your Data to two different "Table" tools, and union the Tables, before the "Render" tool.

I hope that gives you some insight?

[NJT]

Fortunately this is a somewhat common question @ddiesel had the same one quite a while back, while the designer has changed a lot since then, this may still be your best solution. https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-different-files-to-multiple-tabs-in-a... 

You'll want to start with the first post from @Alteryx_KB there and then use a second post from @MargaritaW  on this same thread if you want to be able to name your tabs specific names. 

"If you group your data in the Table Tool by the field that holds the name you want to give your tabs in Excel, then this field will be available in the Layout tool for the 'Section Name' which will give the name to the Excel tab."

To try and simplify that I've attached a workflow combining the two solutions. If you Export your test workflow as a package I can update this with your sample data so it lines up with what you're trying to do but hopefully this gives you enough info to solve. 

Check it out and let us know if it solves it for you or if you need more help! 

Thanks,

Neal @NJT 

[adnan2234]

@darryl5280 

First of all, thank you all for the quick reply. I found your solution the simplest so been working on it. The problem is that we have a lot of files in this flow so I concatenated my report number field to filename. Right now I'm getting an error saying "InbounNamePipe GetOverlappedResuly: The pipe has been ended"

Attached is the packaged workflow.

[adnan2234]

@NJT 

Thanks for the reply.

I will work on the solution you have provided and will let you know the outcome. I'm unable to open the attached file since I'm using a different version of Alteryx (2018.4.5.55178).

Attached is the packaged workflow as requested :)

[ChrisTX]

To open the YXZP file, even when you have a different version of Alteryx:

Open the yxzp file using Alteryx and click Yes to the question "The file you selected is an Alteryx Package.  Do you want to import it?"  Then in the next window you'll need specify the Destination Directory and click Import.  This will un-zip the contents of the yxzp zip file to a sub-folder under your Destination Directory.  The folder name will be the same as the first part of the yxzp file name.

In your case, under your Destination Directory, look for a folder named Outputting_Different_Tabs_Excel.  Open the folder and double-click the yxmd file. 

You may get a message about the workflow being created by a more recent version of Alteryx, but you can just click Yes.  As long as newer tools were not used in the workflow, it should run OK on your version.

[adnan2234]

@ChrisTX 

When I try to import the attached file, I get the error and after accepting the error, nothing opens up:

[darryl5280]

With words it's hard to explain, so I created a sample workflow that creates 3 Sheets in Excel with the "Reporting" Tools.  So here is a picture of the Attached Workflow.