Parsing Excel CSV file with enclosed quotes
Posted: Wed Feb 16, 2011 9:19 am
Input record format: Variable
Input record length: 256
Input record organizations: PS
The CSV input file layout is:
“ID #â€, “â€, “Acct #â€,â€Timestampâ€,â€Free form text #1â€,â€Free form text #2â€,â€Timestampâ€
Field #1 – ID #; Most likely (always?) 15 positions
Field #2 – Not used; Always empty
Field #3 – Not used; Acct #
Field #4 – Not used; Timestamp #1
Field #5 – Not used; Free form text #1
Field #6 – Free form text #2
Field #7 – Timestamp #2 (format: 2011/02/13 07:06:25)
Output format:
Field #1 – 15 positions
Field #7 – 20 positions; Timestamp #2
Field #6 – 100 positions; Free form text #2
I was using the following SYSIN from another JCL that we use to convert VB to FB record when parsing fixed CSV files. This is the SYSIN minus the last line which is the OUTREC.
TIA
Input record length: 256
Input record organizations: PS
The CSV input file layout is:
“ID #â€, “â€, “Acct #â€,â€Timestampâ€,â€Free form text #1â€,â€Free form text #2â€,â€Timestampâ€
Field #1 – ID #; Most likely (always?) 15 positions
Field #2 – Not used; Always empty
Field #3 – Not used; Acct #
Field #4 – Not used; Timestamp #1
Field #5 – Not used; Free form text #1
Field #6 – Free form text #2
Field #7 – Timestamp #2 (format: 2011/02/13 07:06:25)
Output format:
Field #1 – 15 positions
Field #7 – 20 positions; Timestamp #2
Field #6 – 100 positions; Free form text #2
I was using the following SYSIN from another JCL that we use to convert VB to FB record when parsing fixed CSV files. This is the SYSIN minus the last line which is the OUTREC.
//SYSIN DD *
OPTION VLSHRT
SORT FIELDS=(5,1,CH,A)
INCLUDE COND=(5,4,CH,EQ,C'"ID #')
OUTFIL FNAMES=REFORMAT,
CONVERT,
OUTREC .....
OPTION VLSHRT
SORT FIELDS=(5,1,CH,A)
INCLUDE COND=(5,4,CH,EQ,C'"ID #')
OUTFIL FNAMES=REFORMAT,
CONVERT,
OUTREC .....
TIA