Page 1 of 1

Parsing dates from CSV

PostPosted: Wed Sep 21, 2011 9:18 pm
by MitchellSTL
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

Re: Parsing dates from CSV

PostPosted: Wed Sep 21, 2011 10:41 pm
by Frank Yaeger
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):

//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:

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       

Re: Parsing dates from CSV

PostPosted: Thu Sep 22, 2011 3:37 am
by MitchellSTL
Thanks Frank!

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

Thanks again!