I have a requirement as below. Data need to be converted to pipe delimiter from comma separated. Really appreciate if I get any suggestion. Lets say File LRECL is 200 and it is FB.
1. Comma should be converted to Pipes.
2. Double quotes should be removed.
3. Comma in a literal which comes between double quote should not be touched.
I could think of using FINDREP to replace "," and ", and ," and comma to Pipe. This could take care of point number 1 and 2. But that will also mess up the point number 3
I am just not able to think something about point number 3. Other thing is I can write a COBOL Program, but that would be my last preference.
Input data
Code:
13,Customer Care,"YATES, JOSEPH G.","1,875,000.00",15,Vodafone,Pamela,3787800,"Jammye, Woods",Done
13,Customer Care,Jonathan Randy,"1,900,000.00",17,"Vodafone,Hutch",Servo,3787800,"Sherlock",Done
Output data
Code:
13|Customer Care|YATES, JOSEPH G.|1,875,000.00|15|Vodafone|Pamela|3787800|Jammye, Woods|Done
13|Customer Care|Jonathan Randy|1,900,000.00|17|Vodafone,Hutch|Servo|3787800|Sherlock|Done
<sort> parsing takes into account only the separator itself regardless of the in string context
You will have to write a cobol program using reference modification
keeping count of the quotes to set a flag which will indicate if a comma has to be changed
My question may be stupid, but I am using PARSE for first time..... Can I not use FIXLEN and still use PARSE? I am able to do it with FIXLEN, but that is creating another problem and spacing between two fields is getting messed up now. When I tried removing the FIXLEN, it gave me syntax error.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
If at all possible, get the input data rearranged so that the fields which may be bounded by quotes are the last fields. Then it is easy, and you can even use PARSE afterwards to rearrange the data if needed into the original order.
I guess you can't do that, because otherwise they could just pipe-delimit anyway and you'd have nothing to do. There's a plan.
Confirm you can't do that. Also, are the commas necessary in the amount field?
It can probably be done in SORT as is, but with a lot of code.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Good searching Marso. It's actually mentioned in the topic I linked to, but...
With this route the potential presence of multiple embedded blanks add interest. SQZ can protect against those (using a different PAIR=QUOTE/APOST). But in the sample data text fields without commas don't have quotes around them. Back to trickiness.
Need to insert quotes around text fields and FINDREP double-quotes to single quotes, or put apostrophes and have two source values (' and ") in a final FINDREP. Actually, doesn't sound as bad as I was imagining. Still...
Probably (perhaps) the producer could have said "put quotes around all text fields", but they didn't. As they didn't use pipe as the initial delimiter. As they didn't put the fields in an order which would be easier to process.
Sorry to write in a old post but is there any solution for this? I have a similar scenario where I need to convert a CSV file to pipe delimited file keeping in mind of those double quotes.
Joined: 15 Aug 2015 Posts: 1255 Location: Bamberg, Germany
Nilanjan Sikdar wrote:
Sorry to write in a old post but is there any solution for this? I have a similar scenario where I need to convert a CSV file to pipe delimited file keeping in mind of those double quotes.
Thanks
Start a new topic for your request please and specify Input dataset and Output dataset requirements. Use Code tags.
