Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Output multiple files using the same excel template

JordanB
Alteryx
Alteryx
Created

You may have a use case whereby you have a large dataset and you want to output it to separate excel files. However, in each of these excel files you would like to apply a template format.

 

For example, I have a dataset which has three unique IDs each with 10 records. I want to output each of these Unique ID records to different excel files, but give the user the ability to filter to fields directly after the data has been written. Within Alteryx there is native way of adding filters into Excel. One way to achieve this is by creating a template excel file with filters already applied to the first row in Excel. Within Alteryx we can then specify a range of cells to add data too, and subsequently this will auto populate the filters and give the user the option to filter within Excel straight away. 

 

 

Pic 1.png

Example of template file

 

 

Before we get started there are a few prerequisites:

 

  • This example uses .xlsx files
  • A template file must already exist in order to find the data range you want the data inserted into.
  • The excel sheets you wish to write the data too must already exist with the template format inserted.

 

Considerations

 

  • Depending upon the number of variables you have in your chosen unique field it may be difficult and pain staking to manually create the excel files with inserted templates. Therefore, there are two potentials options you can take. 
  • Option 1: Smaller number of variables: Manual create the different excel files for each variable and add in the template.
  • Option 2: Larger Number of variables: Create a .bat or .exe script that creates the excel files specific to each of the variables you have in your dataset and inserts the template file. Once you have created the .bat script you can run this by using the Run Command Tool in Alteryx.
  • Once the excel files have been created with the template inserted we are ready to move to Alteryx.

 

What format does your data need to be in?

 

Your main dataset needs to match up to the template file you have created in Excel. This means you need to have the same number of fields in your dataset, as you do in your template file. This becomes particularly important when we talk about named ranges later in this article. 

 

Once the data has the right number of fields we need to generate the full path field so we can write out to the correct Excel template file.  

 

The easiest way to do this is build the full path yourself using a formula tool. This article describes how to do this and instead of using the [State] field you would use the unique field you are looking to create new excel files for. 

 

Once you have created the full paths you may be wondering how Alteryx knows exactly where to put the data in the excel files. It’s simple, all you need to do is append the sheet name and named range to the end of the full path you have just created.

 

Formula tool expression: ‘C:\Users\JonSnow\Documents\IDExcels\[ID FIELD].xlsx’ + ’|Sheet1$A1:J5’

 

Result: C:\Users\JonSnow\Documents\IDExcels\1001.xlsx|Sheet1$A1:J5

 

This article shows how to do this for importing excel named ranges, but outputting names ranges uses the same principles and steps but in the Output Data Tool

 

Because you have a template file, the named range should not change. Yet, if it does you can manually change the named range within your full path field.   

 

 

 

Congrats you're half way there!

 

You now have a full path and named range associated with each record in your main dataset. We now need to take that full path and output it. Before doing this we first need to find the unique full paths to feed into a Batch Macro.

 

Simple steps first

 

First let’s go ahead and add a unique tool after the formula tool in the workflow. You can now tick the full path field so it only leaves us with the unique full paths.

 

Let’s build that batch macro!

 

Although intimidating at first glance the Batch Macro can become your best friend very quickly. To build a Batch Macro for this purpose you will need to copy a subset of your main dataset. To do this you can add a sample tool (First 5 records) to your dataset and a browse tool. When you run the workflow and click on the browse tool, this will allow you to copy the fields with headers included.

 

Pic 3.png

 

 

Open up a new workflow

 

Once you have a new workflow open right click on the canvas and hit paste. This will paste the records into a text input tool. You can then right click on the text input tool and choose ‘convert to macro input’. This will allow us to feed our main dataset into the macro.

 

Pic 4.png

 

Add that Output tool

 

You can now attach an output tool to your macro input. When configuring the output tool, choose a placeholder file as the ‘Write to file or Database’ (This is just one of your newly generated excel files).

 

In the options section below your output file string, select in the drop down for ‘Output Options’ ‘Overwrite File (Remove)’. You may be thinking this is going to remove your template. Do not fear it is only going to overwrite anything within your specified named range in your full path.

 

 

Here’s the Key to the whole operation!

 

At the bottom of the output tool tick the box for ‘Take field/table name from Field’, and then in the drop down option choose ‘Change Entire File Path’. 

 

**** If you'd like to overwrite a cell range, please select "Overwrite Drop," if you're looking at replacing multiple file templates, please select "Overwrite File."

 

Pic 11.png

 

Outside of a batch macro this would mean a file would be generated for every unique variable we had in the chosen ‘Field containing file name or part of file name’. Unfortunately, in this example we cannot do that because it would output a file for every line of data we had (As each line of data has a full path).

 

To get around this we need to update the output connection string in the output tool with the unique full paths using a control parameter. This will allow the batch macro to batch based on the unique full path and pull all the data at once with the same file path in the main dataset.   

 

Unique File Paths

Pic 12.png

 

Associated file paths per record (row)

Pic 13.png

 

Once you have brough the Control Paramter onto the canvas. Drag from the Q to the lightning bolt on top of the output tool. The configuration we want to choose int he Action tool is 'Update Connection String' and click on the full path which will insert it into the bottom text box in the configuration window (see below). 

 

You can now save this Batch Macro. 

 

 

Pic 14.png

Completed Batch Macro Overview

 

Let’s go back to your main dataset workflow.

 

At the end of the workflow lets go and insert in the Batch macro (Right click on the canvas>>>Macro>>>select the Batch macro you just built).

 

You will see the Batch macro has two inputs. The question mark in the control parameter and the normal node is the data input. In the control parameter node you need to feed in your unique full paths and in the other anchor you need to connect the original dataset.

 

In the macro interface on the tab ‘Group me’ you will need to select the ‘Control GroupBy Field’ as the full path field from your unique tool and the ‘Input12 GroupBy Field’ will need to be the full path field in your main dataset. This will subsequently group these unique fields when feeding in the data and output them as groups rather than individual records.

 

The Questions tab is referring to what you will be feeding into the control parameter. This will be the unique full path field.

 

Pic 10.png

 

 

You’ve made it!

 

You have now successfully built and configured the batch macro to write out to separate excel files in a defined range. In this particular example, the filtered headers auto populated a soon as the data from Alteryx had been written successfully to the excel files.

 

Please find attached an example workflow (Built in Alteryx 10.5)

 

If you have any questions regarding this process please reach out to Support

 

Best,

 

Jordan Barker

Solutions Consultant, Alteryx Inc.

Attachments
Comments
ll1100000
8 - Asteroid

sorry, it is very confused.... don't understand...

JordanB
Alteryx
Alteryx

Hi @ll1100000

 

I would strongly recommend looking to do this for one template sheet before looking at multiple.

 

The workflow above requires a batch macro which adds advanced concepts. Once you have successfully done this for one template file, I would look into batch macros in more detail and the workflow above you make more sense. 

 

Best,

 

Jordan

ll1100000
8 - Asteroid

Hi Jordan;

Thank you very much for your reply.

My output data includes the following columns;

(Department#,  category, Stores, Sales)

Instead of exporting 1 excel file, I want separate the files by Department# and export them at once.

So whenever the department# changes, create a new file, and name the file by its Department number.

 

For now, I am using the "filter" function to filter each individual department and then export it... but since I have more than 50 departments, that is lots of work...

You article seems provided the solution, but I totally lost as I am still very new to Alteryx.  I will check the One template sheet today. 

Thank you!

JordanB
Alteryx
Alteryx

Hi @ll1100000

 

This is the article you want :)

 

In the article it uses the field 'state' in the formula tool, you will want to use your 'department' field.

 

As you are new to Alteryx feel free to download the Alteryx module at the bottom of the article and look at how the formula and output tool are set up. 

 

Add both of these tools at the end of your workflow.

 

Formula Tool Config:

 

Pic1.png

 

Output tool config:

 

pic2.png

 

Best,

 

Jordan 

udhawan
5 - Atom

Hi Jordan,

             I tried this and my batch macro works and create multiple files in the output folder but these files in the output folder are not based on my selected template. Output files just has one sheet created with the workflow and dropping all the format and other sheets from my template sheet.

I have 5 sheets in my template and I want to update one sheet and save the template with a new name based on path created within the workflow.

I have created named range on the template sheet I want to write.

I will appreciate help on this to produce multiple output files based on selected template.

Thank you..

 

falcozin
5 - Atom

I'm having the same problem as @udhawan. Tried changing some things in the macro, but always got 2 results. The first one was as @udhawan pointed. The other output I got was everything pasted in the template independent of the full path field.

Ps.: My template has 6 sheets with several formulas linked to another sheet called "Template". All of this is saved in the same excel file, called "Report 1".

This "Template" sheet is what I want alteryx to "replace" and save several times according to the values of 1 column.

If someone can help, I would be very grateful!

marlline
8 - Asteroid

Hey @JordanB,

 

I ran into 2 problems as template screenshot shown below.

 

1. Numerics outputted into text

8335, 9090 and 7830 should be numbers but they are NOT in the template.

 

2. Blanks

For item B, I have no data. But blanks outputted as F1, F2 and F3. How can I prevent that?

 

Screenshot of Template.PNG

 

Thank you very much!!

JordanB
Alteryx
Alteryx

Hi @marlline

 

1. Numerics outputted into text

8335, 9090 and 7830 should be numbers but they are NOT in the template.

 

I have tested this myself and I cannot replicate. In the template I defined the rows and columns I want as a number within Excel. I then made sure these same fields were a number type in Alteryx before writing out.

 

2. Blanks

For item B, I have no data. But blanks outputted as F1, F2 and F3. How can I prevent that?

 

Alteryx will write out the field headers defined within Alteryx to the Excel sheet. To avoid the F2... you will need to define the field headers names you want in excel within Alteryx.

 

Best,

 

Jordan Barker

marlline
8 - Asteroid

Thank you so much!

Have a great day!

Ralf
5 - Atom

Hi Jordan,

many thanks for your very helpful description.

I was able to develop my workstream so far, but now I am facing the same problem like udhawan and falcozin posted above.

 

Is there any solution available to keep the other tabs of the sample template and only update the one specified.

 

Your help is highly appreciated.

 

Regards

 

Ralf

JordanB
Alteryx
Alteryx

Hi @Ralf

 

You will need to specify the sheet and the template defined in Excel within a formula tool at the end of your workflow similar to the above explanation. 

 

I have sent a simpler sample workflow showing this logic to your email.

 

Best,

 

Jordan

 

 

RSK
7 - Meteor

Hi Jordan,

I'm getting stuck in the same situation as the previous individuals as my template is not being utilized. I get the five sheets, but not in my template. What step am I missing? I went through your example and updated all the paths I could readily see.

Thoughts?

Thx

Rob

Garrett
11 - Bolide

@JordanB perhaps I'm missing something, but it seems to me that the title of this thread ("Output multiple files using the same excel template") is a bit misleading.

 

Isn't it a requirement that a separate excel template file (XLSX, XLSM, XLS, whatever) already exists BEFORE you write into a named range within it?  Isn't that the issue you describe in "Prerequisites" and "Considerations"?

 

  • A template file must already exist in order to find the data range you want the data inserted into.
  • The excel sheets you wish to write the data too must already exist with the template format inserted.

 

Considerations

 

  • Depending upon the number of variables you have in your chosen unique field it may be difficult and pain staking to manually create the excel files with inserted templates. Therefore, there are two potentials options you can take. 
  • Option 1: Smaller number of variables: Manual create the different excel files for each variable and add in the template.
  • Option 2: Larger Number of variables: Create a .bat or .exe script that creates the excel files specific to each of the variables you have in your dataset and inserts the template file. Once you have created the .bat script you can run this by using the Run Command Tool in Alteryx.
  • Once the excel files have been created with the template inserted we are ready to move to Alteryx.

So wouldn't this thread be better named "Output into multiple files"?  And if that's the case why the Batch Macro? Using the "Take File/Table Name from Field" configuration in the Output Data tool has the effect of grouping the data before it outputs anything.  So if the "Full Path" is the same for multiple rows of data (as is the case in your example) all of these rows are written to the same file.

 

And finally, I'm running into the same issue as stated above that this technique does not actually append into an existing template, but instead overwrites the template with raw data. Am I missing a step as suggested above?

 

The real crux of the use case I'm trying to solve is to write multiple files using the same template.  Sounds like I'd have to use something external to Alteryx to create all the templates first?  Then I could write into them (assuming I could get the write into a template part working too).  Sound accurate?

shawan66
5 - Atom

I am having some of the same issues as everyone on here.  I cant figure out how to keep the formatting of the "template" to copy over to the new files.  It just seems like it creates a new file per County.  And you can do that without creating a Macro.  So does anyone know how to create multiple Excel files using a "template" that copies over to all the newly created files?  Any insight will greatly be appreciated.

 

@JordanB

 https://community.alteryx.com/t5/user/viewprofilepage/user-id/2690

 

Example:

Cell highlights - did not copy over

Text Formatting (Bold, underline) - did not copy over

Other Tabs (Sheet2) - did not show up in newly created file

Etc..

 

TEMPLATE I CREATED AS AN EXAMPLE:

 

Untitled.jpg

 

OUTPUT After the Macro was run:
Untitled2.jpg

 

You can see that the yellow highlight, the bold text or the borders did not show up in the output.  How do you get the output to match the template?

 

Thank you in advance for any advice or input.

RodLight
8 - Asteroid

As @Garrett mentioned above, I believe people are getting confused with the term "template". 

 

@shawan66, if you are wanting to apply formatting to an Excel output, you will not be able to do it with the Output tool. What you would do is use the Reporting tools to output a Table to a Render tool (with Excel as the output option). All of the highlighting, underlining, etc. would need to be specified in the Table tool. And I will have to say that when I've done this, it can sometimes be difficult if you are needing to get the columns to particular sizes. 

 

One other option I've seen clients use is to create a "template" tab in an Excel spreadsheet (e.g., a formatted pivot table) that points to a "raw data" tab in the same spreadsheet. If you do that, you can use the Output tool to refresh the "raw data" tab, which will in turn automatically refresh your "template" (and keep the formatting  you are looking for). This works best when the "template" is an aggregation of raw data...if you are trying to apply formatting to added rows, it typically won't work unless you fill the entire "template" tabs with formulas pointing to possible rows in the "raw" tab.

Usamah22
8 - Asteroid

Hello,

 

i have a dataset that i want to split into separate files when output. for example as below;

 

Product CodeProduct NamePrice 1Price 2Price 3
adsfc123
bsdf456
caswq789

 

is the best way to have the 3 select and output functions to produce a file for each price level?

 

Product CodeProduct NamePrice 1
adsfc1
bsdf4
caswq7
Jhar2401
6 - Meteoroid

I think this is what I’m trying to do but are you saying I need to create all 152 separate output files before I can export to them? 
surely this defeats the point?

aifumi
7 - Meteor

hello!

 

I have tried to find how to create multiple output excel files from a file by using macro for a few days in japan.

I was not able to  find the solution in Japanese.

Thank you for your clear information!  I did !

Frankly speaking, I can not understand still the difference between using 'template' and just splitting a file to the multiple files.

But I will  understand it as Iong as I keep learning .

 

   

 

mihaicom20
8 - Asteroid

@JordanB  is there any way to generate multiple sheets with same template  on the same file?

 

I have an open topic about this:  https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Create-multiple-sheets-on-the-same-exc...