Apache OpenOffice (AOO) Bugzilla – Issue 83667
Auto-format erroneously converting TEXT value into DATE
Last modified: 2013-08-07 15:12:27 UTC
When copyijng data from the USDA database (http://www.nal.usda.gov/fnic/foodcomp/search/index.html) into CALC, there is a problem where CALC insists on auto-formatting the input and it causes an error. Sequence: Open this link (http://www.nal.usda.gov/fnic/foodcomp/search/index.html) and choose any food. Now highlight the data, and copy it into CALC. Everything works fine except in the "LIPIDS" identifiers, in the first column. Sample column from Bananas: Lipids Fatty acids, total saturated 4:0 6:0 8:0 10:0 12:0 14:0 16:0 18:0 Once that data is in the fields, it gets converted into a "time" value so that 4:0 becomes 04:00:00 AM. No attempt to change the data into 4:0 by defining the data as text data works, and even if the whole column is defined as text data, CALC still insists on auto-formatting 4:0 and similar values into time values. The manual work around is to enter '4:0. Tis is a major problem if large anounts of data are being studied. It would be better if CALC re-displayed the data as it was pasted in once the auto-formatted time value was switched back to text. A better solution would be: 1)User selects column to be used for data. 2)User selects column and the selects "Format Cells" 3)User selects "text" 4)All data pasted into a column is displayed verbatim,with no conversions.
Turning auto-correct OFF when pasting the text data does not fix this problem.
Hi, as we work in the same way Excel does, this Issue is not a defect. The value 4:3 is in most locales a time value, therefore on paste the default time forrmat for the choosen locale is used to display these values. The enhancement in this case is the possibility to choose if the HTML Values should be pasted as text or as values according to the expected formatting as it is now. Frank
We have a similar issue when importing the data from the following url http://www.euromillions.be/show.aspx?culture=fr&pageid=results/latests in Calc with locale FR(France). In this file numbers are formated with belgium conventions : dot for thousand separator and comma for decimal separator. When importing this data with linguistic environment configuring as FR(France), some numbers are converted in dates. It should be useful to have some option in import function allowing to indicate to Calc what are thousand and decimal separators for imported data Another improvement may be to remove automatic date formatting in such import functions