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

Published: 10 Mar 2020
Last Modified Date: 08 Jul 2022

Question

How to implement the third option Use Other Databases (ODBC) connector in Legacy Connection Alternatives - Community Forums?

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

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

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

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

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

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$`