Scenario 16: How to handle comma in a comma delimited file?

Scenario 16: How to handle comma in a comma delimited file?

Theory Analysis: There are multiple cases in this. We will examine each case one by one. First of all, we need to do data profiling for our source file.

What is Data Profiling?
Data Profiling is a validation process through which we are analyzing the data whether it's fit for source or not. If not then what changes needs to be done to make it fit for source or even sometimes we need to push it back to client with reason that data is not in a format which would be treated like a source as per requirement.

CASE 1: More than one Comma [,] is appearing just after delimiter [,]
CASE 2: Comma is [,] appearing in a data itself. Ex: Address column has data like this 'CA,USA'
CASE 3: Any other Char like [+] Char is appearing anywhere in data. [,]

Practically

CASE 1: More than one Comma [,] is appearing just after delimiter [,]

Data
CID,  CNAME, CITY, CNTRY,  PHONE NUMBER
1234,,,SMITH,CA,USA,,,9845679000
1235,JOHN,NY,USA,9996805944
1236,NORMA,,,CA,USA,,,7804567893
1237,CARL,,,NY,USA,889950493

Solution: It's very easy to handle. Informatica has already provided an option to handle this case.

While Importing File metadata of source file in Flat File Import wizard Step 2 of 3 Check Treat consecutive delimiter as one 

Now, it's good to go ahead with source file.

CASE 2: Comma is [,] appearing in a data itself. Ex: Address column has data like this 'CA,USA'

Data
CID, CNAME, ADD, PHONE_NUMBER
1234,SMITH,CA,USA,9845679000
1235,JOHN,NY,USA,9996805944
1236,NORMA,CA,USA,7804567893
1237,CARL,NY,USA,8899504934

Solution: There are two approach to resolve this issue.

Approach 1: Through Script modify your source and put Single Quotes or Double Quotes for each and every value.

Data After Modification
CID, CNAME, ADD, PHONE_NUMBER
'1234','SMITH','CA,USA','9845679000'
'1235','JOHN','NY,USA','9996805944'
'1236','NORMA','CA,USA','7804567893'
'1237','CARL','NY,USA','8899504934'

While Importing File metadata of in Flat File Import wizard Step 2 of 3 select Radio Button of Text Qualifier section Single Quotes. (If you have modified your source with double quotes then please select double quotes)

Approach 2: Read complete file as fixed width and in the expression with the help of SUBSTR() and INSTR() function split into columns. Only one thing we need to take care while splitting third column data we have to start from second occurrence of comma till fouth occurrence of comma.
(In many other scenarios we have already completed this.)

A real time Secret :-) We are creating Reusable transformation for splitting records into column based upon delimited char and we are using that whenever we need.
A second case where we are dependent upon reusable transformation is for removing unwanted space from data while reading from files. Yes,  LTRM(RTRIM())




CASE 3: Any other Char like [+] Char is appearing anywhere in data. [,]
CID, CNAME, ADD, PHONE_NUMBER
1234,SMITH,CA,USA,+19845679000
1235,JOHN,NY,USA,+19996805944
1236,NORMA,CA,USA,+17804567893
1237,CARL,NY,USA,+18899504934

Solution: While Importing File metadata of in Flat File Import wizard Step 2 of 3 Check Remove escape character from data and give you Char + in Text Box.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com