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 Knowledge Base

Definitive answers from Designer Desktop experts.

Output different files to multiple tabs in an Excel file

Alteryx_KB
Alteryx
Alteryx
Created

If you have two or more files, different structure, and you would like to output each file into a separate tabs in an Excel spreadsheet. You could use the table tool to create snippets and the Layout tool to create sections breaks.

workflow.jpg

Bring in your files using the Input tool and connect them to Table tools to create the snippets.  Finally, Join them by record position.

The Layout Tool properties should look as follows.  Select Vertical with Section Breaks for the Orientation setting.




The output will show each file in a separate tab:



Picture 1.jpg

Picture 2.jpg





Attachments
Comments
VJ_dup_337
7 - Meteor

Its not giving output in multiple excel tabs. 

Aure
5 - Atom

Hi,

 

Thank you very much, it helped me a lot!

Now, in terms of customization, do you have a quick and nice way to change the tab names?

 

Thanks

DultonM
11 - Bolide

Hi @Aure,

 

I'm not sure if there is a way to control the sheet name when outputting to an Excel file with the Reporting tools. However, if you aren't concerned with the format of the Excel file and just want the data written to Excel on specifically named worksheets, I would recommend the method described on this Community page.

MargaritaW
Alteryx Alumni (Retired)

Hi @Aure,

 

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.

 

pic1.png

 

 

pic2.png

 

 

JMoore
8 - Asteroid

In order to have a date added to your export file and name the tabs, select both in the Table as Group by but remove from the Per Column Configuration then I used Multi-join by record position, in the Layout you can follow above where Layout Mode is Each Individual Record, and then in the per section configuration check box the Section Name: Use Field and do so for each table being input based on its record ID associated with it to name your tabs. Then in the Render command select the check box Group Data Into Separate Reports and select the date field you needed.

 

This worked great to name both the tabs and the file with a date stamp.

ddiesel
13 - Pulsar
13 - Pulsar

Thanks @Alteryx_KB and @MargaritaW! Between your two posts, I was able to solve for my use case. Thank you!

Jasmine
5 - Atom

Hi,

 

Thanks for the above thread.

I have tried the approach mentioned by @Alteryx_KB and @MargaritaW , but the output is overwritten while rendering in separate tabs.

 

Requirement:

The data has to be rendered in the same worksheet with each Category as Tab names.

 

Issue:

The output file overwrites the Category name during each iteration of the macro, and retains only the last Category after the complete run.

 

Scenario:
My data has different schema based on multiple Categories. I have used a macro to aggregate the data which is grouped by each Category, and then renders the output inside the macro. Using the settings mentioned by @Alteryx_KB  seems to have worked, but the sheets are overwritten during each iteration of the macro, and only the last Category is retained in the file after the entire run.

 

Any ideas on how to resolve this particular issue?

Paulo1300
8 - Asteroid

the biggest issue i have is that i cant figure out how to make each output table be a different width.  in the example screenshot you posted above, you have the same issue. sheet 1 is stretched out unnecessarily and sheet 2 is cluttered. anyone know how to set their widths individually?

 

to me it makes no sense that alteryx built the render tool with the assumption you are always going to print it on a page. we are in a digital world, who prints out excel sheets these days?

 

all i want to do is have formatted tables and alteryx has the functionality via the render tool, yet its built specifically for print out reports and forces you into specific page sizes and then stretches to fit that page size.  

MJohnson3
5 - Atom

@Paulo1300 Did you find a solution to this?  I am having the same issue!  Thanks. 

GaryMolloy
6 - Meteoroid

I just  used the "Block until Done" tile and the workflow then output each tab as expected. A potential problem remained in that the design had a few branches and then could have presented a problem.

 

This is not really good enough and a more elegant solution is required.

RachelH
5 - Atom

@Paulo1300 @MJohnson3

 

Did either of you ever find a solution to this issue? I'm facing the same problem and can't seem to find a solution anywhere.

 

Thanks

kureka
6 - Meteoroid

@Paulo1300 @RachelH 

 

Hi, the same functionality issue, were you able to find a workaround for a different width per XL sheet (layout per each group)?

 

Thanks

RachelH
5 - Atom

@kureka 

 

I ended up adding blank columns to the smaller tabs. Alteryx doesn't realise they are blank and will squish the actual columns to make space for the blank columns. You do need to add quite a few though.

Good luck!

gagandeep_dhall
8 - Asteroid

Hi,

 

This is not giving me the result in separate tabs.

But its giving me one tab with different columns from the 2 sheets I am combining.

It has prefixed my second file's columns with "Right_"

 

Regards,

Gagandeep

kamilksiazek
5 - Atom

Hello,

 

I tried it but with Join multiple, as I have 10 files that I want to output into 10 tabs. But it gave me an empty file with some one line of code...

PSolomonCote
6 - Meteoroid

Hi,

 

Am I right to think that this trick only works with the original Alteryx Engine and not with AMP ?

I tried every possible configurations with no success until I unchecked the  "Use AMP Engine" in the runtime tab and then it worked.

 

Thanks

Phil

ClaraLin
7 - Meteor

How can I specify the file name and output path of the outputs when I use Render tool?

lepome
Alteryx Alumni (Retired)

@ClaraLin 
That is set at the top of the Render Configuration:

Set path and file nameSet path and file name

ClaraLin
7 - Meteor

How do I specify Sheet? I keep getting this error message 

ClaraLin_0-1673981417608.png

 

lepome
Alteryx Alumni (Retired)

@ClaraLin 
Sheet is configured in the Layout tool per Margarita's comment above.  If you have a large data set, it's possible that you need to expand the page size in the Render tool per this article, depending on what version you are running.

If you have a current, paid license, you can open a case with Support to get help troubleshooting errors.