Using the Render Tool to Export to Different Sheets in the Same Workbook

As a follow up to last week's tech tip, I was asked how do you export to different worksheets in the same Excel workbook using the Render Tool. Creating a table and using the Render Tool allows you to add a nice format to your data export. You may be aware if you use Alteryx to export to an tab in an Excel workbook using the Output Data Tool, the data is exported in a jumbled format. You may have to open it to expand columns etc.

Please note: I found this solution in this discussion in the Alteryx Community website > Link

You would set up your workflow as follows:

I start by having two datasets with arbitrary information such as below;

Next you add a Table Tool to both data sets in the workflow, with whatever default configuration it enables.

Then you add a Formula Tool after each Table Tool with the name you want your separate worksheets to be. I entered the following formulas;

Formula Tool 1

SheetName

"Dataset 1"

Formula Tool 2

SheetName

"Dataset 2"

Then combine these two data streams together with the Union Tool.

Next attach a Layout Tool next to the Union Tool. Set up this tool as follows;

Layout Mode: Each Group of Records

Check the box for SheetName

Orientation: Vertical with Section Breaks

Data Field: Table

Section Name: Sheet Name

Here is a picture of the configuration for this tool:

(You can always right-click on this tool and select Add Browse After to see what your layout looks like.)

Finally add your Render Tool to your workflow. Configure it as follows;

Output Mode: Choose a Specific Output File

Output File: Navigate to where you want the file to be saved and give it a custom name with an .xlsx extension.

Data Field: Layout

Separator: No Spacing Between Records

Paper Size: Custom Size

Size: - You may need to manually adjust this. Since my data was small I entered 3 X 3.

Margins: No Margins

Here is the image of the Render Tool configuration:

Now when you run the workflow, it will export both data sets to one Excel workbook. Click on this workbook in your results pane to open it.

It should appear as below;