Handling Multiple delimiters in a informatica source file

August 20, 2012 6:05 pm4 commentsViews: 976

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 =>


Leave a Reply

7 + = ten

Scared Cat Compilation

Funniest Cats

Just Bieber Pictures

Funny Video

Healthy diet

Best Vines

Cute Cat Funny Cat Compilation

Cute Kittens

Talking Cats

Fail Compilation

gay social networking

Most Inspirational Video Ever

Most Inspirational Video Ever

Most Motivational Video Ever

Funny Cats

Most Motivational Video Ever