Being a cautious type, I read it as alpha and parsed the date and time
COMPUTE date =
UBSTR(LTRIM(RTRIM(date_time)), 9,2), f2.0),
COMPUTE time =
SUBSTR(LTRIM(RTRIM(date_time)), 15,2), f2.0),
From: SPSSX(r) Discussion [mailto:SPSSX-L@LISTSERV.UGA.EDU] On Behalf Of Jon
Sent: Friday, February 11, 2011 1:45 PM
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.
Senior Software Engineer, IBM
From: David Marso <firstname.lastname@example.org>
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
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
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?
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
>>>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.
>> Why not just forget about GET DATA and just use good "old fashioned" DATA
>> 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).
View this message in context:
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
For a list of commands to manage subscriptions, send the command