1 2 Previous Next 14 Replies Latest reply on Sep 20, 2016 7:20 AM by vinodkumar.govardhanam

How to bring multiple Excel files into Tableau as one connection?

Siraj Samsudeen May 8, 2013 1:41 AM

I am trying to import a number of Excel files which are exported from an Oracle database. Each one of these files contain data about one table. Some of them are primary tables like Item, Purchase Order but some of them are secondary/lookup tables containing category descriptions, department names, etc.. I have about 15 such tables. Right now, in Tableau, I have to import them one by one and then use data blending to create the relationships between them. What I want to do is to import all of these files under a single connection and list them as they are coming from the source (In reality, they are coming from the same source) and then I want to create the relationships in advance of creating any visualization. Is it possible to do this in Tableau?

One hack that I have found is to combine all the above Excelsheets into one Excelsheet and then import that as a datasource - in that case, I am able to see them as one source and define joins in advance. But this means more manual work for me each time the data is refreshed - I have to combine these files manually to feed them to Tableau. Any other possibilities?

1. Re: How to bring multiple Excel files into Tableau as one connection?

Russell Christopher May 8, 2013 6:25 AM (in response to Siraj Samsudeen)

Hey Siraj -

The approach you've mentioned is the best one. The sheets must be in the same workbook else we treat them as distinct data sources (which makes sense, really - just not for the scenario you're dealing with).
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: How to bring multiple Excel files into Tableau as one connection?

Siraj Samsudeen May 9, 2013 5:20 AM (in response to Russell Christopher)

Thanks Russel for the helpful response. I understand why you treat different XL sheets as distinct data sources - Fair enough . But given that it is a best practice to export one CSV file per table (in the prototyping situations and in many other situations where reaching the actual data sources is not possible), this becomes more of a problem and does not make sense. If I have to refresh this single Excelsheet containing data from multiple sources, it is cumbersome, but I can live with it.
Like Show 0 Likes(0)

Actions
3. Re: How to bring multiple Excel files into Tableau as one connection?

Toby Erkson May 9, 2013 7:16 AM (in response to Siraj Samsudeen)

Just a second, there's a distinction that needs clarification. Siraj, you said you put all the data into a SINGLE spreadsheet whereas Russell said the SHEETS (plural) need to be in the same workbook. So, Siraj, have you placed each .csv output into its own sheet and then tried your import? For example, in the workbook OUTPUT.XLS you would have one tab with the Purschase Order .csv data, the next tab would have the Category Descriptions .csv data, and the next tab would have the Department Names .csv data.

See what I'm saying? Would that work for you?
2 of 2 people found this helpful
Like Show 0 Likes(0)

Actions
4. Re: How to bring multiple Excel files into Tableau as one connection?

Siraj Samsudeen May 9, 2013 7:31 AM (in response to Toby Erkson)

Hi Toby, thanks for your helpful clarification. Sorry, I was a bit loose with the terminology - sheets, workbook, etc. Yes, I did exactly what you are saying - put each CSV into a tab on its own in one Excel sheet. But the problem here is that I have to repeat this step of merging multiple CSVs into an Excel file each time the data has to be refreshed
Like Show 0 Likes(0)

Actions
5. Re: How to bring multiple Excel files into Tableau as one connection?

Toby Erkson May 10, 2013 9:43 AM (in response to Siraj Samsudeen)

Okay, gotcha. As an experienced Excel programmer I know you could have a macro that would import the CSVs into a workbook for you. Shouldn't be difficult at all for an intermediate VBA programmer. You could even put it on a scheduler (Windows (bleh) or Adtempus is what I love) so it would automatically do the whole process without human intervention.
2 of 2 people found this helpful
Like Show 0 Likes(0)

Actions
6. Re: How to bring multiple Excel files into Tableau as one connection?

Alex Kerin May 10, 2013 11:24 AM (in response to Toby Erkson)

The rdbmerge utility is great for merging multiple files (text or xls) into one file. http://www.rondebruin.nl/win/addins/rdbmerge.htm but does require intervention.

Also, if they are csv's and can remain as those (why even bring them into Excel?) then simple batch files could do that for you. These could even be run on a schedule.
2 of 2 people found this helpful
Like Show 0 Likes(0)

Actions
7. Re: How to bring multiple Excel files into Tableau as one connection?

Siraj Samsudeen May 10, 2013 1:14 PM (in response to Toby Erkson)

Thanks a lot Alex for both the suggestions. I can easily write a VBA Macro to merge the files. Somehow, the thought did not even occur to me. Thanks for pointing it out to me.

I know RDBMerge earlier, but it works only for merging files which have the same structure and it will not be relevant in this case - isn't it?
Like Show 0 Likes(0)

Actions
8. Re: How to bring multiple Excel files into Tableau as one connection?

Alex Kerin May 10, 2013 3:31 PM (in response to Siraj Samsudeen)

I think it doesn't matter, but equally you have no control how they are merged
Like Show 0 Likes(0)

Actions
9. Re: How to bring multiple Excel files into Tableau as one connection?

Akash Nayyar Oct 8, 2015 8:20 AM (in response to Siraj Samsudeen)

Hi Siraj,

Could you please share the macro code for merging the files.
Like Show 0 Likes(0)

Actions
10. Re: How to bring multiple Excel files into Tableau as one connection?

Dmitry Chirkov Oct 15, 2015 1:24 AM (in response to Siraj Samsudeen)
sirajudeen.samsudeen,if your file location and schema doesn't change you could try a crazy Custom SQL (will have to use Legacy Connection though)...

SELECT * FROM [Sheet1$] INNER JOIN [C:\data\file2.xlsx].[Sheet1$] [file2_Sheet1] ON [Sheet1$].[customerid] = [file2_Sheet1].[customerid]

Lonely [Sheet1$] is from a file you originally connect to and subsequent files have to be joined via [FullPath].[SheetName$].
Very hacky though.
4 of 4 people found this helpful
Like Show 3 Likes(3)

Actions
11. Re: How to bring multiple Excel files into Tableau as one connection?

vinodkumar.govardhanam Aug 29, 2016 6:56 AM (in response to Alex Kerin)

this one works fine, I have tried this on my mock up data with 2 different excels. but they should have same number of columns and naming convention as well. then they will merge one sheet over the other,.
the data is presented well. I have worked with this and satisfied with results.

thank you for the assistance.
2 of 2 people found this helpful
Like Show 0 Likes(0)

Actions
12. Re: How to bring multiple Excel files into Tableau as one connection?

vinodkumar.govardhanam Aug 29, 2016 6:58 AM (in response to Alex Kerin)

this one works fine, I have tried this on my mock up data with 2 different excels. but they should have same number of columns and naming convention as well. then they will merge one sheet over the other,.
the data is presented well. I have worked with this and satisfied with results.

do we have any other option which makes data come in to the same excel but in different Tabs
thank you for the assistance.
Like Show 0 Likes(0)

Actions
13. Re: How to bring multiple Excel files into Tableau as one connection?

Siraj Samsudeen Sep 20, 2016 3:49 AM (in response to Siraj Samsudeen)

Just for the benefit of those who are coming to this thread now (after v10 is released), you don't need to do all these hacks as v10 supports joins across datasources - so, each excel file as a different data source and you can do a crossDB join to bring them into Tableau.

If you need to merge multiple files with the same structure, then UNION feature introduced in v9.3 would be very helpful.

Combined together, UNION and cross-DB join would eliminate the need to do any other manual work to bring different Excel files into Tableau as one source.
Like Show 0 Likes(0)

Actions