Scribd
Upload
6 views9 pages

Load CSV to RDBMS with ODI Steps

The document discusses loading data from a comma-delimited flat file into an Oracle database table. It involves the following steps: 1) Setting up the file technology topology in ODI to reference the source flat file location 2) Creating a file data store for the flat file and importing the column definitions 3) Importing the LKM FILE to SQL knowledge module for loading data from files to SQL tables 4) Creating an interface to load data from the file data store to a target Oracle table data store 5) Configuring the interface execution properties and truncating the target table before loading data 6) Executing the interface and checking the operator logs to verify the number of rows inserted into the target table.

Uploaded by

Avin
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views9 pages

Load CSV to RDBMS with ODI Steps

The document discusses loading data from a comma-delimited flat file into an Oracle database table. It involves the following steps: 1) Setting up the file technology topology in ODI to reference the source flat file location 2) Creating a file data store for the flat file and importing the column definitions 3) Importing the LKM FILE to SQL knowledge module for loading data from files to SQL tables 4) Creating an interface to load data from the file data store to a target Oracle table data store 5) Configuring the interface execution properties and truncating the target table before loading data 6) Executing the interface and checking the operator logs to verify the number of rows inserted into the target table.

Uploaded by

Avin
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Delimiter Flat File - RDBMS Table Loading

Hi Gurus,

Let us discuss how to load data from a comma delimiter flat file to RDBMS target table.

I have a flat file with the name [Link] in my windows C:/FILES folder
The flat file [Link] has the following data where each field separated by a comma and the first line is a
heading

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-12-80,800,,20
7499,ALLEN,SALESMAN,7698,20-02-81,1600,300,30
7521,WARD,SALESMAN,7698,22-02-81,1250,500,30
7566,JONES,MANAGER,7839,02-04-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-09-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-05-81,2850,,30
7782,CLARK,MANAGER,7839,09-06-81,2450,,10
7788,SCOTT,ANALYST,7566,19-04-87,3000,,20
7839,KING,PRESIDENT,,17-11-81,5000,,10
7844,TURNER,SALESMAN,7698,08-09-81,1500,0,30
7876,ADAMS,CLERK,7788,23-05-87,1100,,20
7900,JAMES,CLERK,7698,03-12-81,950,,30
7902,FORD,ANALYST,7566,03-12-81,3000,,20
7934,MILLER,CLERK,7782,23-01-82,1300,,10

Now inorder to load this file to oracle table of same structure , first you need to set the topology
Run ODI Studio
Go to Topology navigator
Click on physical architecture
Select file technology
Right click on new physical schema
Give the path of your source files directory in schema and work schema
click on context
Click on +
Give logical schema as SRC_FILES_LS
Click on save

Create a new model for file technology as shown below


Click on save
Right click on the new file model
Click on new data store
Now the new data store creation wizard opens

Enter the name of the data store as EMP


Click on search button in the resource field
And then select the respective file
click open
Then click on Files tab
Select the file format as Delimited
Set the following parameters as shown below
Heading : 1
Record separator : MS Dos
Field separator : ,
Then click on columns
Click on reverse engineer button as shown below
Click on save once the columns are imported

you can right click on the EMP file data store and view data by clicking on view data option
Now Expand your integration project
Right click on import knowledge modules
and then import the Loading knowledge module " LKM FILE to SQL"
Then create a new interface as EMP file data store as your source and TRG_EMP Oracle table data store
as your target.
Perform automatic mapping

Click on FLOW tab

Click on Target to see the IKM target properties


Set the "flow_control" option to false
Set the "Truncate" option to true
Click on save
Click on execute

Go to operator log
Check the log for this session
Open it and check the number of inserts

You might also like