|
View previous topic :: View next topic
|
| Author |
Message |
chaky
New User
Joined: 28 May 2009
Posts: 20
Location: Bangalore
|
|
|
|
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
|
|
|
| Back to top |
|
 |
chaky
New User
Joined: 28 May 2009
Posts: 20
Location: Bangalore
|
|
|
|
searched..... searched and searched more.... found that PARSE can do it (at least by reading).... trying it now  |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10900
Location: italy
|
|
|
|
| Quote: |
| but that would be my last preference. |
unfortunately it will be Your only preference 
<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 |
|
| Back to top |
|
|
chaky
New User
Joined: 28 May 2009
Posts: 20
Location: Bangalore
|
|
|
|
| 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. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
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.
Have a look at this topic. |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
This can be done easily if you take the help of MS Access. Import fields with delimiter as comma And then export with delimiter as pipe.
You will get the desired text file. Upload it into a mainframe PS.
This will save you some effort in writing a COBOL program. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
|
|
Found this parameter in SYNCSORT documentation first, then also in DFSORT documentation.
I tried only with SYNCSORT:
| Code: |
//SORTIN DD *
13,CUSTOMER CARE,"YATES, JOSEPH G.","1,875,000.00",15,VODAFONE
13,CUSTOMER CARE,JONATHANRANDY,"1,900,000.00",17,"VODAFONE,HUTCH"
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=3),
%02=(ENDBEFR=C',',FIXLEN=15),
%03=(ENDBEFR=C',',FIXLEN=25),
%04=(ENDBEFR=C',',FIXLEN=20),
%05=(ENDBEFR=C',',FIXLEN=5),
%06=(FIXLEN=20)),
BUILD=(%01,C'|',%02,C'|',%03,C'|',%04,C'|',%05,C'|',%06) |
which gave me the following:
| Code: |
13 |CUSTOMER CARE |"YATES | JOSEPH G." |"1 |875,000.00",15,VODAF
13 |CUSTOMER CARE |JONATHANRANDY |"1 |900 |000.00",17,"VODAFONE |
and
| Code: |
//SORTIN DD *
13,CUSTOMER CARE,"YATES, JOSEPH G.","1,875,000.00",15,VODAFONE
13,CUSTOMER CARE,JONATHANRANDY,"1,900,000.00",17,"VODAFONE,HUTCH"
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=3),
%02=(ENDBEFR=C',',FIXLEN=15),
%03=(ENDBEFR=C',',FIXLEN=25,PAIR=QUOTE),
%04=(ENDBEFR=C',',FIXLEN=20,PAIR=QUOTE),
%05=(ENDBEFR=C',',FIXLEN=5),
%06=(FIXLEN=20,PAIR=QUOTE)),
BUILD=(%01,C'|',%02,C'|',%03,C'|',%04,C'|',%05,C'|',%06) |
which gave me
| Code: |
13 |CUSTOMER CARE |"YATES, JOSEPH G." |"1,875,000.00" |15 |VODAFONE
13 |CUSTOMER CARE |JONATHANRANDY |"1,900,000.00" |17 |"VODAFONE,HUTCH" |
From there it should be quite easy to remove the quotes and squeeze the fields. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
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.
It's a design lack. |
|
| Back to top |
|
|
Nilanjan Sikdar
New User
Joined: 26 Feb 2016
Posts: 9
Location: India
|
|
|
|
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 |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1430
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. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
