IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

Formatting CSV to Pipe.....


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
chaky

New User


Joined: 28 May 2009
Posts: 20
Location: Bangalore

PostPosted: Tue Dec 02, 2014 1:07 am
Reply with quote

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
View user's profile Send private message
chaky

New User


Joined: 28 May 2009
Posts: 20
Location: Bangalore

PostPosted: Tue Dec 02, 2014 2:42 am
Reply with quote

searched..... searched and searched more.... found that PARSE can do it (at least by reading).... trying it now icon_smile.gif
Back to top
View user's profile Send private message
enrico-sorichetti

Senior Member


Joined: 14 Mar 2007
Posts: 10697
Location: italy

PostPosted: Tue Dec 02, 2014 3:36 am
Reply with quote

Quote:
but that would be my last preference.


unfortunately it will be Your only preference icon_cool.gif

<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
View user's profile Send private message
chaky

New User


Joined: 28 May 2009
Posts: 20
Location: Bangalore

PostPosted: Tue Dec 02, 2014 5:22 am
Reply with quote

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
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7312
Location: Inside the Matrix

PostPosted: Tue Dec 02, 2014 6:09 am
Reply with quote

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
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Dec 30, 2014 1:30 am
Reply with quote

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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1348
Location: Israel

PostPosted: Tue Dec 30, 2014 4:02 pm
Reply with quote

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
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7312
Location: Inside the Matrix

PostPosted: Tue Dec 30, 2014 5:55 pm
Reply with quote

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
View user's profile Send private message
Nilanjan Sikdar

New User


Joined: 26 Feb 2016
Posts: 9
Location: India

PostPosted: Tue Mar 24, 2020 11:12 pm
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 352
Location: Bamberg, Germany

PostPosted: Tue Mar 24, 2020 11:23 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts COBOL VS SORT Utility for file format... COBOL Programming 6
No new posts Need assistance formatting when joini... SYNCSORT 8
No new posts Export columns from DB2 table with pi... DB2 14
No new posts SFTP to a Unix Pipe file All Other Mainframe Topics 4
No new posts Variable length(Pipe delimter) to Fix... DFSORT/ICETOOL 8

Back to Top