Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 13

Batch Macro for Consistent Data Processing

Recommended resources

Hello! Welcome back to our journey into Alteryx macros.

In our last lesson, we built our first batch macro. We learned how the Control Parameter is the engine that runs a workflow repeatedly, once for each record in a control input stream. We used this to pass a list of values to dynamically update a Formula tool, using the Group By option to match the correct control value to the correct data records.

Today, we're going to apply that knowledge to one of the most common and powerful use cases for batch macros: processing multiple files or datasets that all share a similar structure. Instead of updating a value inside a tool, we will be updating the data source itself.

By the end of this lesson, you will be able to build a batch macro to process multiple files or datasets with a consistent schema. This is a fundamental skill for automating data prep tasks and a key topic for the advanced certification.

1. The Core Pattern: Iterate and Process

The logic for building this type of batch macro is always the same:

  1. Generate a List: In your main workflow, use a tool to generate a list of the items you want to process. This could be a list of file paths from a folder or a list of sheet names from an Excel workbook. This list becomes the input for your macro's Control Parameter.
  2. Build a Template Workflow: Create a new workflow (which will become your macro) that is designed to process just one of those items perfectly. For example, hard-code it to read and process a single file from your target folder.
  3. Make it Dynamic: Add a Control Parameter and connect it via an Action tool to your Input Data tool. Configure the Action tool to update the file path or sheet name with the value it receives from the Control Parameter for each batch.

This "build a standard workflow first, then convert it" approach is a best practice, as it allows you to test and troubleshoot your core logic before adding the complexity of the macro interface.

2. Use Case 1: Processing Multiple Sheets in an Excel File

Let's imagine you have an Excel file with sales data, where each month's or store's data is on a separate sheet. The columns are the same on every sheet. A batch macro is the perfect tool to read all of this data and combine it.

We'll watch a video that demonstrates this process from start to finish. It also shows how to handle a more complex real-world scenario where column names and order might be inconsistent, which is a great preview of more advanced techniques.

Build your first Alteryx Batch Macro

The video 'Build your first Alteryx Batch Macro' from DigiBytes will guide us. First, let's understand the challenge and the overall goal.

Watch from the beginning (00:26) to 04:51. The presenter will introduce a dataset with sales data spread across multiple Excel sheets and highlight inconsistencies in column names and order.

Now, let's see how to build the solution. The process involves three key parts:

  1. Getting the list of sheet names to drive the macro.
  2. Building the macro itself to read one sheet at a time.
  3. Putting it all together in a final workflow.

Build your first Alteryx Batch Macro

Let's continue with the video to see the step-by-step implementation.

Watch from 04:51 to the end. As you watch, focus on these key steps: (04:51 - 06:20): Using the Input Data tool to get a list of sheet names. This list will be our control input. (06:20 - 08:49): Building the macro's core. Notice how an Input Data tool is configured with a single sheet as a template, and the Action tool is set up to replace just the sheet name part of the file path. (08:49 - 12:33): Using a Dynamic Rename tool. While our learning outcome assumes a consistent schema, this is a powerful technique for handling real-world messy data where column names might differ between sheets. (12:33 - 15:16): Saving the macro and using it in the main workflow by connecting the list of sheet names to the Control Parameter input (the upside-down question mark). (15:16 - End): Refining the macro by adding a Summarize tool to handle data alignment issues caused by differing column orders. This demonstrates the iterative process of macro development.

This video perfectly illustrates the pattern. You create a control stream (the sheet names) and feed it into a macro that is designed to handle one item at a time, with the Action tool making the process dynamic.

3. Use Case 2: Processing Multiple Files in a Directory

The exact same principle applies when you need to process multiple files (e.g., .csv or .xlsx files) located in the same folder. The only thing that changes is the tool you use to generate your list.

Instead of the Input Data tool, you'll use the Directory tool. This tool points to a folder and outputs a list of all the files within it, including their full file paths. This list of paths is the perfect control input for our batch macro.

Alteryx Workflow for Processing Multiple Excel Files with a Macro
This image shows an example of a workflow that starts with a Directory tool to get a list of files, which is then fed into a macro. This is the exact pattern we are discussing.

Let's walk through an article that outlines this process.

Creating Batch Macros in Alteryx

The article 'Creating Batch Macros in Alteryx' from The Data School Blog provides a great text-based guide for this use case. We'll focus on the key steps that define the pattern.

Please read the following sections from the article: Introduction and Step 2: Prepare the Data Extraction: Focus on how the Directory Input tool is used to get a list of files. You'll see it also uses a Dynamic Input tool. For our purposes, you can think of the Action tool updating a regular Input Data tool's file path in the same way. Step 5: Convert Workflow into a Batch Macro: This is the crucial part. Observe how a Control Parameter is added and connected via an Action tool to a Filter tool in this example. The principle is the same: the Action tool updates a value (Year = 2005) for each batch run. Step 6: Use the Macro in a New Workflow: See how the saved macro is brought into a new workflow and fed the list of control values. You can skim the other steps (like Data Cleansing), as they are specific to that example's data.

Whether you are processing sheets or files, the pattern remains consistent:

  1. Main Workflow: Use Input Data (for sheets) or Directory (for files) to generate a list.
  2. Macro Workflow: Build logic to process one item. Use a Control Parameter and an Action tool to update the Input Data tool's file path/sheet name.
  3. Combine: Insert the macro into the main workflow, connecting your list to the control input anchor.
Alteryx Batch Macro Workflow Example
This diagram illustrates the universal structure inside a batch macro: the Control Parameter provides a value to the Action tool, which then updates a target tool (like an Input, Filter, or Formula tool) before the data is processed and sent to the Output.

Conclusion

Congratulations! You've now learned how to apply the batch macro concept to automate the processing of multiple files and datasets. This is one of the most significant time-saving techniques in Alteryx.

Key Takeaways:

  • Batch macros excel at repetitive tasks like reading and combining multiple files or sheets.
  • Use the Directory tool to generate a list of file paths from a folder.
  • Use the Input Data tool (configured to import a list of sheet names) to get all sheets from an Excel file.
  • This list of paths or names serves as the control input for your batch macro.
  • Inside the macro, an Action tool updates the file path or sheet name in your Input Data tool for each run.
  • Alteryx automatically unions the results from each batch run into a single output stream.

In our next and final lesson on macros, we will cover the essential "housekeeping" tasks: saving, importing, and debugging macros, ensuring they are robust, reusable, and easy to share.