IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

CSV File Creation
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
jegan_selvan

New User


Joined: 23 Oct 2006
Posts: 24
Location: India
PostPosted: Tue Jun 14, 2011 2:09 pm
 Reply with quote
Hi All,

Could you Please help on this requirement in DFSORT.

File Characteristics :

Input : FB with 200 Record Length

Code:

ID|NO| FULL NAME |

AAAAAAAA| 121 |SAM,KRISTIEN X |
BBBBBBBBB |165 |VISHALI ,SELVAN |
CCCCCCCCC |342 |KANNAN,SENTIL VARMAN |



Output File : FB with 200 Record Length

Code:

"ID","ID","FULL NAME","LAST NAME","FIRST NAME","MIDDLE NAME"

"AAAAAAAA" ,"AAAAAAAA","SAM,KRISTIEN X ","KRISTIEN","SAM","X"
"BBBBBBBBB ","BBBBBBBBB ","VISHALI ,SELVAN ","SELVAN","VISHALI"
"CCCCCCCCC ","CCCCCCCCC ","KANNAN,SENTIL VARMAN ","SENTIL","KANNAN","VARMAN"
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Tue Jun 14, 2011 9:26 pm
 Reply with quote
jegan_selvan,
Your expected output for HEADER and detail records doesn't match for trailing space. For example, for the detail record your expected output shows trailing space "BBBBBBBBB " but the same is not true for header"FULL NAME".

Making some assumption about output field length (you can adjust fields to match your expected output of FB200). Here is one way to do it. See if this helps..
Code:
//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                         
ID|NO| FULL NAME |                                                     
AAAAAAAA| 121 |SAM,KRISTIEN X |                                         
BBBBBBBBB |165 |VISHALI ,SELVAN |                                       
CCCCCCCCC |342 |KANNAN,SENTIL VARMAN |                                 
/*                                                                     
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                         
  INREC IFTHEN=(WHEN=INIT,                                             
        PARSE=(%01=(ENDBEFR=C'|',FIXLEN=10),                           
               %02=(ENDBEFR=C'|',FIXLEN=05),                           
               %03=(ENDBEFR=C'|',FIXLEN=40)),                           
        BUILD=(%01,                                                     
               %02,                                                     
               %03,                                                     
               %03,                                                     
               %03,                                                     
               %03)),                                                   
        IFTHEN=(WHEN=INIT,                                             
        PARSE=(%04=(ABSPOS=56,ENDBEFR=C',',FIXLEN=20),                 
               %05=(ENDBEFR=C' ',FIXLEN=20),                           
               %06=(ENDBEFR=C'|',FIXLEN=20)),                           
        BUILD=(%01,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=13),   
               %01,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=13),   
               %03,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=43),   
               %05,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=23),   
               %04,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=23),   
               %06,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"',LENGTH=022),   
               SEQNUM,8,ZD)),                                           
  IFTHEN=(WHEN=(138,8,ZD,EQ,1),                                         
       OVERLAY=(070:C'"LAST NAME",',                                   
                093:C'"FIRST NAME",',                                   
                116:C'"MIDDLE NAME"'))                                 
  SORT FIELDS=COPY                                                     
  OUTREC IFTHEN=(WHEN=INIT,                                             
         OVERLAY=(1,137,SQZ=(SHIFT=LEFT,PAIR=QUOTE))),                 
         IFTHEN=(WHEN=INIT,                                 
         FINDREP=(IN=C',""',OUT=C''))                       
  OUTFIL BUILD=(1,137)                                     
/*                                                         
//*                                                         

OUTPUT
Code:
"ID","ID","FULL NAME","LAST NAME","FIRST NAME","MIDDLE NAME"               
"AAAAAAAA","AAAAAAAA","SAM,KRISTIEN X","KRISTIEN","SAM","X"               
"BBBBBBBBB","BBBBBBBBB","VISHALI ,SELVAN","SELVAN","VISHALI"               
"CCCCCCCCC","CCCCCCCCC","KANNAN,SENTIL VARMAN","SENTIL","KANNAN","VARMAN" 

Thanks,
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Tue Jun 14, 2011 9:51 pm
 Reply with quote
Amazing...I just realized there is no need to code multiple %03 in the first INREC PARSE... I didn't know that earlier...

Below would work for the same output.
Code:

//SYSIN    DD *                                                       
  INREC IFTHEN=(WHEN=INIT,                                             
        PARSE=(%01=(ENDBEFR=C'|',FIXLEN=10),                           
               %02=(ENDBEFR=C'|',FIXLEN=05),                           
               %03=(ENDBEFR=C'|',FIXLEN=40)),                         
        BUILD=(%01,                                                   
               %02,                                                   
               %03)),                                                 
        IFTHEN=(WHEN=INIT,                                             
        PARSE=(%04=(ABSPOS=16,ENDBEFR=C',',FIXLEN=20),                 
               %05=(ENDBEFR=C' ',FIXLEN=20),                           
               %06=(ENDBEFR=C'|',FIXLEN=20)),                         
        BUILD=(%01,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=13),   
               %01,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=13),   
               %03,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=43),   
               %05,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=23),   
               %04,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=23),   
               %06,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"',LENGTH=022),   
               SEQNUM,8,ZD)),                                         
  IFTHEN=(WHEN=(138,8,ZD,EQ,1),                                       
       OVERLAY=(070:C'"LAST NAME",',                                   
                093:C'"FIRST NAME",',                                 
                116:C'"MIDDLE NAME"'))                                 
  SORT FIELDS=COPY                                                     
  OUTREC IFTHEN=(WHEN=INIT,                                           
         OVERLAY=(1,137,SQZ=(SHIFT=LEFT,PAIR=QUOTE))),                 
         IFTHEN=(WHEN=INIT,                                           
         FINDREP=(IN=C',""',OUT=C''))                                 
  OUTFIL BUILD=(1,137)                                                 
/*                                                                     
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Jun 15, 2011 1:13 am
 Reply with quote
jegan_selvan,

The following DFSORT JCL will give you the desired results. I assumed you also have the header record(1st record) in the file. If you don't have that header just remove the ,skiprec parm after copy parm

Code:

//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                       
ID|NO| FULL NAME |                                                     
AAAAAAAA| 121 |SAM,KRISTIEN X |                                       
BBBBBBBBB |165 |VISHALI ,SELVAN |                                     
CCCCCCCCC |342 |KANNAN,SENTIL VARMAN |                                 
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
  OPTION COPY,SKIPREC=1                                               
  INREC IFTHEN=(WHEN=INIT,                                             
        PARSE=(%01=(ENDBEFR=C'|',FIXLEN=10),                           
               %=(ENDAT=C'|'),                                         
               %02=(ENDBEFR=C',',FIXLEN=36),                           
               %03=(ENDBEFR=C' ',FIXLEN=36),                           
               %04=(ENDBEFR=C'|',FIXLEN=10)),                         
        BUILD=(%01,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=12),   
               %01,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=12),   
               C'@',%02,C',',%03,C'$',%04,C'@,',                       
               %03,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=38),   
               %02,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=38),   
               %04,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"',LENGTH=12))), 
                                                                       
  IFTHEN=(WHEN=INIT,OVERLAY=(1,200,SQZ=(SHIFT=LEFT,PAIR=QUOTE))),     
  IFTHEN=(WHEN=INIT,FINDREP=(INOUT=(C'@',C'"',C'$',C' ',C',"" ',C' ')))
                                                                       
  OUTFIL REMOVECC,BUILD=(1,200),                                       
  HEADER1=('"ID","ID","FULL NAME","LAST NAME",',                       
           '"FIRST NAME","MIDDLE NAME"')                               
//*


The output of this is
Code:

"ID","ID","FULL NAME","LAST NAME","FIRST NAME","MIDDLE NAME"             
"AAAAAAAA","AAAAAAAA","SAM,KRISTIEN X","KRISTIEN","SAM","X"               
"BBBBBBBBB","BBBBBBBBB","VISHALI,SELVAN ","SELVAN","VISHALI"             
"CCCCCCCCC","CCCCCCCCC","KANNAN,SENTIL VARMAN","SENTIL","KANNAN","VARMAN"
Back to top
View user's profile Send private message
jegan_selvan

New User


Joined: 23 Oct 2006
Posts: 24
Location: India
PostPosted: Thu Jun 16, 2011 3:04 pm
 Reply with quote
Hi All,

Thanks for your reply. It helped me a lot to solve the issue. But i have one small change in the requirement.
Actually the full name is not ending with space for all the records.
Code:

AAAAAAAA| 121 |SAM,KRISTIEN X |
BBBBBBBBB |165 |VISHALI ,SELVAN |
CCCCCCCCC |342 |KANNAN,SENTIL VARMAN |
DDDDDDDD|341|JAYAN,VARMAN|
EEEEEEEEEE|364|NEELAN,KUNAL|


The Record 4 and 5 is not having spaces after their last name.
So these records are not formatting properly.

Output is coming like this


Code:

********************************* TOP OF DATA ***************************
"AAAAAAAA","AAAAAAAA","SAM,KRISTIEN X","KRISTIEN","SAM","X"             
"BBBBBBBBB","BBBBBBBBB","VISHALI,SELVAN ","SELVAN","VISHALI"             
"CCCCCCCCC","CCCCCCCCC","KANNAN,SENTIL VARMAN","SENTIL","KANNAN","VARMAN"
"DDDDDDDD","DDDDDDDD","JAYAN,VARMAN|2011| ","VARMAN|2011|","JAYAN"       
"EEEEEEEEEE""EEEEEEEEEE""NEELAN,KUNAL|2011| ","KUNAL|2011|","NEELAN"     


Please help me to solve this issue.

Regards,

Jegan S
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Thu Jun 16, 2011 7:47 pm
 Reply with quote
jegan_selvan,
Did you try both the jobs with appropriate input data (with and without header)?

Thanks,
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Jun 16, 2011 9:51 pm
 Reply with quote
jegan_selvan,

well you haven't explained the rules clearly. Looks you have changed the control cards to include additional data as your output shows 2011 which is not even present in the input. Please provide detailed/complete information on what you're trying to accomplish. Do not make people guess what you mean. This will give you a much better chance of getting a good answer to your question.

jegan_selvan wrote:
The Record 4 and 5 is not having spaces after their last name. So these records are not formatting properly.


It is a simple change . change the following lines in my post

Code:

 %03=(ENDBEFR=C' ',FIXLEN=36),
 %04=(ENDBEFR=C'|',FIXLEN=10)), 


to

Code:

 %03=(ENDBEFR=C' ',ENDBEFR=C'|',FIXLEN=36),
 %04=(ENDBEFR=C'|',ENDBEFR=C' ',FIXLEN=10)),
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

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 0
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 8
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us