ODI 12C - Read multiple files and store them in database table

Usecase: Here, we have multiple csv files from Source team. This usecase is to process multiple files from file location and store into database table.

Lets see how we can achieve this:

Highlevel steps

1. First write all file names from the source file location to database table using Jython script procedure. Follow my below blog. https://soalicious.blogspot.com/2022/10/odi-12c-write-file-names-from-location.html
2. Import flat file to odi studio model using a variable for file name. Follow my below blog: https://soalicious.blogspot.com/2022/10/odi-12c-importing-flat-file-to-odi.html
3. Creat a package to read the file contents one by one and write to dadabase table. Which has been described in the details steps below.

Detailed steps:

Package flow: File Load to Emp Db Table

Step1: first create a parameter table to fetch all the parametes needed to poll the files like file location, mail from, mail to , archive location etc.

Create table poc_parameter_tbl(

Par_Name varchar2(200),

Par_val varchar2(500),

Insert_dt timestamp(6),

update_dt timestamp(6)

)

Step2: Create a table to store all the file contents:

Create table poc_emp(

EmpId number,

Name varchar2(200),

Dept varchar2(200),

Salary Number

)

Step3: create model

Step4: create refersh variables

Step5: create procedures

Raise error

Truncate table

Update processed flag

Step6: create mapping

Step6: create package with following sequence to process multiple files one by one:

• Drag and drop LV_EPM_OBJ_STORAGE_SRC_FOL_PATH
• Drag and drop LV_FILE_COUNT
• Drag and Drop LV_FILE_ITERATOR as set variable and assign to zero
• Drag and Drop LV_FILE_COUNT as evaluate variable and  >= 0
• If True, 
• Drag and drop PROC_Truncate_TargetTable
• Drag and drop LV_FILE_ITERATOR as set variable and increment with 1
• Drag and drop LV_FILE_ITERATOR as  evaluate variable and <= #POC.LV_FILE_COUNT
• If True,
• Drag and drop POC_FILE_NAME
• Take mapping FileToDBLoad
• Proc_Update_Processed_Flag
• Loop to LV_FILE_ITERATOR where it is crementaing with 1.
• If False,
• Take LV_ERROR_MSG as set variable and assign with "No File Found"
• Call Proc_Raise_Error