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
 

 

Parsing using ICETOOL with quotes

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
vina2010

New User


Joined: 06 Sep 2010
Posts: 19
Location: Bangalore

PostPosted: Wed Apr 25, 2012 3:51 am    Post subject: Parsing using ICETOOL with quotes
Reply with quote

Hi

I have CSV data coming from an external system which mainly puts quotes for those data which has comma(,) within it mainly to mask the commas as separate data columns.

For e.g.
Data has a layout as below

Emp id, Name , address Line 1 , Address Line 2 , Country, Pincode

Sample data may contain

123123, Abc, " Address Line 1, Street1",Street2,Bangalore, 575007
1234,sdcdfer,Address 1,"Street 2, Karnataka",Mangalore, 534

So quotes occur dynamically based on occurrence of comma as data and not as a delimiter. Can I use any JCL utility to create a data in formatted fashion without quotes?
Back to top
View user's profile Send private message

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Apr 25, 2012 4:26 am    Post subject:
Reply with quote

vina2010,

It would have been nice if you showed the desired output. I assumed that lengths for each field. You can change that the real lengths by changing the values in the FIXLEN field in the parse statement. Use the following DFSORT JCL
Code:

//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD *                                                   
123123, ABC, " ADDRESS LINE 1, STREET1",STREET2,BANGALORE, 575007
1234,SDCDFER,ADDRESS 1,"STREET 2, KARNATAKA",MANGALORE, 534       
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                                   
  INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=06),             $ EMP-ID   
               %02=(ENDBEFR=C',',FIXLEN=30),             $ NAME     
               %03=(ENDBEFR=C',',PAIR=QUOTE,FIXLEN=40),  $ ADDR LINE1
               %04=(ENDBEFR=C',',PAIR=QUOTE,FIXLEN=40),  $ ADDR LINE2
               %05=(ENDBEFR=C',',FIXLEN=20),             $ COUNTRY   
               %06=(FIXLEN=10)),                         $ PINCODE   
  BUILD=(%01,%02,%03,%04,%05,%06)                                   
                                                                     
  OUTREC FINDREP=(INOUT=(C'"',C''))                                 
//*                                                                 
Back to top
View user's profile Send private message
Claes Norreen

Active User


Joined: 20 Dec 2005
Posts: 137
Location: Denmark

PostPosted: Mon Oct 01, 2012 5:28 pm    Post subject:
Reply with quote

Hi,

This is very useful!

We get data from an external partner, and they are not very consistent in the way the "pack" the data... Sometimes we get numeric fields wrapped in quotes - other times they are not.

The OUTREC FINDREP is ingenious, as it can get rid of those unwanted quotes! BUT when I ran some tests, it seems you need to be aware of it, because the columns are shifted, and thus the data may not be where you expected, anymore.

How can you cater for that? Using positions in BUILD of the INREC PARSE won't do the trick.
Back to top
View user's profile Send private message
Claes Norreen

Active User


Joined: 20 Dec 2005
Posts: 137
Location: Denmark

PostPosted: Mon Oct 01, 2012 5:52 pm    Post subject:
Reply with quote

Hmm, how about this:

Code:

  SORT FIELDS=COPY                                                   
  INREC PARSE=(%01=(ENDBEFR=C'#',PAIR=QUOTE,FIXLEN=06),  $ EMP-ID   
               %02=(ENDBEFR=C'#',PAIR=QUOTE,FIXLEN=30),  $ NAME     
               %03=(ENDBEFR=C'#',PAIR=QUOTE,FIXLEN=40),  $ ADDR LINE1
               %04=(ENDBEFR=C'#',PAIR=QUOTE,FIXLEN=40),  $ ADDR LINE2
               %05=(ENDBEFR=C'#',PAIR=QUOTE,FIXLEN=20),  $ COUNTRY   
               %06=(PAIR=QUOTE,FIXLEN=10)),              $ PINCODE   
  BUILD=(%01,JFY=(SHIFT=LEFT,PREBLANK=C'"'),               
         %02,JFY=(SHIFT=LEFT,PREBLANK=C'"'),               
         %03,JFY=(SHIFT=LEFT,PREBLANK=C'"'),               
         %04,JFY=(SHIFT=LEFT,PREBLANK=C'"'),               
         %05,JFY=(SHIFT=LEFT,PREBLANK=C'"'),               
         %06,JFY=(SHIFT=LEFT,PREBLANK=C'"'))               

Only downside seems to be intensionally prefixed blanks are removed.
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 Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts Translating Quotes Khadhar Basha CLIST & REXX 6 Tue May 09, 2017 11:52 am
No new posts Group elements using ICETOOL Learncoholic DFSORT/ICETOOL 2 Fri Apr 14, 2017 11:01 am
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Converting decimal to numeric using I... Balaryan DFSORT/ICETOOL 4 Thu Feb 02, 2017 11:27 pm


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