Guide to Outputting to Multiple Excel files using Alteryx

I’ve recently taken up answering Alteryx community questions and this topic has come up a few times. Is it possible to output my data to multiple excel files and different tabs within those excels files? There are a few possible ways to achieve this in Alteryx in this blog I will demonstrate what they are.

Data Input

I have mock data of sales by product for 3 locations, which I want to output into different excel files by Location and Product per tab. The reason for this is that the end stakeholder will have a file per location and the different products on individual tabs.

Data: Sales by Location per Product

Manual Approach

I could build different streams in my workflow filtering by location and product and then use an Output tool to write to different excels. However this approach is quite manually and not dynamic, imagine if my data is a monthly report and the data changes this approach would not be able to easily cope and would require me to change the workflow per month.

Filtering per Location per Product

Reporting Tools Approach

Thankfully in Alteryx there is a more dynamic solution the first way I will show you utilises the Reporting tools. What I like with this approach it only take a few tools to achieve the desired results and this approach is dynamic. If the number of locations or products increase or decrease this approach would generate the appropriate number of excel files and tab sheets within those excels.

Reporting Tools Approach

In the table tool the key thing is to group by your fields you want to create separate files and sheets for in my example I group by Product and Location.

Table Tool: Configuration

The next tool I utilise is the layout tool for the Layout Mode it’s important you select “Each Group of Records” for the Layout Mode, then change the Orientation to Vertical with Sections Breaks. Changing the Section Name is optional however I used the Product field in my example as I want the separate tabs to be named as the actual product.

Layout Tool: Configuration

The last tool in this approach is the Render tool where I choose a Specific Output file and tick on “Group Data in Separate Reports” and selecting the Location field. In the Report Data section I change the “Date Field “to layout. Based upon my configuration on the layout tool it will produce a layout per product for each location.

Render Tool: Configuration

The output of this approach is three excel files are created with a tab for each product. A benefit of this approach is you can format the table.

Results: Location1.xlsx

Output Data Tool Approach

The second approach is using the Output Data Tool functionality to “Take File/Table Name from Field”.  This approach you only need two tools a formula tool to create a file path with your fields as inputs. Like the Report Tool approach this solution is dynamic and would produce the appropriate number of files, with the correct number of sheets based upon your data.

Output Tool: Overview

As stated in the formula tool I create a filepath field for the Output Tool and use the field Location as a file name then add the file extension. The Product field is used to state the sheet for the excel file (“E:\Trinity\Multiple_Excels/”+[Location]+”.xlsx|”+[Product]). 

Formula Tool: Filepath

In the output tool you want to ensure Output Options (3) Overwrite Sheet (Drop) is selected. Then at the bottom section of the configuration window of the output tool you want to tick on “Take File/ Table Name from Field” then from the drop down select “Change Entire File Path”. The last step is choose the field for the “Field Containing File Name or Part of File Name” section. You also have the option of keeping the field in the output with the “Keep Field in Output” checkbox.

Output Data: Configuration

The results of this approach is similar to Reporting Tools approach in producing separate excel files with different tabs. The only major difference with this approach you are not able to format the table before it’s outputted to excel.

Results Location1.xlsx (Output Data Tool) approach

Batch Macro

The last approach is a batch macro which is personally is my least favourite approach as it requires more steps whereas the other approach utilise a handful of tools.

Batch Macro Overview

This approach is similar to both approaches where essentially you want to separate your data, by location and product. At the beginning of this blog I showed you could build a manual process a batch macro in this situation is essentially that manual process however with a looping process making this a solution dynamic.

Inside Batch Macro

The Batch Macro utilises a filter which is updated by the two Control Parameters changing the values in the filter and then updates the formula within the formula tool, which is creating a file path for the Output Data Tool. The configuration in the Output Data tool is changing the entire file path to our selected field e.g. Filepath field created in the formula tool. 

As you can see this approach is similar to the second approach and is using the functionality of Output Data Tool to change the file path. This approach involves more steps and I think conceptually is more difficult to explain to a user when compared to the two other options. 

In summary there are many ways of outputting your data in Alteryx into multiple files. I typically only show the first two methods when I am teaching a user how to answer this question.

Hope you find this blog helpful and hopefully I highlighted the different ways you can output to multiple excel files. Please leave a comment below or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

One thought on “Guide to Outputting to Multiple Excel files using Alteryx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s