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

Creating CSV file from Variable recs using ICETOOL / SORT


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Alan Playford

New User


Joined: 22 Aug 2012
Posts: 48
Location: UK

PostPosted: Tue Oct 25, 2022 3:50 pm
Reply with quote

Been tearing my hair out with this one, so hoping somebody can shed light on it?

I have variable records (VB/4096/27998) with the following format:
Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----+----2----+-
0200 $        2015-11-10 #GROUP   NO   NO   NO   NO   NO   200            GENERIC USER ID      #GROUP   10:18:23 2015-11-10
0200 irrcerta 1999-10-20 irrcerta NO   NO   NO   YES  NO   000            CERTAUTH Anchor                        2015-11-11
0200 irrmulti 2000-12-02 irrmulti NO   NO   NO   YES  NO   000            Criteria Anchor
0200 irrsitec 1999-10-20 irrsitec NO   NO   NO   YES  NO   000            SITE Anchor
0200 BPXOINIT 2002-11-13 IBMUSER  NO   NO   NO   NO   NO   180            BPXOINIT             SYS1


I want to extract columns 6-13 (8), 26-33 (8), 75-94 (20), 114-123 (10) and 105-112 (8) into a CSV file, separated by commas.
example:
Code:

$       ,#GROUP  ,GENERIC USER ID     ,2015-11-10,10:18:23
irrcerta,irrcerta,CERTAUTH Anchor     ,2015-11-11,

or better still
Code:

$,#GROUP,GENERIC USER ID,2015-11-10,10:18:23
irrcerta,irrcerta,CERTAUTH Anchor,2015-11-11


I've tried all manner combinations of of INREC, PARSE, OUTREC, OUTFIL but always fall foul of various ICE messages which don't appear to help me much.

Has anybody done a similar thing, and if so could you please furnish me with an example?
Many thanks in advance.
Back to top
View user's profile Send private message
Joerg.Findeisen

Active Member


Joined: 15 Aug 2015
Posts: 940
Location: Bamberg, Germany

PostPosted: Tue Oct 25, 2022 4:38 pm
Reply with quote

Use a semicolon as separator field, not the comma. See snippet as below that should work with the IRRDBU extract dataset, and comes close to your requirement.

Code:
OPTION COPY                                                   
INREC IFTHEN=(WHEN=INIT,                                       
  BUILD=(1,4,10,8,X,30,8,X,79,20,X,117,11,X,108,9)),           
  IFTHEN=(WHEN=INIT,
    BUILD=(1,4,5,60,SQZ=(SHIFT=LEFT,MID=C';',LENGTH=60)))
END
Back to top
View user's profile Send private message
Alan Playford

New User


Joined: 22 Aug 2012
Posts: 48
Location: UK

PostPosted: Tue Oct 25, 2022 7:33 pm
Reply with quote

Joerg,
OK, gives me further results to what I had before (at least RC=0 instead of 16!)
But I need to adjust a little more for what I'm looking to do.
Many thanks, pal!
Back to top
View user's profile Send private message
Alan Playford

New User


Joined: 22 Aug 2012
Posts: 48
Location: UK

PostPosted: Tue Oct 25, 2022 8:00 pm
Reply with quote

Joerg,
Almost there, thanks, but a couple of minor problems -
(1) The third field (75-94 (20)) contians spaces, and these get translated to semi-colons (";") instead of being preserved?
(2) Intermediate fields without values do NOT get translated to a defualt ";"

Any suggestions? (as you've been a boon so far!)
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1650

PostPosted: Tue Oct 25, 2022 8:17 pm
Reply with quote

Joerg.Findeisen wrote:
Use a semicolon as separator field, not the comma. See snippet as below that should work with the IRRDBU extract dataset, and comes close to your requirement.

Code:
OPTION COPY                                                   
INREC IFTHEN=(WHEN=INIT,                                       
  BUILD=(1,4,10,8,X,30,8,X,79,20,X,117,11,X,108,9)),           
  IFTHEN=(WHEN=INIT,
    BUILD=(1,4,5,60,SQZ=(SHIFT=LEFT,MID=C';',LENGTH=60)))
END

I'm afraid it would also convert
Code:
CERTAUTH Anchor
to
Code:
CERTAUTH;Anchor
isn't it?
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1650

PostPosted: Tue Oct 25, 2022 8:42 pm
Reply with quote

Alan Playford wrote:
Been tearing my hair out with this one, so hoping somebody can shed light on it?

I have variable records (VB/4096/27998) with the following format:
Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----+----2----+-
0200 $        2015-11-10 #GROUP   NO   NO   NO   NO   NO   200            GENERIC USER ID      #GROUP   10:18:23 2015-11-10
0200 irrcerta 1999-10-20 irrcerta NO   NO   NO   YES  NO   000            CERTAUTH Anchor                        2015-11-11
0200 irrmulti 2000-12-02 irrmulti NO   NO   NO   YES  NO   000            Criteria Anchor
0200 irrsitec 1999-10-20 irrsitec NO   NO   NO   YES  NO   000            SITE Anchor
0200 BPXOINIT 2002-11-13 IBMUSER  NO   NO   NO   NO   NO   180            BPXOINIT             SYS1


I want to extract columns 6-13 (8), 26-33 (8), 75-94 (20), 114-123 (10) and 105-112 (8) into a CSV file, separated by commas.
example:
Code:

$       ,#GROUP  ,GENERIC USER ID     ,2015-11-10,10:18:23
irrcerta,irrcerta,CERTAUTH Anchor     ,2015-11-11,

or better still
Code:

$,#GROUP,GENERIC USER ID,2015-11-10,10:18:23
irrcerta,irrcerta,CERTAUTH Anchor,2015-11-11


I've tried all manner combinations of of INREC, PARSE, OUTREC, OUTFIL but always fall foul of various ICE messages which don't appear to help me much.

Has anybody done a similar thing, and if so could you please furnish me with an example?
Many thanks in advance.

I usually did it as follows

Code:
. . . . . . 
//SYMNAMES DD  *
RDW,1,4,BI
$NAME,10,8,CH
$GROUP,30,8,CH
$USERID,79,20,CH
$DATE,119,10,CH
$TIME,109,8,CH
//*
. . . . . . 
//SYSIN   DD  *
 OUTREC BUILD=(RDW,
            $NAME,SQZ=(VL),C';',
            $GROUP,SQZ=(VL),C';',
            $USERID,SQZ=(VL,MID=C' '),C';',
            $DATE,SQZ=(VL),C';',
            $TIME,SQZ=(VL),C';')
//*
Back to top
View user's profile Send private message
Alan Playford

New User


Joined: 22 Aug 2012
Posts: 48
Location: UK

PostPosted: Tue Oct 25, 2022 9:23 pm
Reply with quote

Thanks for the suggestion, sergeyken.
An alternative is always good, and I'll try this!
Back to top
View user's profile Send private message
Alan Playford

New User


Joined: 22 Aug 2012
Posts: 48
Location: UK

PostPosted: Tue Oct 25, 2022 9:37 pm
Reply with quote

Sergeyken,
Got it to RC=00 but cannot see the output?
Have you the JCL surrounding that which works, please?
Back to top
View user's profile Send private message
Joerg.Findeisen

Active Member


Joined: 15 Aug 2015
Posts: 940
Location: Bamberg, Germany

PostPosted: Tue Oct 25, 2022 10:08 pm
Reply with quote

Update:
Code:
OPTION COPY                                                     
INREC IFTHEN=(WHEN=INIT,                                         
  BUILD=(1,4,10,8,C',',30,8,C',',79,20,C',',118,10,C',',109,8)),
  IFTHEN=(WHEN=INIT,                                             
    BUILD=(1,4,5,18,23,20,JFY=(SHIFT=RIGHT,LENGTH=20),           
           43,20,SQZ=(SHIFT=LEFT,LENGTH=20))),                   
  IFTHEN=(WHEN=INIT,                                             
    BUILD(1,4,5,18,23,40,JFY=(SHIFT=LEFT,LENGTH=40))),           
  IFTHEN=(WHEN=INIT,                                             
    FINDREP=(INOUT=(C' ',C''),STARTPOS=5,ENDPOS=22,SHIFT=YES))   
END

Output:
Code:
000001 irrcerta,irrcerta,CERTAUTH Anchor,,                   
000002 irrmulti,irrmulti,Criteria Anchor,,                   
000003 irrsitec,irrsitec,SITE Anchor,,                       
000004 AFOPERZ,GROUP,AUTOOPERATOR CONTROL,2021-03-28,13:54:41
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1650

PostPosted: Tue Oct 25, 2022 10:46 pm
Reply with quote

Alan Playford wrote:
Sergeyken,
Got it to RC=00 but cannot see the output?
Have you the JCL surrounding that which works, please?


RTFM: by default all output goes to //SORTOUT DD (and all log goes to //SYSOUT DD)

It's a piece of cake.

P.S.
Of course, also SORT FIELDS=COPY is required under //SYSIN DD!
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1650

PostPosted: Tue Oct 25, 2022 10:50 pm
Reply with quote

Joerg.Findeisen wrote:
Update:
Code:
OPTION COPY                                                     
INREC IFTHEN=(WHEN=INIT,                                         
  BUILD=(1,4,10,8,C',',30,8,C',',79,20,C',',118,10,C',',109,8)),
  IFTHEN=(WHEN=INIT,                                             
    BUILD=(1,4,5,18,23,20,JFY=(SHIFT=RIGHT,LENGTH=20),           
           43,20,SQZ=(SHIFT=LEFT,LENGTH=20))),                   
  IFTHEN=(WHEN=INIT,                                             
    BUILD(1,4,5,18,23,40,JFY=(SHIFT=LEFT,LENGTH=40))),           
  IFTHEN=(WHEN=INIT,                                             
    FINDREP=(INOUT=(C' ',C''),STARTPOS=5,ENDPOS=22,SHIFT=YES))   
END

Output:
Code:
000001 irrcerta,irrcerta,CERTAUTH Anchor,,                   
000002 irrmulti,irrmulti,Criteria Anchor,,                   
000003 irrsitec,irrsitec,SITE Anchor,,                       
000004 AFOPERZ,GROUP,AUTOOPERATOR CONTROL,2021-03-28,13:54:41


Too messy...

But nowadays managers do like it: the harder to understand, - the better! icon_pai.gif
Back to top
View user's profile Send private message
Joerg.Findeisen

Active Member


Joined: 15 Aug 2015
Posts: 940
Location: Bamberg, Germany

PostPosted: Wed Oct 26, 2022 1:37 am
Reply with quote

sergeyken wrote:
Too messy...

That is due to the fact that DFSORT does not support SQZ=(VL)
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 File Aid Batch IBM Tools 7
No new posts To replace jobname in a file with ano... SYNCSORT 12
No new posts Converting fixed length file to excel... IBM Tools 7
No new posts Extract record using 2 input file int... JCL & VSAM 2
No new posts Masking variable size field - min 10 ... DFSORT/ICETOOL 4
Search our Forums:

Back to Top