Migration of Data from Files to RDBMS through ODI 11G

Delimiter File :

Topology Setup:

In ODI File will be containing default data server as FILE_GENERIC

 

Now create a Physical server to file generic

Imp Note: Always you need to give forward slash when you are giving directory..

And give the context and save it ..

The physical Schema has been created..

Model:

Files cannot be reverse engineered.

The files need be Reverse Engineered at Model level

Right click on Model=> New datastore

In definition Research our resource and name it accordingly..

In the Files option Select File format => Delimited

If heading is available give heading as 1

Record separator => MS-DOS for windows

Field Separator => Comma

 

In the Columns Option (add columns by clicking on + as per source if required)

And click on Reverse Engineer to import File into ODI Model.. Save it

The Data store is ready to design mapping

Incase if you find the date format is different from source to target you can change it by using conversion_fucntion (to date)..

Drag source from File model and target from Oracle DB folder and design a mapping ..

Flow looks like below

LKM : LKM File to SQL

IKM: IKM SQL Control append

Flow Control : Make it false

Truncate : True

Save and execute it …

 

 

 

 

Fixed width:

Converting Excel file into Fixed width

Here mention save as type as : Format text (Space limited )

Import fixed width file to ODI ,,

 

Import File into ODI,

The files need be Reverse Engineered at Model level

Right click on Model=> New datastore

Research our resource and name it accordingly..

In the Files option Select File format => Fixed

If heading is available give heading as 1

Record separator => MS-DOS for windows

Field Separator => Select Tab

 

Once you click on reverse engineer the window looks like below..

Select your columns correctly..

Give ok..

The columns will be imported like below..

And rename the columns and give type for every column as string only then only it will access to give the data.,

 

The data has been imported ..

 

>> Fixed width File has been imported successfully , Now design the mapping as like delimiter..