2 Replies Latest reply on Mar 12, 2014 8:17 PM by Andrew Davies

    Creating joins between multiple Excel files [Trial user]

    Andrew Davies

      Hi there,

       

      I'm evaluating Tableau at the moment and I'm having difficulty in creating joins between multiple Excel files. If I put all of the Excel worksheets into a single Excel file it all works well, relationships are even auto-assigned correctly, but I cannot for the life of me get multiple Excel files (working as individual connections) working.

       

      I've had a search and the only real solution presented is to combine everything into one sheet, but given I'm evaluating Tableau prior to purchase, it's a bit concerning that I can't even make joins between a few Excel docs.

       

      For the record I am trying to do a fairly layered set of joins.

       

      It uses 4 tables with the following fields:

       

      Table 1:

      Date (dimension), profit (metric)

       

      Table 2:

      Date (dimension), Customer ID (dimension), revenue (metric)

       

      Table 3:

      Customer ID (dimension), CustGroup (dimension)

       

      Table 4:

      CustGroup (dimension), CustName (dimension)

       

      I’ve gone and mapped the relationships between Date in tables 1 &2, Customer ID in tables 2 & 3, as well as CustGroup in tables 3 & 4. With that theory I was hoping to be able to pull in Date from Table 1 and CustName from Table 4 to create a crosstab and data to play with, but to no avail.

       

      Is there some silly thing I'm not doing, is there a technical constraint stopping this, or is what I'm doing just illogical?

       

      Thanks!

        • 1. Re: Creating joins between multiple Excel files [Trial user]
          Noah Salvaterra

          That is consistent with my experience. You can do joins between sheets in an excel workbook, but not between excel workbooks. I'm not sure if this is a Tableau limitation or one in the jet driver it uses for connecting to excel files, my guess would be the latter. I think I heard there would be a new driver in an upcoming release, but it is possible that was just a dream. I'm sure someone else will be along any minute with more of the technical details, but I didn't want to leave you in the land that joins forgot any longer than necessary. Better to explore some other features.

           

          That issue is something I recall from when I used to have a lot of stuff in excel. I remember it being confusing, but lately I don't even notice. I use excel to make quick test data for an idea, but most days I could do without it. Tableau rocks, you should totally buy it.

           


          Another short term workaround is to export the sheets you're interested in as comma delimited files. In that case you just need the files in the same folder and you can join away.

          • 2. Re: Creating joins between multiple Excel files [Trial user]
            Andrew Davies

            You're right! I just converted them to CSV and opened them as a text file connection, worked brilliantly. It even recognized tables in the same directory that I might want to join, despite having only connecting one of them at the time. Very cool.

             

            Excel has been kind of painful to work with. Originally I couldn't even connect xlsx files but I found via Google that it was because I had a 64-bit version of Office installed, solution being I had to apply somewhat of a patch. Link is here for that driver for anyone else that stumbles on this post: Download 2007 Office System Driver: Data Connectivity Components from Official Microsoft Download Center

             

            Thanks for your help Noah. If anyone else figures out how to connect multiple Excel docs without converting to CSV, it'd still be good to know!