Alteryx Designer Desktop Knowledge Base
Definitive answers from Designer Desktop experts.The Ultimate Input Data Flowchart
"I have a bajillion files that I need to bring into my workflow... wayyyyyy too many for me to use individual Input Data tools! What's the best way to do this?"
- every Alteryx user ever
Bringing in multiple files at once can sometimes seem like a daunting task, but is often a necessary piece in developing those wonderful time-saving workflows. Here's a handy flowchart to help you get those tricky files in without a hitch!
1. Input Data with wildcard
Most of the time you just need an Input Data tool to get your data in, which is great, because it's by far the easiest and fastest method here.
Requirements:
- You need all of your files to be in the same directory.
- All files must be the same type (e.g. csv, xlsx, yxdb)
- All files must have the same schema. (The same field names must be in the same order and have the same data types.)
How to do it:
- Configure an Input Data tool to bring in one of the files in your directory.
- In the connection string, replace the filename (or a portion of the filename) with an asterisk (*). The asterisk is a wildcard that essentially takes the place of anything.
So in our flowchart above, this connection string will look in the directory C:\Users\akoszycki\Desktop\Input Data\Data to Input and then pick up every file that starts with "CO Store File - " and ends with ".xlsx".
Results:
- All of the data from these files is stacked on top of each other.
- If you want to see which file the data comes from, use the "Output File Name as Field" option in your Input Data tool configuration.
2. Dynamic Input with file paths
Sometimes the universe conspires against you and you can't use a wildcard. Why? Well maybe it's impossible to have all of your files located in the same directory, or maybe there are a ton of files in the directory that you don't want a wildcard to pick up. In this case you'll need to use a list of filepaths to feed a Dynamic Input.
Requirements:
- All files must have the same schema. (The same field names must be in the same order and have the same data types.)
- A list of filepaths to import.
- A file to populate the "Input Data Source Template" configuration of the Dynamic Input tool. This file will always have to be available for the tool to base its field schema on, and can be any of the files you wish to input. It *will not* be input if it is missing from the aforementioned file list.
How to do it:
- Configure the Input Data Source Template in the Dynamic Input just as if it were a regular Input Data tool.
- This tool includes an option to "Read a List of Data Sources" that will update the connection string based on your list of filenames.
Some file types require a table or sheet specification, such as Microsoft Excel or Access database files. If the sheet or table name is always going to be the same, you won't have a problem with the Dynamic Input tool. However, if your files have different tables or sheets that you're pulling from, you may get an error like...
If this happens, you'll have to add the table/sheet to the end of the file path in the form ‹file path›|||‹sheet/table›. For example, in the flowchart above I'm specifying "Sheet1" as the worksheet to import from the Excel files by using a simple formula:[FullPath]+"|||Sheet1$".
3. Batch Macro with file paths
No matter what you do, it seems you always get these pesky messages that keep your files from coming in. This is when we bring out the big guns - Batch Macros. Though this last method is the most work-intensive initially, let's remember to keep in mind what my good buddy Confucius always says...
The batch macro will run a workflow one time for each file, updating the Input Data tool each time. It would generally look like this:
The Batch Macro will automatically read in files which have the same field schema without errors. But what happens if your field schema is different from one file to another?
This is where the batch macro really comes into its own! Navigate to the Interface Designer (View > Interface Designer) output mode 
Once you have configured the Interface Designer you can save the macro and insert it into your workflow (Right click on the canvas Insert > Macro...). There is an example workflow attached which includes dummy files and the Batch Macro to see how the process works.
That should do it for inputting your data! Comment below or reach out to Customer Support with any questions!
null
Hey @jishnusyaman - yeah so you could just use option #1 for this.
Use the wildcard to read in all the files you need, and just point at the specific sheet in 'Table or Query'
Hello,
Am trying to use this workflow to solve a problem where I need to union all text (tab delimited) files that has different schema. As am very new to this product am not able to understand how to configure Place holder file input in the batch macro setup.
info:
total files - 11 txt files
total records - 490
format 1 - 4 files - 57 columns
format 2 - 7 files - 56 columns
difference - same schema but one additional column.
Problem - At any given point we never know when the extra columns will append to which file.
Solution - we need only identical columns as output.
For example,
The above is the macro i recreated. While using the macro in another WF the loop fails to pick the other files specified in the directory as in the below
.
The total records in the 11 file are 490 but the WF gets out only 176 records post processing. I look for some checkpoints that I need to follow to get all the records as output.
Thanks in advance. looking forward.
UPDATE - I retried the below process from beginning and it worked. Not sure what I was doing wrong the first time
Hi,
I have got the batch macro idea to work with my own data, however, I want to make the following changes:
Start Data Import on Line: 5 (rather than line 1)
- Import only a selection of columns.
I would like these steps to occur before the data is outputted to my workflow.
For #1, I made the change within Batch Correct.yxmc in the Input Data tool (the placeholder file)
For #2, I added a Select tool after the input data tool but before the Macro output tool.
See below screenshot.
I then saved the Macro and inserted it into my workflow, however neither change worked.
Does anyone have any ideas?
Hello,
First off I am new to Alteryx so there is a lot of room for user error here.
I am having problems with a very similar workflow that this thread describes. I migrated about 100 .xlsx files to one folder to simplify the process. They have varying number of tabs but they all have the same one tab of interest with the same name. I am attempting to use the batch macro to combine the one tab from all files in the same folder. Unfortunately the wildcard option does not work seemingly bc I cannot effectively identify the tab name for each file, again it is the same name for all.
Using the batch import option identified above I receive an error, "tab name" does not match a sheet or named range... the Excel file is corrupt, or similar errors depending on configuration. As for configuration I have tried auto configure by position and name (either should be sufficient for the data of interest but not for all data in the tabs), I selected the file path with a wild card for the place holder as I didn't see any other viable option, I have tried update data tool and update value for action config., directory and file path options for the macro itself, and I selected the folder path for the directory input.
I am not certain where each of the unique elements should be defined in Alteryx so any and all help is appreciated. I have done many searches on this topic which has gotten me to this point.
Cheers and thanks for your help.
I'll be off and running with edits once I can access all data in one venue.
I am having problems with this too and receiving an error message based on the picture below. The error message keeps popping up concerning row 296 of the data.
Hi,
Great macro thanks for building this. One issue i am facing. when i share my workflow with others there is an error in the path of the macro. Every time we have to reconnect it with the downloaded macro. Any reason for this.
