Load CSV file data in Oracle table using ODI

Let us see how to transfer CSV file data in an Oracle database table. A CSV (Comma-separated value) file basically stores tabular data (numbers and text) in plain-text form. It is a common and simple file format used by many applications.

Pre-requisites: Oracle database 10g Express Edition with *SQLPlus, Oracle Data Integrator (build version 11.1.1.0.7)

First, you need to create your .csv file. Open notepad and write the following!

EMPLOYEE_ID,FIRSTNAME,LASTNAME 1,Dean,Winchester 2,John,Winchester 3,Sam,Winchester

Save the file with filename as employee.csv

Next we need to create a corresponding table in Oracle to store this information. Open *SQLPlus and login with your credentials. Create table using following command!

create table csv_test(empid int primary key, firstname varchar(30), lastname varchar(30));

The data in this table will come from the .csv file that we will load using Oracle Data Integrator.

Now, open ODI Studio and follow the below steps!

Step 1: Create new data server, physical schema for CSV file

Under Topology navigator, go to Physical Architecture, expandTechnologies, right click the File technology and select new Data Server.

Create new data server and physical schema

While creating your Physical Schema, you need to specify the location where your .csv file exists. In my case the file is located at C:\programs\java_documents\uploads

Step 2: Create logical schema for CSV file

create logical schema

Step 3: Create data model for CSV file

Under Designer tab, go to Models and create a new Model folder. Then create two models, one for the CSV file and the other for the Oracle table.

Right click the created csv data model and select New DataStore

Create datastore

Note: The comma in Field Separator is added because it is a CSV file, and the double quotation mark in Text Delimiter is required because the contents have comma (example: 1, Dean, Winchester).

In a similar manner, create model for the Oracle table by the usual reverse engineering method!

create model for table

Step 4: Create new interface

Finally, create a new interface named csv_test. LKM used is File to Oracle (SQLLDR) and IKM Oracle Incremental Update.

Create new interface

Run the interface. Check the status of the operation under the Operatortab. If everything works fine you should now see the data inserted successfully in your Oracle table.

Output