6 Replies Latest reply on Mar 1, 2017 9:05 AM by srikanth maroju

    Union of two sheets from two different connections (sources)

    srikanth maroju

      I have 'abc' sheet in ABCD spreadsheet and 'efg' sheet in EFGH spreadsheet (Excel). These are two different source connections to Tableau desktop and now i want to combine 'abc' and 'efg' sheets in Tableau to perform the analysis. It's something like union of two sheets. Can anyone help on this?

        • 1. Re: Union of two sheets from two different connections (sources)
          Vamsi Immadisetty

          Srikanth - select both the (CTRL+Sheets) sheets and drag in data source area tableau Union the selected sheets automatically.

           

          Thanks -Vamsi

          • 2. Re: Union of two sheets from two different connections (sources)
            srikanth maroju

             

            Vamsi, How can i drag two sheets at the same time if it displays only particular sheet of the data source (connection).

             

            I want to add sheet '1' from abc and sheet '2' from efg and combine both.

            • 3. Re: Union of two sheets from two different connections (sources)
              Jonathan Drummey

              Tableau doesn't presently support unions across entirely different connections. Tableau has publicly talked about wanting to do this at some point in the future, but we don't have any anticipated release date.

               

              You'll have to do something like:

               

              1) Use Excel's ability to link worksheets to link worksheets into a single workbook. I've done this in the past but data updates require opening and saving the workbook.

               

              2) Manually a worksheet from one workbook into the other to have the worksheets in a single workbook.

               

              3) Use an ETL (extract transform load) process to do the merge. In the past I've used VP, Python, PowerShell, Alteryx, etc. to do this kind of data prep.

               

              4) Depending on how the worksheets are built you might be able to use a "scaffold" data source that has sufficient dimensionality and then use that as the primary source where the two existing worksheets are secondary sources. For example I once needed to merge data from two different systems where there were two sets of offices, so my scaffold source was a list of offices & dates and then I set up calculations in the primary source to effectively merge the secondary sources.

               

              Jonathan

              2 of 2 people found this helpful
              • 4. Re: Union of two sheets from two different connections (sources)
                srikanth maroju

                Thanks Jonathan for the reply. It helped me

                • 5. Re: Union of two sheets from two different connections (sources)
                  Egor Larin

                  Well,

                   

                  in case of excel you can do it But only with XLS

                  Not sure about other DS, but if we can set full address to DB/servers/tables then it should work as well.

                   

                  What you need to do:

                  • We need to connect to our excel using ODBC connection in Tableau Desktop

                  1.png

                  • Then choose Driver: Microsoft Excel Driver (*.xls) and push Connect

                  2.png

                  • The window will pop-up. Please choose version 97-2000 and select your excel workbook

                  3.png

                  • Tableau will fill in 'Connection Attributes' with some values
                  • In Connection window:
                    • Select Database (don't ask me why it is Database) That would be your excel file
                    • Search for Table -  click on magnifier button to see worksheets
                    • Drag-and-Drop the worksheet that you are interested in

                  4.png

                  • Now we need to switch to Custom SQL - > Data - > Convert to Custom SQL

                  5.png

                  • The code should looks like that:

                  SELECT
                      `SheetB$`.`date` AS `date`,
                      `SheetB$`.`value` AS `value`
                  FROM `Sheet1$` `SheetB$`

                  • We need to change SQL to specify file locations:

                  SELECT
                      `Sheet1$`.`date` AS `date`,
                      'table_1' as `table`,
                      `Sheet1$`.`value` AS `value`
                  FROM `Y:\Excel_tests\test workbook 97-2003.xls`.`Sheet1$`
                  UNION ALL
                  SELECT
                      `Sheet1$`.`date` AS `date`,
                      'table_2' as `table`,
                      `Sheet1$`.`value` AS `value`
                  FROM `\\folder\UserData\user\Home\Documents\folder\test workbook 97-2003.xls`.`Sheet1$`

                  • Woolya! It works!
                  • ODBC connections has a lot of limitations  But I suppose you will create an extract
                  • And I know that kind of Frankenstein, but again it works

                  6.png

                   

                  That solution doesn't work with *.xlsx files But drivers is already here in ODBC connections and in Tableau I can choose it on first steps:

                  7.png

                  But After clicking Connect it gives me an error

                  8.png

                  [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x48ec Thread 0x3a88 DBC 0xafc87d4 Excel'.

                  [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

                  [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x48ec Thread 0x3a88 DBC 0xafc87d4 Excel'.

                  [Microsoft][ODBC Excel Driver] External table is not in the expected format.

                  Unable to connect to the server "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)". Check that the server is running and that you have access privileges to the requested database.

                   

                  I found these thread, but can't implement solutions.

                  http://stackoverflow.com/questions/26244425/general-error-unable-to-open-registry-key-temporary-volatile-from-access

                  And install instructions form here:

                  https://www.microsoft.com/en-gb/download/details.aspx?id=13255

                  • 6. Re: Union of two sheets from two different connections (sources)
                    srikanth maroju

                    Thanks Egor, It's a lengthy process but i will work on the process you posted here