How to add footer in your Flat File in BODS

Requirement: you are generating a flat file and want a footer let’s say “total number of row count: [row_count]” to be added in the flat file.

Solution:

Create a job  Job_add_footer:

Step 1 Script_File_Property:

             Set the file path and file name for the file to be created.

Step 2 Dataflow DF_FLAT_FILE:

           Will create your flat file in the appropriate folder without footer.

           After this dataflow your flat file is generated but without footer.

           Our main logic for footer starts from Script_Row_Count.

Step 3 Script_Row_Count:

             The main aim of this script is count the number of records present in the flat file generated.

             Since my folder is in the Unix server. So here are the following code. You have to change your code according to the

              server containing the folder where file has been created.

Logic:

first define a variable which will hold the full file path and file name of the file created:

$G_PathAndFileName = $G_FilePath||’/’||$G_FileName;

Make sure to enclose it in double quotes(“ ”)

$G_PathAndFileName = ‘”‘||'[$G_PathAndFileName]’||'”‘;

Then we have to call the exec() function which will count the number of records including the header. Along with row count, it will also return the full file path and file name separated by space.

$G_File_Line_Count = exec(‘wc’,’-l [$G_PathAndFileName]’,2);

Then we need to extract the row count from the full $G_File_Line_Count string.

$G_File_Line_Count = word_ext( $G_File_Line_Count,1,’ ‘);

The output will be in  varchar. So we need to cast it to integer. And since it also contains the header so subtract -1.

$G_Record_Count = cast($G_File_Line_Count,’int’)-1;

Now we have got our record count.

Step 4 Create one flat file structure and name it as FF_FOOTER.

• –   Give same file path and file name as you have given for file in which you want the footer to be added.
• –   Skip row header should be ‘Yes’.
• –   Column delimiter should be ‘SPACE’.
• –   Add two field FOOTER_NAME VARCHAR(100)and FOOTER_VALUE VARCHAR(100).
• –    Save and close it.

Step 5: Dataflow DF_ADD_FOOTER

             Build the dataflow DF_ADD_FOOTER as follows:

Row_Generation transform

           Since we have to generate only one row as footer.

Query transform:

    Mapping:

     Footer_Name = ‘Total number of records: ‘

     Footer_value = $G_Record_Count

     Note: now open the flat file FF_FOOTER property from workspace and select Delete File as ‘No’.

Now save the job and execute it.

You will find the footer has been added in your flat file.