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 EMP.txt in my windows C:/FILES folder
The flat file EMP.txt 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
Thanks & Regards
WWW.ODIGURUS.COM
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 EMP.txt in my windows C:/FILES folder
The flat file EMP.txt 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
Thanks & Regards
WWW.ODIGURUS.COM
No comments:
Post a Comment