KNOWLEDGE BASE

How To Use Other Databases (Odbc) Connector For The Legacy Connection


Published: 10 Mar 2020
Last Modified Date: 02 Jun 2020

Environment

  • Tableau Desktop

Answer

1. Download the Microsoft Access Database Engine 2010 Redistributable from Microsoft.

2. Convert an Excel Workbook (XLSX) to Excel 97-2003 Workbook (XLS) in Excel (File > Save As)

3. Connect to Other Databases (ODBC) in Tableau Desktop

1.png

4. Then choose Driver: Microsoft Excel Driver (*.xls) and click Connect

2.png

5. 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

6. In the 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
 

7. Switch to Custom SQL - > Data - > Convert to Custom SQL

5.png

  • The code should look like the following:

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

 

8. We may 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$`


 
Did this article resolve the issue?