MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

DFSORT extract CSV file and exclude comma for certain data

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Mon Oct 27, 2014 11:28 am    Post subject: DFSORT extract CSV file and exclude comma for certain data Reply with quote

Hi,

I am trying to remove the comma between double codes alone first and then parse each column in specific postion. Each colum is divided by comma character.

I am struck with following things. Please advice

1. Remove " "
2. Replace , with spaces between " "
3. Parse the file base on , character

Code:

//SYSIN    DD *                                   
  INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=17),       
             %02=(ENDBEFR=C',',FIXLEN=25),         
             %03=(ENDBEFR=C',',FIXLEN=5),         
             %04=(ENDBEFR=C',',FIXLEN=11),         
             %05=(ENDBEFR=C',',FIXLEN=11)),       
       BUILD=(01:%01,                             
              18:%02,                             
              43:%03,                             
              49:%04,%05)                         
  OPTION COPY                                     
//SORTIN   DD *                                   
AAA,"BBB,AACCC",DDDD,"$259,600.00",EEEE           
BBB,BBB BBB,DDD,"$10",EEE                         
/*                                                 




Output now is
Code:

 COMMAND INPUT ===>                                            SCROLL
********************************* TOP OF DATA ***********************
AAA              "BBB                     AACCC DDDD       "$259     
BBB              BBB BBB                  DDD   "$10"      EEE       


Expected output.
Code:

****** ***************************** Top of Data ******************************
000001 AAA              BBB AACCC                      DDDD  $259      EEEE   
000002 BBB              BBB BBB                        DDD   $10       EEE     


Thanks
Magesh
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose

PostPosted: Mon Oct 27, 2014 1:12 pm    Post subject: Reply with quote

Magesh_J,

Try this JCL. The trick here is to differentiate the records with double quotes around the name. So we use Findrep to modify that to different character and then perform the parsing. If you want to see how the processing is done, run the job with 1 IFTHEN statement at a time and see the results.

Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
AAA,"BBB,AACCC",DDDD,"$259,600.00",EEEE                             
BBB,BBB BBB,DDD,"$10",EEE                                           
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  INREC IFTHEN=(WHEN=INIT,FINDREP=(INOUT=(C'"$',C'##',C'",',C'%%'))),
  IFTHEN=(WHEN=(1,80,SS,EQ,C'"'),                                   
        PARSE=(%01=(ENDBEFR=C',',FIXLEN=17),                         
               %02=(STARTAFT=C'"',ENDBEFR=C'%%',FIXLEN=25),         
               %03=(ENDBEFR=C',',FIXLEN=05),                         
               %04=(STARTAFT=C'#',ENDBEFR=C',',FIXLEN=11),           
               %05=(STARTAFT=C'%%',FIXLEN=11)),                     
  BUILD=(%01,%02,%03,%04,%05)),                                     
  IFTHEN=(WHEN=NONE,                                                 
        PARSE=(%06=(ENDBEFR=C',',FIXLEN=17),                         
               %07=(ENDBEFR=C',',FIXLEN=25),                         
               %08=(ENDBEFR=C',',FIXLEN=05),                         
               %09=(STARTAFT=C'#',ENDBEFR=C'%',FIXLEN=11),           
               %10=(STARTAFT=C'%',FIXLEN=11)),                       
  BUILD=(%06,%07,%08,%09,%10))                                       
//*                                                                 

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Tue Oct 28, 2014 2:03 am    Post subject: Reply with quote

Thank Kolusu,

The challenge we have is, there are number of columns having double codes.

From your solution we tried this.. please advice.

Code:

SORT FIELDS=COPY                                             
INREC IFTHEN=(WHEN=INIT,FINDREP=(INOUT=(C' ',C'|'))),         
IFTHEN=(WHEN=INIT,FINDREP=(INOUT=(C',',C' '))),               
IFTHEN=(WHEN=INIT,                                           
BUILD=(1,381,SQZ=(SHIFT=LEFT,PAIR=QUOTE,MID=C'~'))),         
IFTHEN=(WHEN=INIT,                                           
BUILD=(1,381,SQZ=(SHIFT=LEFT,MID=C','))),                     
IFTHEN=(WHEN=INIT,FINDREP=(INOUT=(C'"',C'')))                 
OUTREC FINDREP=(IN=C'|',OUT=C' ')                             


The Input has some spaces too between commas, so initially we changed it to "|" and We used the PAIR=QUOTE which is excluding comma between double codes, I.e. comma between double code will be there in output.
Changing rest of the comma with ~ symbol, and replaced the double codes with null and replaced pipe symbol back to spaces.

Later we are writing another step to parse data using "~" symbol.

Code:

SORT FIELDS=COPY                             
INREC PARSE=(%01=(ENDBEFR=C'~',FIXLEN=17),   
             %02=(ENDBEFR=C'~',FIXLEN=25),   
             %03=(ENDBEFR=C'~',FIXLEN=5),   
             %04=(ENDBEFR=C'~',FIXLEN=11),
             %05=......,%06 etc  )), 
       BUILD=(01:%01,                       
              18:%02,                       
              43:%03,                       
              49:%04,%05,%06..etc)                       


Currently writing parse statement for each column.

Thanks
Magesh
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose

PostPosted: Tue Oct 28, 2014 10:32 am    Post subject: Reply with quote

Magesh_J,

Why do you need 2 passes of data? you can do it all in a single step. You can have multiple change values for FINDREP.

You can combine the 2 step control cards into a single step like this
Code:

//SYSIN   DD *
  OPTION COPY                                             
  INREC IFTHEN=(WHEN=INIT,FINDREP=(INOUT=(C' ',C'|'))),         
  IFTHEN=(WHEN=INIT,FINDREP=(INOUT=(C',',C' '))),               
  IFTHEN=(WHEN=INIT,                                           
   BUILD=(1,381,SQZ=(SHIFT=LEFT,PAIR=QUOTE,MID=C'~'))),         
  IFTHEN=(WHEN=INIT,                                           
   BUILD=(1,381,SQZ=(SHIFT=LEFT,MID=C','))),                     
  IFTHEN=(WHEN=INIT,FINDREP=(INOUT=(C'"',C'',C'|',C' ')))

  OUTREC PARSE=(%01=(ENDBEFR=C'~',FIXLEN=17),   
                %02=(ENDBEFR=C'~',FIXLEN=25),   
                %03=(ENDBEFR=C'~',FIXLEN=5),   
                %04=(ENDBEFR=C'~',FIXLEN=11),
                %05=......,%06 etc  )),
         BUILD=(01:%01,                       
                18:%02,                       
                43:%03,                       
                49:%04,%05,%06..etc)                 

//*


Btw if you can send me your input file and desired output file as text file, I may try to come up with a different solution.
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Fri Oct 31, 2014 9:24 pm    Post subject: Reply with quote

Thanks Kolusu, for the phenomenal solutions.

Really useful.

Thanks
Magesh
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group