Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Formatting CSV to Pipe.....

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Formatting CSV to Pipe.....
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Dec 02, 2014 3:36 am    Post subject: Reply to: Formatting CSV to Pipe.....
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Tue Dec 02, 2014 6:09 am    Post subject: Reply to: Formatting CSV to Pipe.....
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: 331
Location: USA

PostPosted: Tue Dec 30, 2014 1:30 am    Post subject: Reply to: Formatting CSV to Pipe.....
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: 1219
Location: Israel

PostPosted: Tue Dec 30, 2014 4:02 pm    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Tue Dec 30, 2014 5:55 pm    Post subject: Reply to: Formatting CSV to Pipe.....
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm
No new posts Format to pipe delimited with variabl... pshongal SYNCSORT 6 Wed Sep 14, 2016 2:48 pm
No new posts Creating a pipe delimited file from C... dmanwi COBOL Programming 1 Sat Mar 19, 2016 10:40 pm
No new posts Formatting data from Excel to mainframe kishpra JCL & VSAM 5 Fri Feb 19, 2016 7:12 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us