How to Import Multiple Excel Files and Sheets in Alteryx Using the Dynamic Input Tool

The Alteryx Dynamic Input tool lets you replace dozens of manual file imports with a single, reusable workflow. If you work in finance or treasury and spend time each period dragging source files into Alteryx one by one, this tool removes most of that work. The video above walks you through the full setup in Alteryx Designer. Read on for the practical context, the configuration detail, and the failure modes worth knowing before you hit them.

What the Dynamic Input Tool Does and Why It Matters

The standard Input Data tool points at one fixed source. You configure it, it reads that source, done. Every time the source changes, you reconfigure it.

The Alteryx Dynamic Input tool works differently. It reads a list of sources rather than a single source. For each row in that list, it opens the connection, pulls the data, and stacks the results. One tool, many reads.

The key concept is the driving list. The Dynamic Input tool does not discover files on its own. You feed it a structured list of file paths or connection strings, one per row. It iterates through that list and returns a unioned dataset.

This means the tool has two parts to configure: where to get the template connection (a sample file that tells Alteryx the format of the connection string), and which field in your driving list holds the value that changes on each iteration.

The Problem It Solves

Picture a standard month end consolidation task. You have 12 monthly exposure files, one per month, each containing separate sheets for different entities or product lines. To pull that into a single dataset using the standard Input Data tool, you drag in each file, select the sheet, and repeat. Twelve files, four sheets each: that is 48 manual input steps before you have written a single transformation.

Then next month arrives. The folder changes. The file names have a new date stamp. You start again.

For a large portion of finance and treasury reporting work, this is the default reality. The manual import cycle is where time disappears, and it is almost entirely avoidable.

If you are new to Alteryx Designer or want a grounding in how it fits into a finance analytics toolkit, the post Alteryx Designer Explained: What It Does, How It Works and Whether It Is Worth Learning is a good place to start. For a tour of the interface itself, see Alteryx Designer: Interface and Navigation on Day One.

Building Your Driving List of Files or Sheets

Your driving list is just a dataset with at least one column containing the path or connection string for each source you want to read. There are two common ways to build it.

For multiple files in a folder, use the Directory tool. Drop it on the canvas, point it at your folder, and it returns a row for each file it finds, including the full file path in a dedicated path column. Filter that output to keep only the files matching your naming pattern, for example files ending in .xlsx. That file path column becomes the field you pass into the Dynamic Input tool.

For multiple sheets within a known file, you need to construct a connection string for each sheet. Alteryx uses a connection string format that combines the file path with a sheet reference. The exact syntax depends on the connection type (OleDB, ODBC, or the native Excel driver), so rather than reproduce a format that may vary by environment, build this in a Formula tool by concatenating your base file path with the sheet reference your local Alteryx installation expects. Check the connection string Alteryx generates when you manually open a sheet in the Input Data tool: that string is the template to replicate programmatically for each sheet in your list.

You build the driving list using a Text Input tool for a small, known set of sheets, or a Formula tool if you are constructing strings from a base path and a list of sheet names.

When your data spans both multiple files and multiple sheets, you build the driving list in two stages. The next section covers the combined approach.

Configuring the Alteryx Dynamic Input Tool

Once your driving list is ready, connect it to the Dynamic Input tool. The configuration panel has three important settings.

First, set the Input Data Source Template. This is a single example connection that tells Alteryx what type of source you are reading. Open an example file and select an example sheet. Alteryx uses this to understand the connection format. It does not matter if this file is not in your driving list; it is a structural template only.

Second, identify the field containing the file or connection string. This is the column from your driving list that holds the path or the full connection string. Alteryx replaces the template connection with the value from this field for each row.

Third, choose what to replace in the connection. You have options: replace the entire connection string, or replace only the file path while keeping the sheet reference fixed. If you are iterating over files that all have the same sheet name, replace the file path only. If you are iterating over a full connection string that includes the sheet name, replace the full connection.

Run the workflow and inspect the output. The Dynamic Input tool stacks every result into one dataset and adds a field that records which connection string produced each row. The exact name of that field varies by Alteryx version; check your output schema after the first run. It is useful for tracing records back to their source file or sheet, so keep it until you are confident the union is clean.

Combining Multiple Files and Multiple Sheets in One Workflow

This is where the real complexity sits, and it is worth being explicit about the approach.

Suppose you have six monthly files, each with sheets named by entity: EntityA, EntityB, EntityC. You want every combination.

Start with the Directory tool to get your six file paths. Then use a cross join or a Generate Rows tool combined with a Formula tool to append each sheet reference to each file path, producing the full connection string for every file and sheet combination. With six files and three sheets, you produce 18 rows in your driving list.

Feed that list into the Dynamic Input tool, replacing the full connection string on each iteration. The output is a single stacked dataset covering all 18 sources.

This approach scales. Add a seventh file to the folder next month and the Directory tool picks it up automatically. The driving list grows by three rows. You do not touch the configuration.

What Goes Wrong and How to Fix It

Three failure modes appear repeatedly with the Dynamic Input tool in finance contexts.

1. Inconsistent column headers across sheets or files. The Dynamic Input tool unions results row by row. If one sheet calls a column Entity_Name and another calls it EntityName, Alteryx treats them as separate columns and fills the mismatched rows with nulls. The fix is to use a Select or Rename tool after the Dynamic Input to enforce a consistent schema, or to standardise the source files if you control them.

2. Mixed data types in the same column. One file might have a column Alteryx reads as numeric because all values in that file happen to be numbers. Another file has a text entry in the same column. Alteryx will coerce or error. Use a Select tool after the Dynamic Input to explicitly cast every column to the type you expect, before any further transformation.

3. Blank header or footer rows read as data records. Finance Excel files often have a title row above the column headers, or a totals row at the bottom. Alteryx reads everything and your column header row can end up as a data record, with the actual column names shifted or missing. Use a Filter tool to remove rows where the key identifier column matches the column header name, or configure the Input Data Source Template carefully to skip the right number of header rows.

Making the Workflow Reusable

A workflow that requires you to update a hard coded folder path every month is only marginally better than doing the imports manually. The goal is a workflow you can hand to a colleague or schedule without editing it.

The simplest approach is to use a Text Input tool at the top of the workflow that holds just the root folder path. Use a Formula tool to concatenate that base path with the file names returned by the Directory tool. When the delivery folder changes, you update one cell in one Text Input tool and everything downstream resolves correctly.

A more robust approach uses Alteryx workflow constants, accessible from the workflow configuration panel. You can define a constant such as %RootFolder% and reference it anywhere in the workflow. Change it once in the constants panel and every tool that references it updates.

If your files follow a consistent naming convention with a date stamp, you can also use a Formula tool with DateTimeNow() and string formatting to construct expected file names dynamically, so the workflow selects the right file for the current period without any manual input.

Build the Dynamic Input workflow once with a clean driving list, a robust schema enforcement step after the union, and a single parameterised input for anything that changes period to period. For most finance consolidation tasks, that setup typically pays back within the first two or three runs, and continues to save time every period after that.

If you want to go further with Alteryx and data automation in a finance context, browse the Alteryx and workflow automation courses on the Academy catalogue. There is a structured Alteryx learning path that takes you from the fundamentals through to full workflow automation, and the Pro membership gives you access to downloadable resources including workflow templates you can adapt directly.