Outputting to different tabs in excel withoug union

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.

Outputting_Different_Tabs_Excel.yxmd

Output

Accepted answers

NJT

The packaged version didn't work for me probably a firewall issue on my end I couldn't get your data still but I edited your workflow and did it in an older version of Alteryx so hopefully you can open this without issue. I couldn't run it to test because I didn't get your data but give it a shot hopefully this works.

Outputting_Different_Tabs_Excel.yxmd

All comments

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-an-Excel-file/ta-p/1245

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

Output different files to multiple Excel tabs.yxzp

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.

Outputting_Different_Tabs_Excel.yxzp

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

Outputting_Different_Tabs_Excel.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703