[ODI 10g] Connecting To Microsoft Excel

Connecting to Microsoft Excel using Oracle Data Integrator

This describes how  Microsoft Excel spreadsheet can be used in Oracle Data Integrator. 

Microsoft Excel is one of the many different technologies you can leverage in ODI as a source or as a target. 

Prepare your Excel spreadsheet

Prior to using a Microsoft Excel spreadsheet in ODI we need to specify a name for the different cell tables we want to use. You can have multiple names in the same spreadsheet.

First open up a Microsoft Excel spreadsheet, we will need to define a named range.

To do so, start by selecting the data you are interested in.

Then go to the Formulas tab and click on Define Name.

Specify a name in Name field, I used CITY_DATA in this example.

Click OK.

Note: This is slightly different in older versions of Microsoft Excel, please refer to its documentation to find out more information about how to name cells.

Define an ODBC Data Source

ODI will use an ODBC connection to natively access Microsoft Excel.

Open up the Data Sources menu in Microsoft Windows: click on the Start menu, go to Administrative Tools then click on Data Sources (ODBC).

The ODBC Data Source Administrator window will open up.

Click on Add...

In the Create New Data Source window, pick Microsoft Excel Driver and click on Finish.

In the ODBC Microsoft Excel Setup, enter any name in the Data Source Name field. 

I used XLS_SRC_CITY in this example. This name will be used in Topology Manager to refer to this ODBC Data Source.

Click on Select Workbook...

Using the File Explorer select your Excel file and click on OK. 

Make sure that the Read Only checkbox is unchecked otherwise you will get an error message while trying to insert data into the Excel spreadsheet (Error: '[Microsoft][ODBC Excel Driver] Operation must use an updateable query.').

You should now see your new ODBC Data Source listed in the ODBC Data Source Administrator.

Create a Data Server in Topology Manager

Open up Topology Manager and go to Physical Architecture.

Right on the Microsoft Excel technology and select Insert Data Server.

In the Data Server window enter a name in the Name field, I picked XLS_SRC_CITY.

Go to the JDBC tab.

Select the Sun JDBC-ODBC bridge in the JDBC Driver List.

In the JDBC URL template replace <odbc_dsn_alias> with the name of the ODBC Data Source you specified earlier. I used XLS_SRC_CITY in this example.

Here are the JDBC settings I used in this example:

JDBC Driver: sun.jdbc.odbc.JdbcOdbcDriver

JDBC URL: jdbc:odbc:XLS_SRC_CITY

Click on Test and make sure you get a successful connection.

Click OK.

In the Physical Schema window go to the Context tab to define a Logical Schema.

Click onto insert a new row, select your context and enter a Logical Schema name in the Logical Schema column. I used XLS_SRC_CITY in this example.

Click OK to save and close the Physical Schema window.

We now have successfully created the infrastructure we need to use Microsoft Excel in our ODI processes.