OK, I worked on this offline with Sri Kolusu, and it works great. Here are
the details.
For a file with seven column names interspersed in multiple header fields
like this:
5----+----6----+----7----+----8----+----9----+----0----+----1----+--
...003,FIELD,*EMP NO*,N,005,00,N,FIELD,*ACT CD*,C,0001,00,N,FIELD,*ACT STA*
,C...
...and multiple Data records with an introductory field "DATA" that needed
to be skipped:
----+----1----+----2----+----3----+----4----+----5
DATA,1,"A","S",3000.00,25000.00,23000.00,78000.00
DATA,17,"A","S",1000.00,10000.00,15000.00,26000.00
DATA,24,"A","S",1000.00,8500.00,11500.00,26000.00
DATA,30,"A","S",975.00,9000.00,5500.00,25350.00
...and a Trailer record to be dropped:
----+----1----+
TRAILER,0202
Here is the resultant SYSIN:
//SYSIN DD *
OPTION COPY,VLSCMP
OMIT COND=(5,7,CH,EQ,C'TRAILER')
INREC IFTHEN=(WHEN=(5,5,CH,EQ,C'DATA,'),BUILD=(1,4,10)),
IFTHEN=(WHEN=(5,6,CH,EQ,C'HEADER'),
PARSE=(%01=(STARTAFT=C'FIELD,',ENDBEFR=C',',FIXLEN=22),
%02=(STARTAFT=C'FIELD,',ENDBEFR=C',',FIXLEN=22),
%03=(STARTAFT=C'FIELD,',ENDBEFR=C',',FIXLEN=22),
%04=(STARTAFT=C'FIELD,',ENDBEFR=C',',FIXLEN=22),
%05=(STARTAFT=C'FIELD,',ENDBEFR=C',',FIXLEN=22),
%06=(STARTAFT=C'FIELD,',ENDBEFR=C',',FIXLEN=22),
%07=(STARTAFT=C'FIELD,',ENDBEFR=C',',FIXLEN=22)),
BUILD=(1,10,
%01,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"'),
%02,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"'),
%03,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"'),
%04,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"'),
%05,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"'),
%06,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"'),
%07,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),HIT=NEXT),
IFTHEN=(WHEN=(5,6,CH,EQ,C'HEADER'),
BUILD=(1,4,11,154,SQZ=(SHIFT=LEFT,PAIR=QUOTE,MID=C',')))
/*
The changes needed for other files would be if there are other than seven
column headers, you need to modify the PARSE statement and its
corresponding BUILD statement for each header, and if each header can be
more than 22 chars long, you need to change the FIXLEN statement
accordingly.
This command works great whether the column headers are contiguous or have
spaces:
"ACTIVITY-CD" and "ACT CD" both work fine. Note that if there are multiple
spaces within a title, those spaces are preserved. For example "ACT CD"
stays as is.
Many thanks to Sri Kolusu at IBM for helping to work this out!
Billy
On Wed, May 7, 2014 at 1:36 PM, retired mainframer <retired-mainfra...@q.com
> wrote:
> You can use the OMIT feature to drop the trailer record
>
> You can use the IFTHEN feature to recognize whether the current record is a
> header record or data record.
>
> For the data records, you can simply copy starting at column 6. Because
> the
> records are VB, you specify column 10 to account for the RDW.
>
> You can use the PARSE feature to extract the 7 column titles from the
> header
> record. Using comma as your delimiter, find the word FIELD; extract the
> next word; repeat 6 more times. Use the 7 extracted words to build your
> new
> header.
>
> :>: -----Original Message-----
> :>: From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU]
> On
> :>: Behalf Of Bill Ashton
> :>: Sent: Wednesday, May 07, 2014 9:28 AM
> :>: To: IBM-MAIN@LISTSERV.UA.EDU
> :>: Subject: Use SORT to manipulate a CSV?
> :>:
> :>: Hi friends...you have been helpful to in so many areas so far, and now
> I
> :>: have a question about using SORT.
> :>:
> :>: I have an application that creates a CSV file (sort of) on the
> mainframe.
> :>: The data rows start with a field "DATA" before the actual data fields,
> :>: and
> :>: there is a Header row that has a lot more than the column names, and a
> :>: single trailer row I want to drop.
> :>:
> :>: I have never used SORT to work with the concept of fields before, and
> am
> :>: not sure if this can be done, but would appreciate if someone might
> lend
> :>: a
> :>: hand. The file is a VB file with LRECL=0 and BLKSIZE=4096. Here is what
> :>: the
> :>: data records look like:
> :>:
> :>: DATA,1,"A","S",3000.00,25000.00,23000.00,78000.00
> :>: DATA,17,"A","S",1000.00,10000.00,15000.00,26000.00
> :>: DATA,24,"A","S",1000.00,8500.00,11500.00,26000.00
> :>: DATA,30,"A","S",975.00,9000.00,5500.00,25350.00
> :>: DATA,26,"A","S",950.00,7900.00,11000.00,24700.00
> :>: DATA,31,"A","S",925.00,7500.00,6000.00,24050.00
> :>:
> :>: I want to drop the first field and comma, so the output should look
> like
> :>: this:
> :>: 1,"A","S",3000.00,25000.00,23000.00,78000.00
> :>: 17,"A","S",1000.00,10000.00,15000.00,26000.00
> :>: 24,"A","S",1000.00,8500.00,11500.00,26000.00
> :>: 30,"A","S",975.00,9000.00,5500.00,25350.00
> :>: 26,"A","S",950.00,7900.00,11000.00,24700.00
> :>: 31,"A","S",925.00,7500.00,6000.00,24050.00
> :>:
> :>: Then, the 1st record header looks like this:
> :>:
> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
> :>: --+----8----+----9----+----0
> :>:
> HEADER,MYVAR,DETAIL,050714,091407,RECORD,PAYROLL,PAY,001,FIELD,NUMBER,N,
> :>: 005,00,N,FIELD,ACTIVITY-CODE...
> :>: (this goes on for the 7 column headers. It appears the field contains
> :>: "FIELD" just before the column header
> :>: (FIELD,NUMBER,...FIELD,ACTIVITY-CODE,...FIELD,ACTIVITY-STATUS,...), and
> :>: the
> :>: first "FIELD" field is the 9th field in the record.
> :>:
> :>: I would really like this header to look like this (I don't think I need
> :>: to
> :>: add double-quotes, but am not sure how to do that, either!):
> :>: NUMBER,ACTIVITY-CODE,ACTIVITY-STATUS,...
> :>:
> :>: Finally, the trailer looks like this:
> :>: TRAILER,0202
> :>:
> :>: and I want to just drop this record.
>
> ----------------------------------------------------------------------
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>
--
Thank you and best regards,
*Billy Ashton*
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
