IBM Mainframe Forum

by **MitchellSTL** » Wed Sep 21, 2011 9:18 pm

One of the fields that I'm parsing from a CSV file is an excel date format. I need the date in a DB2 date format. I'm interested in the date field being reformatted. And more specifically, I don't need the time portion of the timestamp. The date format looks like this:

example input:
"data1","12/1/2010 1:00 PM" ,"moredata1"
"data2","8/5/2011 1:23 AM","moredata2"
"data3","11/15/2011 12:59 PM","moredata3"

desired output:
data1 2010-12-01 moredata1
data2 2011-08-05 moredata2
data3 2011-11-15 moredata2

Any help is appreciated. TIA

by **Frank Yaeger** » Wed Sep 21, 2011 10:41 pm

You can use a DFSORT job like the following to do what you asked for (I added a couple more records with other possible variations):

Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
"data1","12/1/2010 1:00 PM","moredata1"
"data2","8/5/2011 1:23 AM","moredata2"
"data3","11/15/2011 12:59 PM","moredata3"
"data123","1/5/2011 12:59 PM","moredata456"
"d8","1/12/2011 12:59 PM","data8"
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC PARSE=(%01=(ABSPOS=2,ENDBEFR=C'","',FIXLEN=10),
%02=(ENDBEFR=C'/',FIXLEN=2),
%03=(ENDBEFR=C'/',FIXLEN=2),
%04=(ENDBEFR=C' ',FIXLEN=4),
%=(ENDBEFR=C'","'),
%05=(ENDBEFR=C'"',FIXLEN=10)),
BUILD=(%01,%04,C'/',%02,UFF,EDIT=(TT),C'/',
%03,UFF,EDIT=(TT),X,%05)
/*

SORTOUT will have:

Select all

data1 2010/12/01 moredata1
data2 2011/08/05 moredata2
data3 2011/11/15 moredata3
data123 2011/01/05 moredata45
d8 2011/01/12 data8

by **MitchellSTL** » Thu Sep 22, 2011 3:37 am

Thanks Frank!

I must have had a mental block -- once I saw your solution, the "light" turned on.

Thanks again!

IBM Mainframe Forum

Parsing dates from CSV

Parsing dates from CSV

Re: Parsing dates from CSV

Re: Parsing dates from CSV