Question
Bank of America
US
Last activity: 6 Nov 2019 13:13 EST
How to process multiple values in csv files in file listener
We have a requirement where in external team sends csv file and we need to parse each and every row and create case ( basic scenario)
Column are as follows
(these are some example:)
Name|ID|Address|Country|Skills
John|123|123 Lane|US|Java,Pega,SQL
My question - last column has multiple comma separated values. I can parse delimit using "|" the various columns like Name etc and save to DB but how do we handle the last column where we have comma separated values in various rows in DB like:
John|123|123 Lane|US|Java
John|123|123 Lane|US|Pega
etc
Also how do we handle in File Listener service activity in Parse Segment section where I need to read every rows taking "," in account
Pegasystems Inc.
CA
Is "|" your delimiter or ","? If "," is the delimiter for each column, the last value which contains a "," will be enclosed within double-quotes. You can map the entire value to clipboard using Pega's parse-delimited rule. Within the service activity in parse segment section, you can further extract tokens from "Skills" using "," as the delimiter.
So, in your example, if the csv is viewed in a text editor such as notepad, confirm if you see the following:
Name,ID,Address,Country,Skills
John,123,123 Lane,US,"Java, Pega, SQL"
If the above csv is run through our file service and you skip the first row and map the columns to pega properties named Name, ID, Address, Country and Skills, after parsing the first row, the values on clipboard will be as follows:
Name: John
ID: 123
Address: 123 Lane
Country: US
Skills: Java, Pega, SQL
You can process this data however you want within the service activity.
Bank of America
US
John|123|123 Lane|US|Java,Pega,SQL
this is what I see in notepad, I was wondering how do I separate out the last skill columns where it is comma separated
Thank you taking time to answer
Pegasystems Inc.
CA
First you need to run this past a parse delimited rule where "|" is your delimiter. That would map "Java,Pega,SQL" to a property. Within the service activity, you have two options:
1. Run this string past another delimited rule where "," is the delimiter.
2. Use a string tokenizer function to extract individual tokens from "Java,Pega,SQL".