If you have used Alteryx to output data onto a template file, no doubt you have run into the problem of outputting data onto multiple template files. What exactly do I mean by this? As an example, say you have five Excel input files that you need to output to five different Excel output files. Normally, you would be stuck with two options:
1. Input the five source files and output all the data onto one Excel file
2. Input the five source files one by one, running Alteryx five times in total
Option #1 does not accomplish the goal to output each input file onto a separate output file. Option #2 is inefficient as you would have to run Alteryx five times in total. With run command functionality in Alteryx, a third option becomes available
3. Input the source files, run Alteryx one time, and output the data onto five separate output files
Command Line Operations
The run command functionality in Alteryx lies in the tool called “Run Command.” Within this tool, you can perform command line operations directly within the Alteryx Designer environment. To access the command prompt in Windows, type “cmd” in your search bar.
One such command line operation is the ability to copy a file from one folder path, rename it, and move it to another folder path. Say you have a file called “Template.xlsx” within the documents folder path and you want to rename it to “Output.xlsx” and save it in the desktop folder path. To perform this function in the command line you would enter the following:
Within the command line:
Alteryx Command Line Functionality
Back to Alteryx – how would you use this functionality to batch output multiple input files onto different output templates? The trick is to rely on the name of the input files.
Let’s pretend the above five source files are five balance sheets from separate companies that you want to input into Alteryx, recalculate the footing of each subtotal, and output onto a preformatted template file (I.E. the numbers are formatted in accounting format). The names of each input file are as follows:
- Balance Sheet 1
- Balance Sheet 2
- Balance Sheet 3
- Balance Sheet 4
- Balance Sheet 5
The first step would be to use a directory tool to input the files into Alteryx. This would give you an extra field [File_Path] with the source file path for each input file:
- C:Users\CMS\Documents\Balance sheet 1.xlsx
- C:Users\CMS\Documents\Balance sheet 2.xlsx
- C:Users\CMS\Documents\Balance sheet 3.xlsx
- C:Users\CMS\Documents\Balance sheet 4.xlsx
- C:Users\CMS\Documents\Balance sheet 5.xlsx
You would then strip out the “File Path” field and create a separate field called [Destination_File_Name] formatted as follows:
- Balance sheet 1.xlsx
- Balance sheet 2.xlsx
- Balance sheet 3.xlsx
- Balance sheet 4.xlsx
- Balance sheet 5.xlsx
You would then create a [Copy_Command] field that would feed into the run command tool in Alteryx. This field would be formatted as follows:
Copy + C:\Users\CMS\Documents\Template.xlsx + C:\Users\CMS\Desktop\ + [Destination_File_Name]
Copy Template Macro
After you have set up the [Copy_Command] field, the only remaining thing to do is create a macro that houses the run command functionality. Within the macro, you would input the source data and select only the [Copy_Command] field with a select tool in order to ensure that only this field is input into the run command tool.
Run Command Tool
Copy the exact syntax below within the run command tool to ensure that the functionality works. The Write Source portion creates a separate BAT file within your temporary Alteryx folder. The BAT file houses each of the five copy commands and is used to execute command line scripts. The Run External Program portion then runs this BAT file and creates the five separate excel template files onto the desktop folder path.
What’s Next?
After you have each of the five template files created, it becomes a simple matter of creating an additional field, [Output_Range], for the source data from the five input files that matches the file path of each separate template. The only difference within this field would be the final name of the balance sheet… C:\Users\CMS\Desktop\Balance sheet 1 (or 2, 3, 4, 5).