https://bugs.freedesktop.org/show_bug.cgi?id=41577
Summary: PIVOTTABLE: floating-point data from database getting
converted to date
Product: LibreOffice
Version: LibO 3.3.2 release
Platform: Other
OS/Version: All
Status: UNCONFIRMED
Status Whiteboard: BSA
Severity: normal
Priority: medium
Component: Spreadsheet
AssignedTo: [email protected]
ReportedBy: [email protected]
Created an attachment (id=52102)
--> (https://bugs.freedesktop.org/attachment.cgi?id=52102)
inside the blue boundary, what calc gets from the datapilot fed from the
database; inside the red boundary, what base sees in the same database
Problem description:
When LibreOffice calc retrieves floating-point data from a LibreOffice database
(odb file) while having a locale setting with comma as the decimal separator, a
floating-point number from the database may get changed by calc (and therefore
lost from the spreadsheet) into a date. That is, a number like 2.12 gets
changed into an integer (in this example, 40879) which yields 2/12/01 (December
2nd 2011) in date format. The floating-point data is then forever lost.
Steps to reproduce:
1. Create a txt file like the following (without the pounds of course):
##########################
numbers field
22,1 a
22 b
55,1 c
5,501 d
5,5001 e
5,50001 f
##########################
2. Fire up database and connect to that txt file for it to create a table from
that txt file using space as field separator and comma as decimal separator.
Save that odb file and register it to your data sources (so you can later
access it from calc)
2.a. check that in the database, everything seems ok; double-clicking the table
you just created gives you:
#########################
numbers field
22,10 a
22,00 b
55,10 c
5,50 d
5,50 e
5,50 f
#########################
and right-clicking the table and selecting "edit" shows you that:
#########################
Field Name Field Type
numbers Decimal [ DECIMAL ]
field Text [ VARCHAR ]
#########################
3. fire up calc, go to Data -> DataPilot -> Start... -> Data source registered
in LibreOffice
In "Database", select your data source, this one you have just registered
In "Data Source", select the table with that test data you have just created
In "Type", select "sheet"
Confirm. Then, in the box that appears, drag "field" to the "Row fields" area,
and then do the same with "numbers".
Current behavior:
A sheet is created in calc, with the following table:
##############################
| A | B
--------------------------
1| field | numbers
--------------------------
2| a | 22/01/11
--------------------------
3| b | 22
--------------------------
4| c | 55.1
--------------------------
5| d | 5501
--------------------------
6| e | 01/05/01
--------------------------
7| f | 5.50001
##############################
additionally, if I enter the following formulas in cells next to that table:
=ISNUMBER(B2)
=ISTEXT(B2)
=ISNONTEXT(B2)
and drag down to get information about the data types of whatever got inside
calc, I get:
##########################################################
| A | B | c | d | e
----------------------------------------------------------
1| field | numbers | isnumber | istext | isnontext
----------------------------------------------------------
2| a | 22/01/11 | 1 | false | 1
----------------------------------------------------------
3| b | 22 | 1 | false | 1
----------------------------------------------------------
4| c | 55.1 | false | 1 | false
----------------------------------------------------------
5| d | 5501 | 1 | false | 1
----------------------------------------------------------
6| e | 01/05/01 | 1 | false | 1
----------------------------------------------------------
7| f | 5.50001 | false | 1 | false
##########################################################
Expected behavior:
A sheet is created in calc, with the following table:
##########################################################
| A | B | c | d | e
----------------------------------------------------------
1| field | numbers | isnumber | istext | isnontext
----------------------------------------------------------
2| a | 22,1 | 1 | false | 1
----------------------------------------------------------
3| b | 22 | 1 | false | 1
----------------------------------------------------------
4| c | 55,1 | 1 | false | 1
----------------------------------------------------------
5| d | 5,501 | 1 | false | 1
----------------------------------------------------------
6| e | 5,5001 | 1 | false | 1
----------------------------------------------------------
7| f | 5,50001 | 1 | false | 1
##########################################################
OBS: this appears to be a problem in the integration between base and calc,
since base sees everything as floats and knows where the decimal separator is
and calc simply converts data to whatever it thinks it is. Numbers come to calc
with dots even though the locale is set to use commas both system-wide and
LibreOffice-wide, and numbers within date range (between 1 and 31 for days and
1 and 12 for months) get converted to date. Notice that 55,1 and 5,50001 didn't
get converted to date but got converted to text and 5,501 simply lost it's
decimal information (maybe got converted to a year??)
This also happens if the txt is created with dots instead of commas and base is
told to use dot as decimal separator. Base shows things as they should be, and
calc shows them messed up. I think it is absurd to have the date autoformatting
in DataPilots since the data is being retrieved from a database (and therefore
cannot be a typo); it has been suggested before to have an option to disable
the autoconverting of strings with numbers and dots into dates and I see here a
good reason why the software should be told when it **doesn't** know better
than the user. In DataPilots there shouldn't even exist an option to "autofix"
things, since anything wrong there must be fixed in the database, which relies
externally, and should those things be fixed automatically in the spreadsheet,
mistakes in the database might get unnoticed.
As DataPilot cells are locked against formatting, cell formatting is not an
option. Also, trying to change the style of text of the DataPilot entries
doesn't work. Changing the locale of the whole LibreOffice suite to one that
uses dot as decimal separator solves it, but that is **not** an option either
(I have documents that must be published countries where comma is the standard
for decimal separator) and this behaviour is not what it is supposed to be
anyway; calc should be able to understand what data are strings and what are
floats anywhere in the world.
--
Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
[email protected]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
