Connecting to Microsoft Excel using Oracle Data Integrator
By Julien Testut-Oracle on Mar 30, 2010
The posts in this series assume that you have some level of familiarity with ODI. The concepts of Topology, Data Server, Physical and Logical Architecture are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.
In this post I will describe how a 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.
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.
In the Physical Schema window go to the Context tab to define a Logical Schema.
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.
In another post I will describe how to use Microsoft Excel as a source and a target in ODI.