Reading in multiple files with different field schemas

This article is part of the CSMacroDevelopment Series. The goal of this series is to communicate tips, tricks, and the thought process that goes into developing good, dynamic macros.

It provides a technical walkthrough of building a Batch Macro in Alteryx Designer. It assumes prior experience with standard macros and a working knowledge of the Designer’s interface and tools.

If you’re new to batch macros or macros in general, go to the Alteryx Academy to learn about macros:

Macros - Interactive Lessons

The Directory tool can be very powerful when reading in multiple files from a folder, either on your local drive, or network location. Once you have the list of file paths you can use the Dynamic tool or create a batch macro to read all of thesefiles in.

The Dynamic Input tool can be used if all your files have the same field schema.

However, if you need to read in multiple files with different field schemas - some files may have extra columns, missing fields, or different field orders - you will receive the warnings below when using the Dynamic Input tool to read in the full file paths.

When you need to read in multiple files with different field schemas, using a wildcard input (like *.csv) in Alteryx may not work as expected. That’s because the fields in each file don’t always match up—some files may have extra columns, missing fields, or different field orders. This can result in inconsistent or incorrect data.

To solve this, you can build a Batch Macro that reads in each file individually, ensuring the schema of one file doesn’t affect another.

BUILD A BATCH MACRO

1. Bring in an Input tool and select one of the files you wish to read in. In this example I use .xlsx files, but you can choose whatever file format you wish to read in. However, it needs to be the same file format for all files you are reading in.
2. Add a Control Parameter tool above the Input tool. This allows you to pass one file path at a time from outside your macro. There is no configuration needed on this tool.
3. Drag a connection from the 'Q' anchor of the Control Parameter tool down into the lightning bolt anchor of the Input tool. This adds an Action tool.
4. In the Action tool, select the file path in the configuration window. This adds it into the 'replace a specific string' option at the bottom of the configuration window. This means is that the value you pass through your control parameter will replace this in the input tool.
5. Add a Macro output and connect it to the Input tool. This allows the data to flow out of the macro back into your original workflow.

INTERFACE DESIGNER

Now got to View > Interface Designer (ID) and select the Properties icon on the left hand side.

In the Output mode, change the option to:

• Auto Configure by Name (Wait until all iterations Run) - this unions the fields with the same names.
•  Auto Configure by Position (Wait Until All Iterations Run) - this unions field 1 etc. in the same position for every file read in.

This removes the warnings you were getting with the Dynamic input.

INSERT THE MACRO IN YOUR WORKFLOW

1. Select File>>Save As - Save the macro in a location where you can access it. Alteryx will know this is a macro workflow because you have added a control parameter, as well as a macro output.
2. In a new canvas, right click, then select Insert>>>Macro.
3. If you are reading in a list of files you can then use the directory tool to access the folder with the files in it.
4. As this is an .xlsx example, add the sheet name for each of the files. If you are reading in a different file type skip this step.
5. You can then configure the control parameter and select 'Full path'.

Attached is an example workflow (Version 11.0) which you can use as a template to build off.

*Although this macro has been tested, it may not work in all scenarios.*