Thanks everyone!

Being a cautious type, I read it as alpha and parsed the date and time

NUMERIC date(ADATE10).

COMPUTE date = DATE.MDY(NUMBER(CHAR.SUBSTR(LTRIM(RTRIM(date_time)),6,2),f2.0),NUMBER(CHAR.S UBSTR(LTRIM(RTRIM(date_time)), 9,2), f2.0), NUMBER(CHAR.SUBSTR(LTRIM(RTRIM(date_time)),1,4), f4.0)).

NUMERIC time(TIME12.3).

COMPUTE time = TIME.HMS(NUMBER(CHAR.SUBSTR(LTRIM(RTRIM(date_time)),12,2),F2.0),NUMBER(CHAR. SUBSTR(LTRIM(RTRIM(date_time)), 15,2), f2.0), NUMBER(CHAR.SUBSTR(LTRIM(RTRIM(date_time)),18,6), f6.3)).

From: SPSSX(r) Discussion [mailto:SPSSX-L@LISTSERV.UGA.EDU] On Behalf Of Jon K Peck Sent: Friday, February 11, 2011 1:45 PM To: SPSSX-L@LISTSERV.UGA.EDU Subject: Re: Reading Date & Time from CSV File

David is right to be queasy about this format. It's not that it is free format. A good mantra here is "don't guess". It would be very easy for this file to be read incorrectly. There is no text delimiter such as " used, so a comma in the text would split a field. Obviously spaces don't work as separators either. If you read the file into Excel, the date/time field displays as 54:19.0 although the field value contains everything between the commas. (Excel inferred a format of mm:ss.0) CSV format is not standardized. I would be careful to validate any data read in this way.

Jon Peck Senior Software Engineer, IBM peck@us.ibm.com 312-651-3435

From: David Marso <david.marso@gmail.com> To: SPSSX-L@LISTSERV.UGA.EDU Date: 02/11/2011 12:43 PM Subject: Re: [SPSSX-L] Reading Date & Time from CSV File Sent by: "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>

_____

GACK!!!! My bad!!! I missed the (vital) CSV part of the question. I suspect that if either the string literals are quoted it would be fine. One reason I completely deplore free-formatted data in favor of fixed record fields. OTOH, if the DATETIME field were in DD-MM-YY HH:MM:SS it would probably work perfectly fine reading as a single DATETIME formatted variable. ----- Good catch Bruce! ...

David, I can't get your method to work for a CSV file that contains lines like this:

101,Posted date,2011-01-18 00:54:19.000,more text out here

If I allow both commas and blank spaces as field separators, the string "Posted date" gets split into two variables, and everything gets messed up. So it looks to me like your approach will work in a CSV file only if all blank spaces can be treated as field separators (in addition to commas). Or am I missing something?

Bruce

David Marso wrote: > > > Richard Ristow wrote: >> >> At 10:46 AM 2/11/2011, John Fiedler wrote: >> >>>I am trying to parse a CSV tile with a time and date variable that looks >>>like this: >>>2011-01-18 00:54:19.000 >>>What format do I use in my GET DATA command to read this? >> >> "Matthew Pirritano's quite right that you have to read it as a text >> field and parse it." >> >> You *DO NOT* have to it read as a text field ;-) . >> Read directly as two separate fields (a DATE and a TIME) then sum them. >> ----------- >> <SNIP> >> Why not just forget about GET DATA and just use good "old fashioned" DATA >> LIST. >> Read the date field as SDATE and the time field as TIME formats then sum >> them to get the DATETIME representation. Why bother with a STRING >> variable at all? >> In this case I read the time field as a scratch variable and add it to >> the date field, >> reformatting it later from SDATE to DATETIME. >> Personally I have NEVER been all that fond of GET DATA. >> >> data list FILE "C:\DATETIMETEST.txt" / dttm (sdate) #tm (time). >> COMPUTE dttm=dttm+#tm. >> FORMATS dttm (DATETIME). >> LIST. >> >> >> > >

-- View this message in context: <http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-Fi le-tp3381430p3381902.html> http://spssx-discussion.1045642.n5.nabble.com/Reading-Date-Time-from-CSV-Fil e-tp3381430p3381902.html Sent from the SPSSX Discussion mailing list archive at Nabble.com.

===================== To manage your subscription to SPSSX-L, send a message to LISTSERV@LISTSERV.UGA.EDU (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD