I couldn't resist. Here's a REXX solution. Should work for any layout which
is defined in the manner of the original request, although my test data
only has the header in the first record.
On Thu, Mar 24, 2022 at 7:47 AM Don Johnson <
[email protected]> wrote:
> This is a post now to the listserv, instead of the Google group. Sorry for
> the duplication!
>
> Hi, I have a comma-delimited extract from a file that has numeric and
> character fields, and I would like to turn it into a true CSV file by
> making the character fields quoted.
>
> I have a 2-line header (column names, and column types) which indicates
> which are CHAR fields, but cannot figure out how to capture the information
> from the header to apply to the actual data lines.
>
> For example, I have this in my file:
> ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
>
> CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2) N.N.
> A10000,CARPETED RUBBER
> MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
> A10001,CARPETED RUBBER
> MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20
> A10002,PERSONALIZED VINYL
> MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03
> A10003,4-PIECE CARPET MAT SET (BLUE),MAT
> SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03
> A10004,SPLASH
> GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03
> A10005,SPLASH
> GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03
> A10006,MONOGRAMMED SPLASH
> GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20
>
> and want the output to look like this:
> "A10000","CARPETED RUBBER
> MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
> "A10001","CARPETED RUBBER
> MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20
> "A10002","PERSONALIZED VINYL
> MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03
> "A10003","4-PIECE CARPET MAT SET (BLUE)","MAT
> SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03
> "A10004","SPLASH
> GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03
>
> "A10005","SPLASH
> GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
>
> "A10006","MONOGRAMMED SPLASH
> GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20
>
> Is there a way to see which column type contains CHAR( -- each of the
> types is column separated -- and then be able to apply quotes to that
> particular output field? I am not sure about this, but hope there is an
> answer here.
>
> Thank you for your help!
> Don Johnson
> Sr. Principal Support Engineer | MSD - Datacom product family
> Broadcom Software
>
> ----------------------------------------------------------------------
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to [email protected] with the message: INFO IBM-MAIN
>
--
Wayne V. Bickerdike
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [email protected] with the message: INFO IBM-MAIN
/* REXX */
/* MODIFY A COMMA DELIMITED FILE USING FILE TAILORING */
/* INITIAL ROUTINE TO PARSE THE HEADER */
SAY 'ENTER LISTDS'
PULL LISTDS
SAY 'ENTER OFIL '
PULL OFIL
"ALLOC F(IFILE) DA('"LISTDS"') SHR REUSE "
/* Read Input file */
"EXECIO * DISKR IFILE (STEM REC. FINIS "
"FREE F(IFILE) "
F. = ''
F.0 = 0
Member = Strip(REC.1)
/* The header record defines the attributes of the columns in the next
records */
/* Example
ID,NAME,ADDR,SUBURB,ZIP,SOCSEC DEC(5,0),CHAR(20),CHAR(20),CHAR(20),DEC(4),DEC(10
*/
K = 0
Do I = 1 To Length(REC.1)
If Substr(REC.1,I,3) = "CHA" Then Do
K = K + 1
F.K = '"&F'||K||'",'
End
Else If Substr(REC.1,I,3) = "DEC" Then Do
K = K + 1
F.K = '&F'||K||','
End
End
L = Length(F.K)-1 /* Take off one byte */
F.K = Substr(F.K,1,L) /* Remove trailing Comma */
SLIB = ''
Ptail = ''
Do J = 1 to K
SLIB = SLIB || F.J
Ptail = Ptail ' F'||J||"','"
End
Pstmt = 'PARSE VALUE MEMBER WITH ' Ptail
/* Allocate a generated PDS file name */
"ALLOC F(FILEIN) DA('BDB204.SKEL.SOURCE(CAFILE)') SHR REUSE"
/* Assign the contents */
R1.1 = SLIB
/* Write and close the file */
"EXECIO 1 DISKW FILEIN (STEM R1. FINIS)"
"ISPEXEC LIBDEF ISPSLIB DATASET ID('BDB204.SKEL.SOURCE')"
CALL AllocPDS(ofil 10 10 300 CYL)
ADDRESS ISPEXEC
Do k = 2 to REC.0
Member = Strip(REC.k)
Omem = 'CAFILE'
INTERPRET Pstmt
"ISPEXEC LIBDEF ISPFILE DATASET ID('"ofil"')"
ADDRESS ISPEXEC 'FTINCL ' CAFILE
End
ADDRESS ISPEXEC 'FTCLOSE NAME('omem') '
"ISPEXEC EDIT DATASET ('"ofil"') MACRO(NONE) "
Exit
Allocpds:
ARG iparm
parse value iparm with ipds prim sec dir cyl
/*********************************************************************/
/* Allocate PDS for file tailoring */
/*********************************************************************/
ADDRESS TSO
tFIL = "'"||IPDS||"'"
IF SYSDSN(tFIL) = 'DATASET NOT FOUND' THEN DO
" ALLOC DA("tFIL") F(NEWFILE) ",
"DIR("dir") SPACE("PRIM","SEC") "CYL,
"RECFM(V B) LRECL(255) BLKSIZE(0)"
" FREE F(NEWFILE) "
END
RETURN
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [email protected] with the message: INFO IBM-MAIN
