Microsoft Excel Generic ODBC

Aqua Data Studio supports connecting to Microsoft Excel files via Generic ODBC. The Mac operating system does not allow Aqua Data Studio to connect to Microsoft Excel Files with Generic ODBC.

Aqua Data Studio version 17 and higher use Java 1.8. Oracle has removed the JDBC-ODBC bridge driver in Java 1.8. The driver is not bundled with Aqua Data Studio which could cause a missing driver error. Aqua Data Studio can run using Java 1.8 and still use the JDBC-ODBC bridge from Java 1.7 if Java 1.7 is added to the installation. See How to Install the Java JDBC-ODBC Driver for installation instructions.  

Contents

• Create an ODBC Data Source from your Excel file
• Register a connection for your ODBC Data Source in Aqua Data Studio
• Microsoft's ODBC Driver
• How to Install the Java JDBC-ODBC Driver

Create an ODBC Data Source from your Excel file

1. Within Windows, go to Start > Control Panel > Administrative Tools > Data Sources (ODBC) and right click on Data Sources to Run as Administrator
2. Click on the System DSN tab if you want any other user of that computer to be able to access the ODBC data source.
   Click on the User DSN tab if you want only your username on that computer to be able to access the ODBC data source.
3. Click the "Add" button within DSN tab.
4. Within the list of available drivers, choose "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" and click Finish
   
5. Within the ODBC Microsoft Excel Setup dialog, enter a Data Source Name, enter a description and click the "Select Workbook..." button
6. Browse to the Excel file you wish to use and click "OK"

   

7. Within the DSN tab you should now see the Data Source Name you entered above in step 5. Click "OK"
   
8. Now, within Aqua Data Studio, continue with the server registration process

Register a connection for your ODBC Data Source in Aqua Data Studio

For Microsoft Excel, a single file is stored on a host machine either as a .xls file or .xlsx file.

Add the ODBC data source	Create Server Group	Register a Microsoft Excel File	Enter Properties

Generic ODBC Properties MS Excel	Query an Excel File

Your Installation of Aqua Data Studio bit version ( 32bit or 64bit ) must match your ODBC Driver for Excel. Only 32bit ODBC drivers work with the 32bit version of Aqua Data Studio. Only 64bit ODBC drivers work with the 64bit version of Aqua Data Studio. Mismatching the driver type with the version of Aqua Data Studio will prevent Aqua Data Studio from successfully connecting to your ODBC datasource.

There is currently no way to distinguish User DSN ODBC driver 32bit versions from 64bit versions because of an issue with Windows ( http://support.microsoft.com/kb/942976 ).

To connect to a Microsoft Excel ODBC Datasource using Aqua Data Studio Generic ODBC:

• Make sure you have ODBC drivers available for Microsoft Excel, and that you have created an ODBC Data Source from your Excel file.
• Create an ODBC Data Source out of your Excel file so you connect to it within Aqua Data Studio ( See above )
• Create a new server Group and name it as MS Excel ODBC
• Right Click on the server group and click on the the Item which displays Register Server
• Provide a new name for the Microsoft Excel file's Server Registration
• In the General tab, pick Generic - ODBC
• In the Location Datasource field, browse to locate the ODBC datasource for Microsoft Excel you created.

The Microsoft Excel ODBC Data Source is registered successfully in Aqua Data Studio. You can now connect to the newly registered Server and perform Data Manipulations.

Queries on Microsoft Excel ODBC Data Sources do require that the Worksheet name be surrounded by brackets, like

select * from [Sheet1$]

for Aqua Data Studio to successfully execute them.

Microsoft's ODBC Driver

You can get more information about Microsoft ODBC connections here

Vendor: Microsoft