Hi, Was busy and couldn't look at this right away. There are a few monkey wrenches in processing the data. You have some columns which had spaces in between the data and the column attributes having comma's added complications to parsing the data as a comma separator. Either way here is a job that would give you the desired results. I added comments so that you can follow and easy to modify.
I am attaching the solution as a text file . Let me know if you have any have further questions. Thanks, Kolusu DFSORT Development IBM Corporation ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [email protected] with the message: INFO IBM-MAIN
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SYMNOUT DD SYSOUT=*
//INA DD DISP=SHR,DSN=Your.input.VB.csv.file
//INB DD DISP=SHR,DSN=Same.input.VB.csv.file
/*
//SORTOUT DD DSN=Your.output.vb.csv.file,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(X,Y),RLSE)
/*
//SYMNAMES DD *
FLD-MAX-LENGTH,33 # Max len of field including quotes and separator
ARRAY-COUNT,20 # MAX NUMBER OF COLUMNS
** Note : we assumed that we have a max of 20 columns, so we defined
** 20 flag indicators and 20 field-values. If you more fields
** then make sure you define additional QTInn and FLDnn
** symbols also
**
TOT-FLD-WIDTH,660 # FLD-MAX-LENGTH X ARRAY-COUNT (33 X 20 = 660)
** Note : The REFORMAT statment does not allow symbols, so if
** change the tot-fld-width, make sure you change it on the
** reformat statement too.
**
JOIN-IND,01,01,CH
QTI01,*,01,CH
QTI02,*,01,CH
QTI03,*,01,CH
QTI04,*,01,CH
QTI05,*,01,CH
QTI06,*,01,CH
QTI07,*,01,CH
QTI08,*,01,CH
QTI09,*,01,CH
QTI10,*,01,CH
QTI11,*,01,CH
QTI12,*,01,CH
QTI13,*,01,CH
QTI14,*,01,CH
QTI15,*,01,CH
QTI16,*,01,CH
QTI17,*,01,CH
QTI18,*,01,CH
QTI19,*,01,CH
QTI20,*,01,CH
ORIG-DATA-BEGIN,*
FLD01,*,33,CH
FLD02,*,33,CH
FLD03,*,33,CH
FLD04,*,33,CH
FLD05,*,33,CH
FLD06,*,33,CH
FLD07,*,33,CH
FLD08,*,33,CH
FLD09,*,33,CH
FLD10,*,33,CH
FLD11,*,33,CH
FLD12,*,33,CH
FLD13,*,33,CH
FLD14,*,33,CH
FLD15,*,33,CH
FLD16,*,33,CH
FLD17,*,33,CH
FLD18,*,33,CH
FLD19,*,33,CH
FLD20,*,33,CH
ORIG-DATA-END,*
/*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(5,8,A),SORTED,NOSEQCK
JOINKEYS F2=INB,FIELDS=(5,8,A),SORTED,NOSEQCK
JOIN UNPAIRED,F1
REFORMAT FIELDS=(?, # Match-ind
F2:013,020, # Column indicators
F1:13,660) # parsed data
** Add quotes checking the column flag. we have 20 columns so we
** have 20 IFTHEN statements to validate them.
INREC IFTHEN=(WHEN=(QTI01,EQ,C'Q'),
OVERLAY=(FLD01:FLD01,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI02,EQ,C'Q'),
OVERLAY=(FLD02:FLD02,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI03,EQ,C'Q'),
OVERLAY=(FLD03:FLD03,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI04,EQ,C'Q'),
OVERLAY=(FLD04:FLD04,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI05,EQ,C'Q'),
OVERLAY=(FLD05:FLD05,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI06,EQ,C'Q'),
OVERLAY=(FLD06:FLD06,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI07,EQ,C'Q'),
OVERLAY=(FLD07:FLD07,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI08,EQ,C'Q'),
OVERLAY=(FLD08:FLD08,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI09,EQ,C'Q'),
OVERLAY=(FLD09:FLD09,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI10,EQ,C'Q'),
OVERLAY=(FLD10:FLD10,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI11,EQ,C'Q'),
OVERLAY=(FLD11:FLD11,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI12,EQ,C'Q'),
OVERLAY=(FLD12:FLD12,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI13,EQ,C'Q'),
OVERLAY=(FLD13:FLD13,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI14,EQ,C'Q'),
OVERLAY=(FLD14:FLD14,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI15,EQ,C'Q'),
OVERLAY=(FLD15:FLD15,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI16,EQ,C'Q'),
OVERLAY=(FLD16:FLD16,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI17,EQ,C'Q'),
OVERLAY=(FLD17:FLD17,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI18,EQ,C'Q'),
OVERLAY=(FLD18:FLD18,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI19,EQ,C'Q'),
OVERLAY=(FLD19:FLD19,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')),
HIT=NEXT),
IFTHEN=(WHEN=(QTI20,EQ,C'Q'),
OVERLAY=(FLD20:FLD20,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"')))
** Now we have added the quotes for char columns and we now need
** to squeeze out the spaces in between for the parsed data
OUTREC IFTHEN=(WHEN=(JOIN-IND,EQ,C'B'),
BUILD=(ORIG-DATA-BEGIN,TOT-FLD-WIDTH,
SQZ=(SHIFT=LEFT,PAIR=QUOTE,MID=C','))),
** Build the header records as is as
IFTHEN=(WHEN=(JOIN-IND,EQ,C'1'),
BUILD=(ORIG-DATA-BEGIN,TOT-FLD-WIDTH))
** Now create the VB file removing the trailing spaces
OUTFIL FTOV,VLTRIM=C' '
/*
//JNF1CNTL DD *
** This task will only parse the actual data contents which
** begin from record # 3. we first number the records using
** SEQNUM and use that to parse out the field data
INREC IFTHEN=(WHEN=INIT,
BUILD=(001,004, # Rdw
SEQNUM,8,ZD, # Record number
05)), # Original data
** Now we numbered the records so parse the data and make
** every record number as 3 so that it can match with column
** attributes which was built in JNF2
IFTHEN=(WHEN=(05,08,ZD,GT,2),
PARSE=(%=(FIXLEN=8),
%01=(ENDBEFR=C',',
FIXLEN=FLD-MAX-LENGTH,
REPEAT=ARRAY-COUNT)),
BUILD=(01,04, # Rdw
C'00000003', # Match-key
%01,%02,%03,%04,%05,
%06,%07,%08,%09,%10,
%11,%12,%13,%14,%15,
%16,%17,%18,%19,%20))
//JNF2CNTL DD *
** This task will only read record num # 2 which has the column
** attributes. Since some of the numerical columns has comma in
** between we have to separtor (comma) to something else so that
** we can parse out the fields. we use FINDREP to do that
OPTION SKIPREC=1,STOPAFT=1
INREC IFTHEN=(WHEN=INIT,
FINDREP=(INOUT=(C'N.N.,',C'@',
C'NOT NULL,',C'@',
C'N.N.',C'@'))),
** Now we can parse the columns and then we just need 1 byte ind
** to denote if the column is a char column. so we use CHANGE
** to set the flag
IFTHEN=(WHEN=INIT,
PARSE=(%01=(ENDBEFR=C'@',
FIXLEN=4,
REPEAT=ARRAY-COUNT)),
BUILD=(01,04, # Rdw
C'00000003', # Match-key
%01,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%02,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%03,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%04,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%05,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%06,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%07,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%08,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%09,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%10,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%11,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%12,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%13,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%14,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%15,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%16,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%17,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%18,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%19,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' '),
%20,CHANGE=(01,C'CHAR',C'Q'),NOMATCH=(C' ')))
/*
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [email protected] with the message: INFO IBM-MAIN
