Handling Multiple delimiters in a informatica source file

Ever come across a flat file source with multiple delimiters and wondered how these source files can be handled and read accurately in informatica. Multi delimiter file data essentially looks as given below; one or more delimiter candidates define the boundaries of a column as stated in my sample data.

order_no | product $ region ~ units sold ~ discount_pct | Sales_amount | discount_candidate

X1234A4 | 234543 $ IND ~ 3 $ 1@ 297 | Y


In sample data stated above, we could see four delimiters (| $ @ ~).

Let’s discuss how the situation can be handled; In tools like AbInitio, you can explicitly give the delimiter for each column through the cobol code you write.

PowerCenter is very well equipped to handle the situation, below simple steps, have a solution on board!!

Steps to follow for this solution – 

1. Open Flatfile source in Powercenter designer, double click on it.

2. Goto Advanced file properties

3. In Column Dimiter placeholder, list all the delimiters as shown in the screenshot

4. Uncheck the property “Treat multiple delimiters as AND unchecked”

Risk: If this option is checked Integration service will look for a delimiter token “$|@~”; and as we see there exist no delimiter token in the sample data which eventually endup putting all the columns under one data column; So, to avoid the mentioned risk always ensure to turnoff this property

5. Click OK to save the changes

6. Good to go now

Preview of the data will look as below =>


  1. Manikandan says

    Hi ,

    Can you please help me on my below query ?

    I have a delimiter source file which is having 3 columns such as Eno,Location,Deptid.

    The values like below in source file


    I want these data to my target whether it may database table or flat file target.

    Like below

    Eno Location Depid

    1 IN INDIA 10
    2 US AMERICA 20

Leave a Reply

Your email address will not be published. Required fields are marked *