X

Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

How to load data from Excel using ODI 12.2.1 and Progress DataDirect SequeLink

Author: Octavio Oliveira

Background

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).

The source – MS Excel Spreadsheet

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:

  1. Select all columns
  2. Go to “Formulas”
  3. Select “Define Name”
  4. Type a name for the table (metadata)
  5. Save

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

Installing & Configuring the SequeLink solution

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:

  1. SequeLink Server for ODBC Socket, make sure you select the 32-bit version for Windows
  2. Download the SequeLink Client for JDBC

This software can be tested for evaluation for 15 days, licenses can be purchased separately from Progress.

Server for ODBC - Installation

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.

Client for JDBC - 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
directory (
..\AppData\Roaming\odi\oracledi\userlib
on Windows)

ODI Studio

In
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.

ODI Topology – Connection

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):

  • JDBC Driver: com.ddtek.jdbc.sequelink.SequeLinkDriver
  • JDBC URL: jdbc:sequelink://localhost:19996


Then create a logical schema and attach it to this physical schema according to the context you want to use.

ODI Designer - Metadata Harvesting and Mapping definition

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:


ODI Operator – Mapping Execution

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.

Join the discussion

Comments ( 6 )
  • guest Tuesday, April 5, 2016

    Each excel file needs to be created a "define name" and odbc dsn, if there are many excel files, the efficiency is low.

    Do we have any solution to batch import multi excel files or mutli sheets in one single file?


  • guest Wednesday, April 6, 2016

    Thank you Octavia. This is very useful. However do you have an example to load Excel from Linux server instead of windows with SequeLink.

    Thanks,

    AK


  • guest Wednesday, April 6, 2016

    Thank you for this article. This is exactly what I currently use, i.e. Progress DataDirect SequeLink JDBC-ODBC bridge. The only errors I ran into is with having the source Excel spreadsheet on a network folder. For now I am developing with the workbook on C: drive. Could you check at your end.

    I don't see Datastore properties in your post, and perhaps you do this already. Under Resource Name I have e.g. TABLE_NAME$. The "$" allows for automatic sourcing of additional records that might be added over time.


  • Julien Thursday, April 7, 2016

    Hi,

    1. The requirement to name cells in the spreadsheet appears to be coming from the ODBC driver from Microsoft and not ODI or SequeLink.

    2. For Linux I'd redirect you to the Progress SequeLink documentation which should cover how to install their product on Linux.

    3. The issue with a network folder could be a bug on the SequeLink side I'd open up a ticket with their Support to confirm. Thanks for the tip about using $!

    Thanks,

    Julien


  • Arturo Pérez Tuesday, June 21, 2016

    Hi, thanks for the guide, was very helpfull. Just one question... Do you know another ODBC but free?

    Thank you again!


  • Julien Tuesday, June 21, 2016

    Hi Arturo,

    Sorry I don't know of free ODBC-JDBC bridges.

    Thanks,

    Julien


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.