Date Time Variant NOT read when loading CSV

  • Thread starter Thread starter kelvinq
  • Start date Start date
K

kelvinq

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
 
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...
 
Back
Top