0 votes
1 view
in BI by (17.6k points)

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.

1 Answer

0 votes
by (47.2k points)

  • 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 to load your data into a database table instead of a long CSV or Excel file.

  • A third option is to 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.

Welcome to Intellipaat Community. Get your technical queries answered by top developers !