There is often a need to read data from Excel spreadsheets and integrate it with different target technologies in Data Integration projects. The challenge is that MS Excel only supports ODBC connectivity whereas Oracle Data Integrator (ODI) and many other tools only support JDBC connectivity natively. This is where the Sun JDBC-ODBC Bridge packaged with Sun Java Development Kit (JDK) proved very useful to make the bridge between ODBC and JDBCS. However, starting with JDK 1.8 (required for ODI 12.2.1) the Sun JDBC-ODBC Bridge has been deprecated.
Luckily there are many third-party JDBC-ODBC bridges out there that can be used with ODI. In this blog post we will be using a solution provided by Progress DataDirect named SequeLink (https://www.progress.com/connectors/sequelink).
As an example I’m using an MS Excel spreadsheet from an Oracle Enterprise Data Quality demo named “writing-instruments.xlsx”.
Here is a snapshot of the file:
The first step is to prepare this spreadsheet so ODI can harvest its metadata. Basically we have to define in MS Excel a name for the cells. We do this by selecting all the columns and define a name for the cell. The next screenshots shows all the steps:
The steps depicted are:
The second step is to define the ODBC DSN (Data Source). To create a DSN for Excel it is required to open the 32 bit version instead of the 64 bit one (default in Windows 7 and above). To do this run the the command: c:\windows\SysWOW64\odbcad32.exe.
Then you can create a DSN using the ODBC Excel driver, as depicted in the next two screenshots:
This previous blog post explains this part as well: https://blogs.oracle.com/dataintegration/entry/connecting_to_microsoft_excel
The SequeLink solution provides a server/client architecture. In this example we need to install the SequeLink Server for ODBC Socket 32 bit version (a requirement for MS Office tools) and the SequeLink Client for JDBC.
The SequeLink Server will provide connectivity to the MS Excel spreadsheet and the SequeLink Client will connect to that server and will offer a JDBC interface. The next picture shows all the pieces of this architecture.
The first step for this part is to download the SequeLink software. From Progress website you can download two software packages:
This software can be tested for evaluation for 15 days, licenses can be purchased separately from Progress.
After the downloads have finished the server software can be installed by unzipping the file. Then navigate to the folder “sl600socket” and as an administrator launch the “setup.exe” application.
From the list of options just keep the default selection and proceed (next screenshot).
Then keep the proposed server and agent names/ports, or change them if required (see next screenshot).
In the next screen you can define a connection to the ODBC DSN (you can add more connections using the GUI later on).
Finally click “Next” to conclude the server installation.
To install the Client for JDBC, starts with unzipping the jar file (sequelinkjdbc.jar). Open the command line as an administrator, navigate to the folder where you downloaded the file and execute the following command:
java -jar sequelinkjdbc.jar
Once done, install all the client software (although we will only need the jdbc driver for our setup, this package contains a GUI, examples, docs, and other tools for testing). To do run the following command:
java -jar SLJCInstaller.jar
The following screenshot shows these last steps:
Follow all the steps and keep the default settings. We end up with a directory containing a few folders, as seen below:
Go to the \driver\lib folder and copy “sljc.jar” to the ODI 12c userlib
ODI Studio the first thing to do is to configure the Excel technology in the Topology navigator. But before that let’s confirm that ODI Studio has loaded the
right jar file during its startup. Go to “Help” -> “About –>“External
Components” and type “sljc” to filter the list. You should see the following:
If you don’t, then make sure you have copied the jar file to the right folder.
In Topology under Physical Architecture, right-click on “Microsoft Excel” technology and select “New Data Server”. Give it a name and provide the username and password (login to the SequeLink Server).
Then click on the “JDBC” tab and provide the following information (type it manually):
Then create a logical schema and attach it to this physical schema according to the context you want to use.
Go to the “Designer” tab and create a new Model for your Excel workbook. Set the Technology to Microsoft Excel and select the Logical Schema you previously created.
Click on Selective Reverse-Engineering then on ‘Objects to Reverse Engineer’ and select the tables you want to use in your Mappings
You should now see all the metadata in your newly created Model.
You can look at the data contained in the Excel spreadsheet using the “View Data” menu item.
Now we can create a simple Mapping to load that data into an Oracle table. First we need to create the Oracle target table, we can do that simply by drag & drop the newly created Excel datastore into an existing Oracle Model and then generate the DDL and execute it from ODI Studio (See Common Format Designer functionality in the ODI documentation).
Next create a new Mapping, drag & drop both source and target datastores and connect them together. The Logical Design of the Mapping will look like this:
And its Physical Design will look like this:
We can then execute this Mapping and review the results in the “Operator” navigator.
Once the execution is complete we can review the data in the target Oracle table:
We’ve now seen how to load data from Microsoft Excel spreadsheets using ODI 12.2.1 and the Progress DataDirect SequeLink JDBC-ODBC bridge.