14 Replies Latest reply on Oct 11, 2011 6:02 PM by guest contributor

    Connecting to two Excel workbooks

    Daniel Connelly

      I have an analyst asking if it's possible to connect to two different Excel workbooks.  It doesn't look possible, but I wanted to ask anyways.  If anyone has any recommendations, please send them my way.

        • 1. Re: Connecting to two Excel workbooks
          James Baker

          Two Excel workbooks in one Tableau workbooks? Certainly - you can use Data -> Connect to Data (Ctrl-D) to add as many data sources as you like to a Tableau workbook.  Every sheet in your workbook can use a different data source.  Dashboards can be used to present results from different independent sources on one page.

           

          Two Excel sheets in one Tableau data source?  When setting up your Excel Workbook Connection, select "Multiple Tables" and set up a Table Join to define how the sheets interrelate.

           

          Does that fit your needs, Connelly?

          • 2. Re: Connecting to two Excel workbooks
            . mmaizel

            Sorry, but this just does not work (Tableau 3.5). May I suggest a 'BUG" report page - which, may not be a bug, though it can look like one to a user for whom the simplistic directions do not work at all ? Thanks.

            • 3. Re: Connecting to two Excel workbooks
              Erin Easter

              Hi, could you clarify what you are trying to accomplish? It sounds to me like you are trying to connect to two Excel workbooks. What is not clear is whether you are trying to connect to the two workbooks so you can use them on a single worksheet in Tableau or if you just want to pull data from two Excel workbooks into one Tableau workbook.

               

              Each worksheet in a Tableau workbook can be connected to a different data source. For example, you may have one sheet connected to an Excel workbook, another sheet connected to a SQL Server database, and finally a third sheet connected to another Excel workbook. You can make these connections by selecting: Data > Connect to Data. When you add a new data connection, a new worksheet is created in Tableau for that connection. You can then combine the sheets into a Dashboard if you want to see all these views together.

               

              Tableau also supports database joins, which means you relate data from two different tables (or worksheets in an Excel workbook) so you can use the data from both of them in a single view in Tableau. For example, if you have a sales worksheet that has information about the products sold and another worksheet that has the return status for each product, you can join the worksheets so you can see how much sales revenue was actually returned. You can join tables by selecting Data > Tables or by selecting the Multiple Tables option when you are first connecting to the data. Just search for "Joins" in the Tableau Online Help to learn more.

               

              Finally, Tableau does not support combining data from two workbooks (or data sources in general) into a single Tableau view. This is a feature request that we've heard in the past and we are considering it for future development.

               

              If you are still having trouble, please feel free to respond with more details on what the problem is. Also you can contact support directly if you think you've found a bug or need more specific help.

               

              Hope this helps!

              • 4. Re: Connecting to two Excel workbooks
                guest contributor

                Can you connect mutiple Excel worksheets together to form one workbook?

                • 5. Re: Connecting to two Excel workbooks
                  guest contributor

                  I can see why combining two data sources directly into a single sheet can present problems. Data type and alignment issues for time series could be really problematic.

                   

                  In my situation, there are many instances where data comes from several vendors or sources. Combining these into a single view is important. But, if we have to manually manipulate the data that could be a problem. Is there no way to accomplish this other than to physically join the data into a single table? In 5 this still seems to be an issue. Maybe I am missing something.

                  • 6. Re: Connecting to two Excel workbooks
                    Michael Cristiani

                    Anonymous(es?)

                     

                    One thing to try is to set up a master workbook, with sheets the are sourced from your various vendors or sources, which need not even be in the same folders or volumes.  You can do this by using formulas that reference the external workbooks or by setting up Database Queries to external sources.  In the latter case, be sure that the properties for the queries provide for refreshing these tables.  In the case of using formulas that reference external workbooks, it might work best if you use relative references in Excel instead of absolute references.  Now, whenever you sources change, you should open and re-save the master workbook if you wish to verify that the values in it have updated.

                     

                    In Tableau, you can then use the Excel data connection option, connect to the master workbook, and use the multiple tables dialog to join tables together as needed if that is what is required (simulated relational db), or, if the various sources just provide more records, you can use custom SQL to UNION ALL the worksheets together from the master workbook.

                     

                    I know this sounds a bit disjointed and possibly unclear, but perhaps someone better at wordsmithing can chime in here to offer clarity. In any event, it works.

                     

                    Hope this helps some.

                     

                    MANY BLESSINGS!

                    Peace and All Good!

                    Michael W Cristiani

                    Market Intelligence Group, LLC

                    • 7. Re: Connecting to two Excel workbooks
                      guest contributor

                      I have 50 tabs in a workbook, as lets say a master workbook.  I need to break these 50 tabs into 5 different business units, or 5 other workbooks to run equations off.  I want a way to be able to just change information on the master workbook (when/if changes need to be made), which then will change the tabs in the 5 different business units.  Is there an easier way to do this?  Thanks.

                      • 8. Re: Connecting to two Excel workbooks
                        Michael Cristiani

                        Jake,

                         

                        Do you mean to say you have an Excel data source with 50 worksheets (tabs), or a Tableau workbook with 50 worksheets (tabs)?

                         

                        Many Blessings!

                        Michael

                        • 9. Re: Connecting to two Excel workbooks
                          guest contributor

                          I have an excel workbook, containing two sheets with identical fields of claims data, and one sheet with membership information.  I would like to UNION the two sheets of Claims Data and then JOIN the membership sheet.

                           

                          Example:

                           

                          (SELECT * FROM ['Claims 1$']

                          UNION

                          SELECT * FROM ['Claims 2$']) A

                          LEFT JOIN [Membership$] ON A.[Begin SVC Month] =

                          [Membership$].[MembershipMonth]

                           

                           

                          Tableau gives me an error each time - "Syntax error in JOIN operation"

                           

                          I also tried to JOIN both claims worksheets with the membership sheet and then UNION the two sets of data, and am still getting the same error.

                           

                          What am I doing wrong?

                          • 10. Re: Connecting to two Excel workbooks
                            James Baker

                            I just tested something like this, this is what it looks like:

                             

                            
                             SELECT * FROM (SELECT * FROM ['Claims 1$']
                            UNION
                            SELECT * FROM ['Claims 2$']) AS A
                            LEFT JOIN [Membership$] ON (A.[Begin SVC Month] = [Membership$].[MembershipMonth])
                            
                            


                            Not sure if the nested select is needed...

                            • 11. Re: Connecting to two Excel workbooks
                              . ainiko

                              I have problems about data connection for Tableau. I used version5.2 (trail version)

                              1. I tried to connect 2 Excel sheets to join them together, in video I saw that when I go to menu bar > Data > Table... > Add New Table... then I clarified join condition and OK. So the result on Dimension Box and Measurement Box (on the left) will be added more columns but my application did not have. It was not the same result as the example

                               

                              2. I would like to ask anyone that it will be possible to join 2 Excel file together by the solution above because every time I connected to datasource so it will generate new data connection.

                               

                              Please help me clarify this. Thank you

                              • 12. Re: Connecting to two Excel workbooks
                                guest contributor

                                Is there a way to connect excel worksheet information so that i do not have to copy and paste the same items each time

                                I make a new sheet? Example A1 through A150 are the same information on every workbook sheet I creat also A1 through BB1 are the same is there a way to have this happen everytime I open a new sheet?

                                wiuthout having to cut and paste each time.

                                • 13. Re: Connecting to two Excel workbooks
                                  guest contributor

                                  hi i work for a distributor and i need to maintain the records of  purchase and sales with details of payments as well for which i use MS excel. but i want to make it simpler by linking sheets to get the required results like  monthly sales record, company purchase record, unique customer monthly details, payments details generating invoice etc..... please help me if u could

                                  • 14. Re: Connecting to two Excel workbooks
                                    guest contributor

                                    Yes, I need to continue with it.