ODI - File Reading steps

Usecase: Here, we will read a csv file from a file location and then write to oracle db using copied source file data store.

Summary steps:

1. Know the name/type of file
2. Preparing the path that ODI can reach that area/machine/sharing folder
3. Creating physical and logical schema
4. Creating model after that we can see our file as datastore
5. Create a target table(with copy paste from the datastore or you can create manually)
6. Create a map and drag drop these objects into map.
7. Choosing knowledge modules if nesessary you may need to import new knowledge modules. Path of knowledge modules(ORACLE_HOME/odi/sdk/xml_reference). 
8. Run

Implementation Steps:

Step1: create source csv file.

File name: sourcedata.csv

Headers: Name,empid,salary

Provide some values.

Step2: upload the file to the correct location .

Here using localhost location as /home/oracle/Documents

Step3: Define source topology physical architecture

Go to Topology tab

Texhnologies

File -- new data server -- File_Generic(name)

Keep same jdbc driver details.

In the definition section provide the connections user and password and save -- ok with local(no agent)

On the created data server (File_Generic)-- new physical schema

In the definition area, privide the directory name for both schema and work schema.

Go to the context area and save it. It will show as empty.

Step4: Define source topology logical architecture

File -- new logical schema -- name(LGC_test)

Provide context values for following

Development

Global

Prod

Test

Link the created physical schema

Step5: Define source design model

Go to the design tab and create new model folder as file and then create new model as MDL_File

Provide following definition details:

Name: MDL_FILE

Technology: file

Logical schema: LGC_test

Do reverse enggineering and save

File -- MDL_FILE -- create new data store

Name : sourcedata

Resourcename: sourcedata.csv

UNDER files:

Format: delimited

Heading(number of lines): 2

Field separator: other: ,

Go to attributes and do reverae engg. It will populate all the csv fields. Save and view data.

Step6: preparing the target table.

Copy the created souredata datastore to another model for exaple HR model.(it has a oracle db connection)

Open that copied datastore and change the details like name ans resourcename s fileone.

Save

Step7: Mapping from source to target

Create a new project as file_projects

Create new mapping as map_file_import

Drag and drop both the datastores in the map area.

Link these 2 tables and map the fields. You can automap or also can do manually.

Save.

From mapping area, go to the physical tab and change the loading knowledge module to LKM file to oracle(Builtin) Global

In the Integration knowledge module, make true for create target table and truncate target table

Note: optionally you can import knowledge modules from project knowledge module section.