[Libreoffice-bugs] [Bug 53103] New: FILEOPEN INSERT 'Link to external data' 'Select the language to use for import' does not make a difference with dates/numbers

[bugzilla-daemon at freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=53103

             Bug #: 53103
           Summary: FILEOPEN INSERT 'Link to external data' 'Select the
                    language to use for import' does not make a difference
                    with dates/numbers
    Classification: Unclassified
           Product: LibreOffice
           Version: 3.5.4 release
          Platform: All
        OS/Version: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Spreadsheet
        AssignedTo: libreoffice-bugs at lists.freedesktop.org
        ReportedBy: narebeestjes at yahoo.com


When you choose:
INSERT -> 'Link to external data...'
and than link to a website with dates and numbers you get the question:

'Select the language to use for import'

It does not really matter what you select, nothing seems to make a difference.
Especially with dates when the day-of-month is 1-12 (so it could be a month in
the USA).

LibreOffice seems to always do the following:
- US/UK formated numbers are imported as number fields (and displayed according
to locale)
- Numbers with a "," as decimal separator are imported as text (easy to convert
with the VALUE() function as long as you use the "," as separator in your
locale).
- Dates are a mess: imported as text but translated to US-format anyway in
cases like day-of-month is below 13.

I guess the only real solution is to get everybody to start using ISO formatted
dates (YYYYMMDD) :-(
But maybe the 'Select the language to use for import' can be split in 2
working, separate options that honour the following choices:
- use "," or "." as decimal separator for numbers
- use ISO, US, "Rest of the world" for date (all three with/without common
separators like "-", "/", ..)

This might not work for written dates but these seem to work better according
to your locale?


Two examples reproduce:

1. - strict locale
Open a site that is clearly not US-English and consistent in using "non
US-dates" and a "," as a decimal separator, e.g.

https://www.asnbank.nl/asnappl/scripts/koersen/koersen.asp

Today (ISO 20120803) you get a table with something like (but with dutch text):

       Quote
       03-08-2012
Fund:  60,27

No matter if you choose "Automatic" or "Dutch", Calc insists on:

       08-03-2012
       60,27    

Both are text fields in this case (see bug
https://bugs.freedesktop.org/show_bug.cgi?id=47109). 


2. - Mixture of locales: 
NYSE/Euronext is unable to select a locale and stick to it: Numbers are
formatted like US/UK-English, dates like NL/UK/.. when you select "NL".

http://www.euronext.com/trader/priceslists/priceslists-1800-NL.html

Example text from today:

ARCELORMITTAL .. AMS,PAR
12.25      16,537,998     2.90     03/08/12 17:38

Linking to this page gives numbers in "NUMBER" fields and the date as TEXT (@):
12,25  .... 08-03-2012 17:38:00

Solution for the numbers is the VALUE() function which translates text wit
numbers in your own locale to NUMBER-format. For numbers in the other locale
the import worked.

For the date there is only an ugly manual solution possible for days like today
when LibreOffice insists on swapping the values around to US-date.

(roughly: convert the date to ISO-formatted string and see if it is between
today and 30-days ago, if not (future or long ago) it might be wrong,
especially when you retrieve stock quotes every few days like in this example)

-- 
Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.