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
 

 

CSV File Creation

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: CSV File Creation
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: 578
Location: USA

PostPosted: Tue Jun 14, 2011 9:26 pm    Post subject:
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: 578
Location: USA

PostPosted: Tue Jun 14, 2011 9:51 pm    Post subject:
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    Post subject: Reply to: CSV File Creation
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    Post subject: Reply to: CSV File Creation
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: 578
Location: USA

PostPosted: Thu Jun 16, 2011 7:47 pm    Post subject:
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    Post subject:
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    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
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm
No new posts Writing a file using online program grvtomar PL/I & Assembler 3 Fri Jun 30, 2017 1:06 pm


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