Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Multiple Excel Files to One Excel File with Multiple Worksheets

Alteryx
Alteryx
Created

Multiple files can easily be combined into a single Excel file containing multiple worksheets (or tabs).  The format of the files do not need to be the same; they can be completely different. 

 

In the example attached to this article, two files, a Customer .csv file and a Store .xlsx file, are output to a single Excel spreadsheet (.xlsx format) with one tab for Customer and another for Store. These files contain different data elements and have different layouts. 

 

The first step is to create a new field in each dataset (e.g.: 'TabName') and set the expression to the name you want each worksheet to have. In the attached example, 'TabName' is set to 'Customer' and 'Store,' respectively for each dataset. 

 

When you get ready to output your data, use an output tool for each dataset and configure them in the same way.  The file format is 'Microsoft Excel (*.xlsx)' and when prompted to select a worksheet for the output, select 'Sheet1'. Check the 'Take File/Table Name' checkbox located at the bottom of the configuration window. Select 'Change File/Table Name' in the dropdown and under 'Field Containing Name or Part of File Name' enter 'TabName'.  You probably don't want TabName in your final output, so uncheck the 'Keep Field in Output' checkbox. 

 

Attachments
Comments
5 - Atom

Do you know if there is a way to order the tabs in the output file?  I've tried adding .01, .02.... before my tabs names in the formula, but it still seems to be placing them in the order the operations are being run.  I also have a block until done before each output tab, if that effects it.

8 - Asteroid

Hello,

 

I am trying to do this but my output node is showing different options to the example. i don't have the "create new sheet" option. How can i do this?

 

Usamah22_0-1582887456566.png

 

5 - Atom

@Usamah22  - I think the issue is that you have file format set to .csv (which cannot have multiple sheets)

Change it to .xlsx to resolve this