Importing multiple files into Tableau at the same time is an incredibly useful feature for consolidating files that are saved in the same folder and you don’t to spend the time to manually union or append them together. Fortunately, Tableau makes combining multiple files easy during the import process. We’ll explain how to import multiple files in one step and explain some best practices when doing so.
Tableau calls the feature to import multiple files a “wildcard” union. The steps to complete one start out the same as a regular import, and then adjust some additional import configuration options.
In our example, we’ll use Excel .xlsx files but this method works for .csv and other common filetypes. It even works for files within subfolders.
Let’s jump in!
Table of Contents
Why You Should Wildcard Union Files into Tableau
Even if you only have a handful of files that you are importing into Tableau, we consider it a best practice to use a wildcard union to dynamically import all of the files in a folder at one time. Not only does this save time, but it sets up your larger process to be more automated in the future. For example, if you have to run a report weekly or monthly, you would have to manually append it to your dataset by opening up the Tableau Hyper file and making adjustments. By setting it up to happen dynamically, you can setup other processes to automatically run, or save reports into a folder.
These are some of the additional benefits to importing multiple files into Tableau at one time:
- Time Savings – It’s faster to save a file in a folder than it is to update a Hyper file.
- Improved Accuracy – The less changes and adjustments made to source files the less chance for errors
- Scalability – If you have to manually append large datasets together, it limits the amount of data likely to be analyzed.
- Increased Automation – Tools like Zapier or Power Automate can help automate the running and saving of files to network drives or cloud services on a schedule that can later be consolidated.
- Improved Organization – Daily or Weekly reports can be saved in monthly or annual sub-folders to more easily ensure completeness and avoid missing periods of time in your data analysis.
These are just some of the reasons why utilizing the wildcard union feature in Tableau is an important tool to have in your data visualization toolbox.
Now lets look at how to set one up!
How to Import Multiple Files from a Folder into Tableau
To import multiple files into Tableau from a folder or set of sub-folders, connect to a single file. Then Create a new Union and assign an asterisk * to part of a file name to make it a wildcard. Wildcards can be assigned to a full file name, or part of one to control which files are imported. Tableau will automatically align columns with the same header. Adjust union settings if needed.
Now let’s break it down into individual steps.
Step 1.) Connect Tableau to a single File
To begin, we need to import a single file into Tableau. This file acts as the starting point for all of the other files that we will import. Pick one that is in the same folder as other files you wish to combine, with a similar set of column headers as the other files being imported.
In our example, we will use a series of Excel files, one for each month that we want to combine into a single continuous dataset.
After the file is selected, it will show in Tableau that it is connected to a single Excel file. In this case, the July Cookie Sales file.
Step 2. Create a new Union
Below the file name is an option to create a “New Union”. A union is a way to append data and a term frequently used with SQL databases. The New Union will add a data transformation step where you can set specific settings for how Tableau handles automatically importing multiple fields. Double click New Union to launch the union editor.
Note: A Union is a term frequently used in SQL that describes a function to append multiple tables together. It’s useful to acquaint yourself with common SQL terminology even if you are working with flat files instead of connecting to a database as it is a common language in data transformation that is used across a wide range of tools.
Step 3. Adjust Union Settings for a wildcard import
Adjust the full file name to an asterisk * by pressing Shift + 8 on most keyboards. The * symbol acts as a wildcard. You can apply it to a full or partial file name in Tableau. In the example below, we used a wildcard as the full file name to import any file in a folder that ends with a .xlsx or Microsoft Excel file extension.
Click OK to save the new wildcard union settings.
We curated our data so that all of our incoming data has the same file headers, which helps us avoid having to adjust multiple columns or our dataset after the initial import.
We recommend doing the same whenever possible or using data directly out of a source system as the basis of a report. It’s typically the most consistent and will require the least amount of adjustments on a period to period basis when it’s consistent.
Step 4. Set Union relationship
When you import multiple files into Tableau, it may automatically detect that all of the columns are the same and proceed with the union after you click OK from the union editor. Otherwise you will be prompted to create a union relationship.
If this occurs, don’t worry it’s easy to update and Tableau will prompt you at the bottom of the import screen. To set a union relationship any of the columns in your dataset and make it = the corresponding column in another one of the files being imported.
This should only take a moment as most or all of the columns in your data should be the same. Where this feature becomes more useful is when you add additional fields and want to manually align them instead of aligning all of them based on column name.
Step 5. Refresh the data source
If Tableau does not automatically show you all of the files being imported and appended together in the data source preview panel, click the refresh button at the top of Tableau. You will also need to click refresh any time you add or remove files from a folder. Tableau will not automatically detect changes to files on your network drive.
By pressing refresh Tableau will run the query and append any new files that have been added into the central folder that it’s connected to with a wildcard union.
Step 6. Review results of merged files
The final step is to review the union results. Make sure that all columns align as expected. You can then add them into a visual like the one below that shows the range of dates being updated between the July and August files.
When you review the output of appending tables together, make sure that all of the columns are aligned as expected. there may be some issues where datasets have extra columns or misspellings between files where the columns won’t align as expected.
If this occurs, update the input data or adjust it on the Data Source screen before building a report with it to avoid unintended results.
Working with Wildcards in Tableau
If you apply an asterisk wildcard in Tableau to a partial filename, Tableau will find anything that begins or ends with the written text ignore anything that has been wildcarded.
For example, here are some common ways that we could setup a wildcard union with the file name “July Cookie Sales.xlsx”
- Starts with: July*.xlsx – pulls in all files that start with the word July
- Ends with *Cookie Sales.xlsx – pulls in all files that end with the word Cookie Sales
- All Excel Files *.xlsx – pulls in all files that end with the file extension .xlsx
After setting the wildcard, refresh the dataset to bring in the new set of files using the wildcard union.
Certainly! Here are the revised sections with simpler sentences and the Troubleshooting Tableau Union Errors section in bullet points:
Importing Multiple Files into Tableau from Sub-folders and Parent Folders
Tableau gives users the ability to import from a parent folder, the folder that a file is currently sitting in or subfolders. These settings are adjusted at the bottom of the Union Editor settings. Importing based on subfolder is extremely useful when importing large numbers of files that are saved by month or year. Tableau will go into each folder and can be set to automatically consolidate all files into a single dataset.
Click OK to save the settings, and refresh your data source to bring in the new set of files.
Troubleshooting Tableau Union Errors
When setting up a wildcard union there are a number of errors that can occur which are important to be aware of. Most of these errors occur when there are differences in the data between source files that are being appended together.
- Check data types – Ensure each column in your tables has the same data type.
- Align column names – Verify that all columns have matching names across tables.
- Look for hidden columns – Remove any extra columns that might cause mismatches. Excel is notorious for having columns that are active but empty. You can identify them in Excel by pressing CTRL+ END to navigate to the last active cell in a sheet.
The most consistent data is, the less like there will be errors when importing it which is why we recommend importing unmodified data from source systems whenever possible and transforming it directly in Tableau.
Conclusion
Importing large numbers of files and consolidating them with a wildcard union in Tableau is a great alternative to working with live connections to data sources. You can even use tools like Microsoft Power Automate or Zapier to save files that come from scheduled reports automatically from your e-mail to use as the basis of a Tableau report.
To append multiple files automatically in Tableau, get a single file as a starting point. Then create a new union, and modify the union settings. Change the filename of the single file being imported into a wildcard by using the * asterisk symbol to replace part of or the entire filename.
When working with data that’s being appended make sure that the data has consistent columns to avoid having to do too much additional data transformation.