Merge/Union multiple data sources

4
votes

I've got 4 excel files that have the exact same columns, they are just simply broken out by Quarter. This is needed because the automated system can't handle too large of files.

In Tableau I've connected to each data source and called them Q1/Q2/Q3/Q4.

How can I get this to all work as one single year?

I've explored joining, and read articles talking about Custom SQL Unions. That doesn't work, it seems to only let me do a Union between Worksheets in the same Workbook. They're different Workbooks, and hence different data sources.

4
tableau-api
Additionally, it seems you don't want to join the tables, but rather append them. In that case, Tableau cannot help you (I guess). If the join works for you, you can always save in csv. You can join different csvs – Inox
onlinehelp.tableau.com/current/pro/desktop/en-us/qs_union.html - See list of data sources which can use UNION – Pirate X

4 Answers

4
votes

Starting with version 10.1, you can UNION worksheets from different Excel workbooks using the wildcard search feature when you specify a UNION.

Nevertheless, Tableau can handle very large Excel or CSV files, so the other choice is to append the multiple data files into one long file before using Tableau.

If your front end tool can't generate more than a quarter's worth of data at a time, it's not too difficult to append the files into one file (with a single header row) before using Tableau, especially when using CSV. There are many ways to do this, but one free toolset that simplifies tasks like this is csvkit http://csvkit.readthedocs.org

A second option is load your data into a database table instead of a long CSV or Excel file.

A third option is create a Tableau data extract from one of the files, and then append data to the extract from the other files. That's the least effort on your part, but make sure you understand how extracts work a bit first (and keep the original files around for when you have to rebuild your extract)

There is even a 4th option. Use Excel's copy worksheet command (right click on a tab) to combine all your files into a single Excel workbook with multiple worksheets (tabs). Then you can use custom SQL to combine the workbooks with the UNION ALL command. But this will require you to use the legacy Excel driver to have custom SQL available. This is not the option I would recommend for this use case, but it will work if you really hate the other choices.

2
votes

I prefer to work outside of Excel when it comes to most data sets. If I were facing this issue, I would get the data back to a format that Tableau can more easily work with using a local or hosted database. My preference is MySQL.

Download/Install MySQL Server locally

Load to new table using sql code

Or

Download MySQL Workbench for a graphical interface load

Secondarily, I would look at splitting the data vertically and not by quarter to allow for a data blend or join back in Tableau. If you have access to the report structure, choose a primary key for all four files and split the fields among them. This could get ugly so I would stick with the local database option if possible. Hope this helps spark some ideas.

2
votes

Union of CSV files is now (since version 9.3) a part of Tableau functionality: https://www.tableau.com/about/blog/2016/1/combine-your-data-files-union-tableau-93-48891

You may also want to vote for my idea of Extract union: https://community.tableau.com/ideas/7076

0
votes

If you have less than 30 spreadsheets to load then the free edition of EasyMorph would suffice. It can load multiple files from a given location (or multiple locations), automatically concatenate them and publish to Tableau Server or TDE. Disclaimer: I work for EasyMorph.