4 Ways to Combine Multiple Files in Alteryx

Combining files is one of the most common methods of working with data in Alteryx. There are 4 different ways to combine files that will be dictated by your use case. You can wild card import files for quick consolidations, manually append with a Union tool or use a Dynamic Input or Batch Macro depending on the file format.

Alteryx can combine multiple files manually using union tools or you can use several other methods to dynamically input files, filter a list of files or connect to a folder of files

Alteryx is a super flexible platform, and there’s usually at least a few different ways to combine files that range from being super quick to taking a little bit more effort, such as the case of a batch macro. We’ll explain the different import methods and the pros and cons of each one.

Let’s dive in!

Choosing the Right Method for your Use Case

There is some nuance with the different import methods to combine multiple files in Alteryx that you should be aware of when choosing how to import files. Here are the high level details of each method:

  • Union Tool – Manually combine multiple files, more control but you have to create manage multiple input tools.
  • Wild Card Input– Automatically consolidates all files in a folder, must be the same header format.
  • Dynamic Input Tool – Allows you to filter a list of files in a folder or folders and consolidate them, must be the same header format.
  • Batch Macro – Imports multiple files in a folder or filtered list regardless of header format.

The list above is ordered from simplest to more complex, but don’t worry even though a Batch Macro has the word “Macro” in it it’s much easier to setup than Excel VBA Scripts.

Union ToolManual Consolidation Process

The Union Tool is available in the Join section of the Alteryx Toolbar. It accepts multiple inputs, by default aligns them by column header name, and appends them together. It’s used to combine files from two or more inputs. If the column headers differ, it will align what it can but will not drop data.

Screenshot of a Union Tool configured to combine two files

The Union tool can also be configured to append files together based on column position instead of name. It’s one of the simplest ways to combine files, the drawback is that it requires you to manually add files to a workflow and would not be suitable for importing a large number of files or having a folder where you can add files without having to manually adjust your workflow.

Wild Card InputMultiple Files Same Format

The fastest method to consolidate multiple files in a folder using Alteryx us with a Wildcard input. Modify the file name or file location in an input tool by renaming the file with an asterisk * . The asterisk is a wildcard and instead of pulling in a single file, it will import all files that meet that criteria if they have the same header format.

The example below shows the relative file path: .\Sales\*.csv where the name of a file path has been replaced with: *.csv this tells Alteryx to import all files from the Sales folder that end with .csv

Using a wildcard input to import a folder of files into Alteryx

When the workflow runs it will import all files in a folder automatically. The screenshot below shows that the Sales folder contained files from January, February, and March which are all consolidated into a single data set within the input tool.

Example of a list of files imported in the workflow results

Wildcard inputs are incredibly useful and easy. The * can be used in place of an actual file name and all files will be imported that match the criteria.

If a file does not have the same schema, or column headers as the first file input from the folder Alteryx will provide a warning in the workflow results that not all files have been consolidated. Be cautions of files that come from other departments or people as they tend to occasionally add notes columns or change header names which will cause this method to fail to import all files.

Dynamic Input ToolMultiple Files Same Format

Use the Alteryx Dynamic Input Tool to convert a list of file locations, typically from a Directory Tool to convert a list of file locations into one consolidated data set. To use this method, you’ll need a list of files in your dataset, connect it to a Dynamic Input Tool, provide it with an Import Template and set a column for it to read file names from.

The Dynamic Input Tool is available in the Developer section of the Alteryx Toolbar.

The Location of a Dynamic Input tool in the Developer Toolbar

We’ll use a directory tool to get a list of files in a folder. The Directory tool is in the In/Out section of the Alteryx Toolbar.

The directory tool, when pointed to a folder will return a list of all of the files and meta data about the files, such as the date last edited or created that could be used as filters prior to going into the Dynamic Input Tool.

The results of a Directory Tool showing all files in a folder location

To configure a Dynamic Input Tool, connect it to a Directory Tool or a data set that contains a file name or file location. Then click on “Edit” to select a template file. This is the file Alteryx uses to check if the additional files after it match the same column headers.

The second step is to select which Field and Action to apply. Be careful on this section, because you want to replace an Entire File Path with a Full Path name. Other times you might want to change only the File Name from a File Name field in the incoming data.

Configuration of a Dynamic Input Tool when connected to a Directory Tool

When you run your workflow watch for workflow result errors or warnings. Common ones occur because the data isn’t the same format across files which causes them not to import or there is a mis-match between the Field and the Action. The mismatch becomes apparent when files end in something like: .xlsx.xlsx or .csv.csv it means the field isn’t replacing the full part of the file location of the source template.

Batch Macro Multiple Files with Different Formats

Alteryx requires a few steps to setup a Batch Macro, but once you set one up you can use and re-use it many times. The idea is that instead of importing a single file, you feed a list of files into a macro which in the Alteryx context is a mini workflow. You save the workflow and add it back to the primary workflow as its own tool.

Data is passed from the primary workflow into a “Control Parameter” which uses an “Action Tool” to tell the connected tool which field to update. The data is then passed into a “Macro Output” which tells Alteryx to exit the Macro.

All of the Macro tools are available in the Interface Section of the Alteryx Toolbar.

To create an Alteryx Batch Macro, follow these steps:

  • Create a New Workflow – Macros are mini-workflows and get saved as their own files which are later added into other workflows.
  • Connect the Control Parameter to the Lightning Bolt above an Input Tool – When you a connect an interface tool like a control parameter to another tool an Action tool is automatically generated.
  • Add a Macro Output Tool – Connect a Macro Output Tool to the output of the File Input Tool
  • Enable the Interface Designer – Go to View, and turn on the Interface Designer. This is a pop-out or panel that you can configure Analytic Apps and Macro Settings.
  • Change the Output Configuration – Use the dropdown menu to select “Auto Configure by Name (Wait Until All Iterations Done). This step tells Alteryx not to error out if the file formats change between files.
  • Save the Workflow as a Macro

The steps look like this:

Illustration of the steps required to setup an Alteryx Batch Macro

Action Tools are typically used to tell Alteryx which parameter or which setting of a connected tool you want it to update. Many Tools have a number of options to update but Alteryx needs to know which one to update. The Control Parameter and Action tool update the input file by default so no change is necessary.

How to enable the Alteryx Interface Designer

The Interface Designer will appear as a panel or a pop-out. Click on the Cog / Settings Wheel on the left side to adjust configuration settings of the Macro.

Example of telling Alteryx to Auto Configure by Name (Wait Until All Iterations Run)_ to avoid batch macro errors with multiple file formats

After saving the Batch Macro, a file will be available when you right click in another recently opened workflow, or your original workflow where you can go to Insert > Macro > Batch Macro or whatever you named the file

How to add a Macro to a workflow after saving the .yxmc file

Connect the Batch Macro to the directory tool or data set that contains file names. Then select the column you want to update. If there’s a mis-match the tool will error out. If you don’t want to update the full file path, you may need to edit the Action Tool inside of the Batch Macro. When you save Macro changes they will automatically update in the primary workflow file.

Configuring a Batch Macro control parameter input with a field name

When you click on run, you will see all of the files get consolidated, similar to a Dynamic Input Tool but Macros will consolidate everything no matter if the column names or schema differ.

Batch Macros can also be used to prep data one file at a time. For example, removing headers from each file one by one instead of consolidating all files and then applying data transformations.

Conclusion

Alteryx gives users a number of ways to combine files. The Union Tool is the most straightforward but requires manual intervention whenever files are added or changed. They’re also not dynamic. A regular Input Tool with an asterisk * wildcard will quickly import all files in a folder if they’re the same format. A dynamic Input Tool combined with a Directory Tool will import multiple files and lets you filter down the list of files being imported more dynamically. A Batch Macro can be configured to ignore the file schema and is often used to consolidate folders of files where the formatting changes between files.

Scroll to Top