At 17:47 31/12/2007 +0100, Al Noname wrote:
Dan Lewis wrote:
On Monday 31 December 2007 08:49:10 Al Noname wrote:
I am migrating *.txt data from one system to
another. There are a lot of columns with numbers in them (e.g. "1.80")
When I open it with Calc, it converts it to a
date (01.01.80). 1.62 becomes 01.01.62, and 2.12 becomes 02.12.07
How do I shut this off?! I hate automation! I
dropped excel, because it is worse, it was
automating everything, like dropping leading zeros, etc.
With the txt or csv import I ensure all fields
are defined as "text" and not as "standard",
but Calc displays it as dates despite my definition.
Al
Is there a way to define these columns as numbers when you import the
file?
Dan
Hi Dan,
yes, it is. I have the problem that I am porting
French, German and English characters all in one
file, with the numbers that uses dot for the
decimal. In the imported system there the comma
is used for decimal. Besides that, I have to use
utf8 for exporting the code pages correctly, but
the import system only understands M$ ('*°°&%$%)
code pages. I have now managed to export a file
in just *.txt, and avoided using any office
products. It worked for now. There are more
language dependent pages to do next week or so.
I will then come back to the problem.
The auto conversion seems also different when
using OO-Calc on Windows or Linux. The code
pages are different for Linux, which does not
seem to be able to convert the M$ ('*&$§/&")
stuff. I will look into it more closely when the
going live pressure is low next month.
Al
This sort of thing is highly locale-dependent,
and I clearly have a different locale setting
from yours, so I don't make any guarantees. But
testing with my locale and its different
exceptional cases suggests that there are two
workarounds - both involving changing the .txt file - that may help:
o Another way of telling Calc that you want
values as text (apart from declaring the column
type) is to give them leading apostrophes. This
method seems a more robust technique in this
circumstance and to overcome your problem.
o If you give the values a sufficient number of
leading zeroes (apparently one or two, depending
on the data values themselves), you can fool Calc
into believing that these really are values, not
dates. This way, the values would end up as
numbers, not text, and you could format them as
you wished (to resurrect trailing zeroes, for
example) by formatting the cells in the normal
way. Or you could convert them back to text
using the TEXT function, perhaps pasting the
results back over the original values using Paste
Special and unticking Formulas.
I trust this helps (and that your party went well).
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
