Alteryx Designer

Definitive answers from Designer experts.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Output different files to multiple tabs in an Excel file

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
7 - Meteor

Its not giving output in multiple excel tabs. 

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

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.

Alteryx
Alteryx

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

 

 

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.

12 - Quasar

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

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?

7 - Meteor

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.  

5 - Atom

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

5 - Atom

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.

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