This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 05-07-201604:00 PM - edited on 07-27-202111:41 PM by APIUserOpsDM
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.
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.
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’
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.
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.
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."
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
Associated file paths per record (row)
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.
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.
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.