Parsing dates from CSV



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Parsing dates from CSV

Postby 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
MitchellSTL
 
Posts: 27
Joined: Wed Feb 16, 2011 8:57 am
Has thanked: 1 time
Been thanked: 0 time

Re: Parsing dates from CSV

 

Re: Parsing dates from CSV

Postby 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):

//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       
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Parsing dates from CSV

Postby 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!
MitchellSTL
 
Posts: 27
Joined: Wed Feb 16, 2011 8:57 am
Has thanked: 1 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post