Output multiple excel files with multiple sheets based on 2 different fields

[fvilelabr]

I recently had a session in our Virtual Solution Center when a user asked me about the possibility to simultaneously output multiple excel files with multiple sheets, however, with a peculiarity: he needed to output different files based on column X, and each file needs different sheets based on column Y. 

For example:

Code: Column used to create the output files: Output_1.xlsx ; Output_2.xlsx

Separator: Column used to create the sheets: Output_1.xlsx with 3 sheets (A, B, C) ; Output_2.xlsx with 2 sheets (A, B)

The Output Data tool is used to write the results of a workflow and can be used to write distinct results for each value in a selected field. So, it's possible to create different files based on field or multiple sheets in the same file based on field.

Two additional resources about this topic:

Output to separate Excel files 

Output to Multiple Excel Sheets in Alteryx 

In order to have two simultaneous actions we must use a more advanced technique: the Batch Macro.

  The focus of this article will not be about building macros. But don't worry, look at this article or these Interactive Lessons in order to know everything you need know about them 😉

We are going to create a batch macro that will create a file with different sheets, using the field [Code] as the Control Parameter to create multiple files, at the same time.

Step 1 - Add a Macro Input tool: 

Step 2 - Add and configure the Output Data tool:  We must to set up this tool in order to create multiple sheets based on Separator field.

Step 3 - Add and configure configure the Control Parameter tool and Action tool:

Setting up the Action tool:

1- Select Update Value with Formula:

2 - Create a formula to update the file name based on the field of the Control Parameter:

Step 4 - Save it!

Now we have our batch macro configured to receive the input data and to use the [Code] field to create multiple files with different sheets at the same time.

Using the Macro

First, drag and drop a Summarize tool in order to eliminate duplicate [Code] values. Next, set up the [Code] field in the first anchor of the macro: Control Parameter. Finally, set up the [Code] field in the second anchor of the macro: Group By and Input Group By

1 - Drag and drop a Summarize tool:

2 - Set up the [Code] field in the first anchor of the macro: Control Parameter:

3 - Set up the [Code] field in the second anchor of the macro: Group By and Input Group By:

Et voilá! Run your workflow and take a look at the results 😃

Note: Attached is a sample workflow for demonstration purposes, and not for use in a production environment.

Banner image credit fotokrug.

Output different files and schemas.yxzp