Bug Hunting Session
Bug 53103 - FILEOPEN INSERT 'Link to external data' 'Select the language to use for import' does not make a difference with dates/numbers
Summary: FILEOPEN INSERT 'Link to external data' 'Select the language to use for impor...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.4 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:4.3.0 target:4.2.0.0.beta2 tar...
Keywords:
: 47109 56457 (view as bug list)
Depends on:
Blocks: Calc-External-Datalink
  Show dependency treegraph
 
Reported: 2012-08-03 18:29 UTC by joaquin
Modified: 2017-08-28 19:15 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description joaquin 2012-08-03 18:29:25 UTC
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)
Comment 1 joaquin 2012-08-06 17:10:10 UTC
Just found out that the situation described above is with the "global setting"

Load/Save -> HTML Compatibility -> Use English (USA) locale for number

Without that checked the choice 'Select the language to use for import' does something:

- The first example is handled correctly with "Dutch/NL" and checking the box for dates and numbers. But the imported text remains TEXT.
- The second example does not work, even when selecting English/UK: The date is not swapped but the numbers are still TEXT and the VALUE function does not work on other number-formats then your default locale (see bug 53173).


So I guess the real problem with this bug comes down to:

- The global setting "Use English (USA) locale for numbers" does not make sense since it overrules locale choices on import, instead of just setting a default, and messes with dates as well. 
- The choice "Select the language to use for import" can be improved a lot by
  - solving bug 47109, i.e. numbers should be treated as such and
    according to the locale-choice for import
  - make it possible to see and/or change this choice for an added link


Tested with LibreOffice 3.6.0 RC4
Comment 2 Rainer Bielefeld Retired 2012-10-27 15:41:01 UTC
Currently I do not know what exactly that Language' selection does, but definitively it's different from 'Locale'. I left a more detailed description of the problem in "Bug 56457 - allow (document related) locale setting for 'Insert - External Data'" Comment 1.
Comment 3 Eike Rathke 2013-11-26 22:46:10 UTC

*** This bug has been marked as a duplicate of bug 47109 ***
Comment 4 Eike Rathke 2013-11-26 22:51:45 UTC
*** Bug 47109 has been marked as a duplicate of this bug. ***
Comment 5 Eike Rathke 2013-11-26 23:00:01 UTC
*** Bug 56457 has been marked as a duplicate of this bug. ***
Comment 6 Commit Notification 2013-11-26 23:14:27 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=513eadd422ff6a41cfe9a16f82cf32872d729652

resolved fdo#53103 actually use the external data filter options



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2013-11-26 23:17:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1de7d0aba4142424fe0082071a4ac64ec377cea0&h=libreoffice-4-2

resolved fdo#53103 actually use the external data filter options


It will be available in LibreOffice 4.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 8 Eike Rathke 2013-11-26 23:22:30 UTC
Pending review for 4-1 at https://gerrit.libreoffice.org/6823
Comment 9 Commit Notification 2013-12-01 00:45:07 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ea40b8b14d830f5025a3fe2e9da9163b83a843e5&h=libreoffice-4-1

resolved fdo#53103 actually use the external data filter options


It will be available in LibreOffice 4.1.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 10 m.a.riosv 2013-12-09 23:08:41 UTC
Thanks Eike.

Works for me, with detect special numbers enable, in:
Win7x64Ultimate
Version: 4.1.5.0.0+ Build ID: 812963c5ff6e1c4685eddb830a82a69a3801d82
Version: 4.2.0.0.beta2+ Build ID: 02180aed7dc0b8c5f9cc23b319adc2386a9aab69
         TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2013-12-08_08:48:31
Version: 4.3.0.0.alpha0+ Build ID: c6fe5c6f371328cb6cf61d68f016a04f8b635a48
         TinderBox: Win-x86@39, Branch:master, Time: 2013-12-08_02:58:23