-
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)1 of 1 people found this helpfulHey 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).
-
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.
-
3. Re: How to bring multiple Excel files into Tableau as one connection?
Toby ErksonMay 9, 2013 7:16 AM (in response to Siraj Samsudeen)
2 of 2 people found this helpfulJust 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?
-
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
-
5. Re: How to bring multiple Excel files into Tableau as one connection?
Toby ErksonMay 10, 2013 9:43 AM (in response to Siraj Samsudeen)
2 of 2 people found this helpfulOkay, 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.
-
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)2 of 2 people found this helpfulThe 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.
-
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?
-
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
-
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.
-
10. Re: How to bring multiple Excel files into Tableau as one connection?
Dmitry ChirkovOct 15, 2015 1:24 AM (in response to Siraj Samsudeen)
4 of 4 people found this helpfulsirajudeen.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.
-
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)2 of 2 people found this helpfulthis 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.
-
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.
-
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.