Loading multiple Files into a single table using ODI

Scenario: Consider there are multiple files in the source directory with File naming convention as “test_YYYYMMDD_hhmiss”  and we need to transfer all those files to target using a single interface. We need to follow below steps to achieve that.

Step 1: Get the files from the source server using OdiSFTP command(No need to do this step if the file is in local server)

Step2: Use ODIOSCommand with below parameters:

Command to execute: tail –q -n r /directory/test_YYYYMMDD_hhmiss(here “r” indicates the number of “r-1” number of header lines that to be removed. Suppose there are 3 header lines, then r = 4

directory indicates the source directory). This command will get the concatenated content of all the files in source directory and puts into the Output file.

Output File: /directory/tempfile.txt (This file will contain contents of all the files)

Working directory: /directory/(Source directory name which contains the files)

Step 3:Mention tempfile.txt as the resource name in the File Data store

Step 4:Run the interface using File to SQL knowledge module. This will load the contents of the files into target table.

Assumption: All the files in the source directory has the same file format.