Load MS Excel data in Oracle using ODI

Hello friends!

I have been working on ODI since time now and I feel the tool has a lot to offer to developers out there. Each day I learn something new that I like to share with everyone, hoping that someday it helps you in your work! 

So here’s one more tutorial adding to my series of ODI tutorials. Through this post, you will learn how to transfer your Microsoft Excel spreadsheet data into a table in Oracle.

Pre-requisites: Microsoft Excel, Oracle 10g Express Edition with *SQLPlus, Oracle Data Integrator 11g.

First of all you need to create a new excel sheet! I have named my excel sheet as my_data.

my_data.xlsx

Now, you need to add a new ODBC data source. Go to Control Panel–>Administrative Tools–> Data sources. Click on Add. Give any data source name and then select your workbook. Click on Ok.

Adding data source

Once this is done, you now need to create a table called Karan_Employeein your Oracle database. The data for the table will be populated using the MS Excel sheet.

create table Karan_Employee(Empid int primary key, EmpName varchar2(30), EmpCity varchar2(30));

Now, follow the below steps for handling the ODI part!

Step 1: Create logical and physical schema for Karan_Employee table.

Step 2: Create schema for MS Excel

Under Topology navigator, go to Physical Architecture, right clickMicrosoft Excel and select new data server. Enter the DSN name which is same as the name of the data source and click on test connection.

create data server for excel

Create new physical and logical schema for the Excel file as well.

Step 3: Create new project and model folders

Once creation of schemas is done both for the table and the excel sheet, you can now create a new Project under Designer tab.

Right click your Project–>Import–>Import Knowledge modules and select following modules!

1. LKM: SQL to oracle
2. IKM: Oracle Incremental Update
3. CKM: Oracle

You also need to create a new Model folder that will contain the models for table and excel sheet respectively as follows:

creating models for table and excel

You need to reverse engineer the Karan_Employee table as well as theexcel sheet.

Step 4: Create new interface

Under your project, there exists a folder called First Folder. Expand that folder, right click Interface–>New Interface.

Inside the interface, select Mapping to map the source to target.

mapping operation

Switch to flow for selecting your IKM,LKM and CKM.

Interface design

Now finally run the interface. Check the status of the operation underOperator tab. It should not show any errors. The data of your source excel sheet is now successfully inserted in the target table.

run interface and view table

That completes this tutorial. Thanks!