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)
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
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.
*** This bug has been marked as a duplicate of bug 47109 ***
*** Bug 47109 has been marked as a duplicate of this bug. ***
*** Bug 56457 has been marked as a duplicate of this bug. ***
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.
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.
Pending review for 4-1 at https://gerrit.libreoffice.org/6823
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.
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