Type to search

Design Patterns

An improved batch input macro

Why do you need to improve the batch input macro

A typical first use case when teaching batch macros to Alteryx users is for the use case where the dynamic input tool produces a warning about files having a different schema and therefore will be skipped, or when the order of fields is different within the file. For an example see these example posts on Alteryx community:

Dynamic Input – Different Schema

Dynamic Input Error – Different Schema in the Data


Source: image taken from Dynamic Input Error – Different Schema in the Data

The underlying cause of this issue is due to the template file being different to the other files you are trying to bring in, either field names have changed, data types have changed or there are additional fields in your data. The main reason that something like this might occur is human interaction with the spreadsheet files.

So how do I improve this

I am not going to recreate things which have already been documented on Community, so I’ll just point to something already on Alteryx community which explains how to create batch macro for file inputs.

This works perfectly well, when the input data has the same field names, and it might have just been due to an erroneous field with no data which was throwing out the dynamic input tool. But what happens if the field names of your data set has changed (maybe someone was on holiday and someone else prepared the input file for you). In the example below we have the top part of three months worth of transaction data, but while the data below the headers is consistent the names change, for example Date is replaced by Period.

January Sales Data


February Sales Data

We can extend this further, where in the March Sales Data, not only is Sales renamed to Amount, the column order has been switched so Date is now the rightmost field.

March Sales Data

Each of these modifications to the schema of the file will cause issues when the files are brought in via a dynamic input tool as the field names and positions are not consistent.   Therefore the way to over come this is to additionally include a dynamic rename tool within the batch macro.

The dynamic rename tool is set up as follows

Which required the text input tool, which is connected into the R input anchor of the tool, to act as a dictionary of possible field names and the required rename.

This could be extended further by making the text input tool a macro input which would allow customisation of the dictionary via an input outside of the macro.

When this is now added to a workflow, the result is an output which unions the input data with consistent field names, which will improve any downstream process that is now undertaken.



Leave a Comment

Your email address will not be published. Required fields are marked *