Re: Date Time Variant NOT read when loading CSV
From: JE McGimpsey (jemcgimpsey_at_mvps.org)
Date: 02/26/04
- Next message: Sheila: "Re: Sizing all comments at once"
- Previous message: ModelerGirl: "Re: Re: Median of differences of columns, with embedded "if""
- In reply to: kelvinq: "Date Time Variant NOT read when loading CSV"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 26 Feb 2004 06:53:50 -0700
Hmm...
When I import a .csv file with your data, I get the same results
*displayed*, but a quick check of the formula bar shows the data is
intact.
XL apparently sees the time with tenths of seconds and defaults to the
Time (not Date) format
mm:ss.0
format. But the data is still there - you can switch the format to
General to see that XL's parsed the string correctly - the two colons
were interpreted as part of a time string. To correct the display,
simply change the format:
Format/Cells/Number/Custom mm/dd/yyyy hh:mm:ss.0
Not sure where you were going with the "z" thing - XL doesn't have a
default "DateTime" format. However, it seems to me that doing a
Find/Replace would have worked ok. And you shouldn't have had to run the
Install routine for Office 2000 unless you hadn't loaded it before. I
don't remember there being a particular language issue, but it's been a
long time since I installed it...
In article <67d8e1e3.0402252122.dd9462a@posting.google.com>,
kelvinq415@yahoo.com (kelvinq) wrote:
> I wrote a Java Timestamp using myvariant.toString() which writes
> 2003-12-08 15:34:43.0
>
> In a CSV (Comma seperated Values file) Excel reads most things OK
> but it truncates the above string to
> 34:43.0
> It seems that Excel doesnt like 2 colons in a string!!!
> This bug occurs in Excel 97 & Excel 2000
>
>
> Excel 2000 is WORSE:
> if in Java I replace the first colon with 'z' then use a 'formula' in
> Excel to
> replace the 'z' with a colon I get back to a string which Excel97 can
> recognise as Date (Actually a DateTime)
> but Excel 2000 does NOT recognise the corrected field as a Date.
> The Install routine for Office 2000 looked like it was written by
> EAASL (English As A Second Language) migrants (from Eastern Europe?
> Bangalore?)
> I am glad I didnt pay for either version.
> cheers
> Kelvin
- Next message: Sheila: "Re: Sizing all comments at once"
- Previous message: ModelerGirl: "Re: Re: Median of differences of columns, with embedded "if""
- In reply to: kelvinq: "Date Time Variant NOT read when loading CSV"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
