Looping conecepts for load multiplefiles into single table

Using looping concept load multiple files into single table in single job

Using looping concept we can load multiple files into single table in single job. This is follow chart for easily understanding

                            

To write scripts we have to initialize the global variables:

To create variables go to Tools->variables->global

Varaible1:

$FILE_NAME: Set data type-varchar, length- 21, value-1

we will try to fetch the filename by using this . And the current filename’s data will be fetched and loaded to DB.

Varaible2:

$FILE_NO : Set data type-int, length- 4, value-1

Initialize the variable for looping

Varaible3:

$FILE_COUNT: Set data type-varchar,length- 20

: It is a variable which we used to store the count of the files.

write scripts using global variables:

Script-1:

$FILE_NO=1;

Script-2:

Dir F:\smartfolder\GL_Position\*.txt/b /a:-d>  F:\smartfolder\csvfiles\GL_position.csv        

$FILE_NAME=sql(‘New_Datastorename’, ’SELECT FILENAME FORM (SELECT FILENAME,ROWNUM R FROM SRC_GP_DEATILS) WHERE R=’||$FILE_NO)||’.txt’;

$FILE_COUNT= sql(‘New_Datastorename’, SELECT count(*) FROM SRC_GP_DEATILS) ;

Print(‘Loading data from:’||$FILE_NAME);

Script-3:

$FILE_NO=$FILE_NO +1;

write condition for while loop:

$FILE_NO<=$FILE_COUNT;

Here , we will check the count of the file names is been correct or not

Just like lets say we have totally 50 files to be loaded.

Then 1<=50 next

After increment 2<=50 like this loop will be iterated upto the condition is been dissatisfied.

After 51<=50 it goes to false path linked object

Files Path:

Source1  

Local: F:\CSV Files\GL_Postion

Filename:$FILENAME

Column delimiter ‘|’ pipe

Skipped row header: yes

To get all file names into one csv file

Here we will try to load all the filenames from a defined folder to one file by using a command as

Dir F:\smartfolder\GL_Position

\*.txt/b /a:-d>  F:\smartfolder\csvfiles\GL_position.csv        

If files are in server

Dir \\192.0.0.0\projectdevelopemnt\smartfolder\GL_Position folder\*.txt/b /a:-d> \\192.0.0.0\projectdevelopemnt\smartfolder\csvfiles\GL_position 

 Overall process is first we load all the names of the files into a table in DB. First initialize the variable value 1.we will try to fetch the filename by using query. And the current filename’s data will be fetched and loaded to DB. Increment the variable until condition is true (total count of files reached) , so that load next filename of all files.