Adapt which columns are written into one Excel Worksheet

kim09

Hello everyone!

I am trying to find a solution for the following problem:

I want to write my created table in Alteryx into one Excel file. My idea is, that the file contains different worksheets. Each worksheet should show the columns Check, Position, and one number-column (1, 2, 3, ...).

Meaning the worksheets of my Excel file should contain the following columns (see photo attached of the table structure in Alteryx):

Worksheet 1: Check, Position, 1

Worksheet 2: Check, Position, 2

Worksheet 3: Check, Position, 3

...

I want to have an automated solution, so using the select tool and creating devisions in the workflow for each worksheet is unfortunately no option to me (because I have a lot of number columns). 

Does anyone have an idea how I can achieve this output if my table in Alteryx is structured according to the photo?

I really appreciate any help that you can offer, thank you very much!

Best regards,

Kim

image.png

Pilsner

Hello @kim09,

This is a great question. I've actually recently been looking at different ways to output dynamically to Excel. There are many ways to tackle this problem but I've tried to outline my preferred method below:

1) I started with some dummy data, which I tried to base on your screenshot.

2) Once I had the data, I wanted a dynamic way to list all the numbered columns that I want on the separate worksheets. To do this, I used a field Info tool to list ALL columns, then a filter, to remove those that I wanted to appear on every page.

3) Then I fed these column headers into the control parameter of my batch macro:

4) Inside the macro you will find a text input tool with 3 column headers. They are "Check", "Position" and "1". These are then unioned onto the full data with the output common subset of fields only option selected. 

5) To make this dynamically change the output columns and sheets, we just need to point the control parameter in the right place. 

a) The first place to update is the column header in the text input. We want the column header 1 to be overwritten each batch, so use the action tool to select this:

b) We also need the output sheet name to be overwritten, use another action tool, connected to the output tool, to overwrite just the sheet name from the full path.

6) The final check, is to make sure that the output tool is set to "Overwrite sheet or Range" as without this the macro will error after the first run (due to the file already existing).

Here are some screenshots from two of the worksheets output:

I have attached the workflow below to try and assist further but please feel free to as any other questions.

Regards - Pilsner

Workflow.yxzp