Extracting Data from Flat file with Different Columns Delimiter

Introduction

Hare in this article, we are going to discuss to retrieve data from flat file. Sounds look easy. No, it’s not. As our flat file columns delimiters are little different.

Sample of Flat file

We have a flat file named StudentRecords.txt

If we look at the flat file we can find that the columns have different type of Delimiter like

Roll  !  StudentName  !  Calss  -  Section

Roll, Student Name and Class are separated by explanation sign (!) and Section is separated by minus sign (-).

Solution Approach

Control Flow Task

Step – 1 [ Retrieve Data from Flat file Using Flat file Connection Manager ]

In the columns Delimiter drop-down we never find the explanatory sign (!). So we need to type it down.

The Columns Delimiter Drop-down Contains

Value	Description
{CR}{LF}	Columns are delimited by a carriage return-line feed combination.
{CR}	Columns are delimited by a carriage return.
{LF}	Columns are delimited by a line feed.
Semicolon {;}	Columns are delimited by a semicolon.
Colon {:}	Columns are delimited by a colon.
Comma {,}	Columns are delimited by a comma.
Tab {t}	Columns are delimited by a tab.
Vertical bar {|}	Columns are delimited by a vertical bar.

Note:  There is an option in this Drop-down that if any column separator is not found, we can directly type it down.

Step – 2 [ Save the records into another Flat file Destination ]

In the destination flat file connection manager, we use column delimiter as minus sign (-).

Now execute the package to generate the destination flat file.

Step – 3 [ Now read the Destination Flat File as Source ]

Step – 4 [ Execute package ]

Hope you like it.

Posted by: MR. JOYDEEP DAS