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