Bug Hunting Session
Bug 47109 - 'Insert external data' form HTML table imports US dates with the cell formated as TEXT (@)
Summary: 'Insert external data' form HTML table imports US dates with the cell formate...
Status: CLOSED DUPLICATE of bug 53103
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.1 release
Hardware: All All
: high normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsDevEval
Depends on:
Blocks:
 
Reported: 2012-03-08 10:46 UTC by m.a.riosv
Modified: 2015-12-16 22:31 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Table with three columns, second column is date. (5.25 KB, text/html)
2012-03-08 10:46 UTC, m.a.riosv
Details
Example of buggy number import (25.63 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-08-15 18:59 UTC, satlinker
Details

Note You need to log in before you can comment on or make changes to this bug.
Description m.a.riosv 2012-03-08 10:46:02 UTC
Created attachment 58204 [details]
Table with three columns, second column is date.

Opening HTML file with dates MM/DD/YYYY (English-US), trough link to external data source, import dates well formed but with the cell formatted as text (@).

Opening in the system with right-click, selecting LibreOffice Calc to open, the dates look well with the cell formatted as date DD/MM/YY.
Comment 1 joaquin 2012-08-06 17:24:59 UTC
Also numbers are imported as TEXT, with one exception:

- Global setting 
  Load/Save -> HTML Compatibility -> Use English (USA) locale for number
  changes this behaviour for numbers with a "." as decimal separator 
  (but swaps month and day around if day < 13)
- Withouth this global setting all numbers in all formats and (manipulated) dates
  are imported as TEXT

Tested with LibreOffice 3.5.4 & 3.6.0 RC4
Comment 2 bfoman (inactive) 2012-08-08 12:35:05 UTC
(In reply to comment #0)
> Opening HTML file with dates MM/DD/YYYY (English-US), trough link to external
> data source, import dates well formed but with the cell formatted as text (@).

Confirmed with:
LO 3.5.5.3 
Build ID: own W7 debug build
Windows 7 Professional SP1 64 bit(In reply to comment #0)
 
> Opening in the system with right-click, selecting LibreOffice Calc to open, the
> dates look well with the cell formatted as date DD/MM/YY.

Could not reproduce.
Comment 3 satlinker 2012-08-15 18:59:50 UTC
Created attachment 65609 [details]
Example of buggy number import

File imports numbers from http://n2yo.com/satellites/satlinker.php
in the third coloumn it tests some numbers of the second coloumn if its a number or not.
Comment 4 satlinker 2012-08-15 19:16:34 UTC
The import of numbers doesn't work at all in version 3.6.

See the second attachment, it imports numbers from a website. In the third coloumn it tests some numbers of the second coloumn if its a number or not.
It worked fine in former libreoffice versions.

The numbers don't have a ' before to format it as text, however they are not recognized as numbers.

It has nothing to do with Load/Save -> HTML Compatibility -> Use English (USA) locale for number. This should not be a global option but an option for a specific table.

Please be careful with changes to a running system, that error makes me a lot of troubles. Better not so many updates therefor better tested ones.
Comment 5 joaquin 2012-08-31 18:58:35 UTC
(In reply to comment #1)
> Also numbers are imported as TEXT, with one exception:
> 
> - Global setting 
>   Load/Save -> HTML Compatibility -> Use English (USA) locale for number
>   changes this behaviour for numbers with a "." as decimal separator 
>   (but swaps month and day around if day < 13)
> - Withouth this global setting all numbers in all formats and (manipulated)
> dates
>   are imported as TEXT
> 
> Tested with LibreOffice 3.5.4 & 3.6.0 RC4

Use this global setting, automatic on import and import some numbers with two or three decimals:
http://beurs.fd.nl/aandelen/amsterdam/aex/

Then the "," decimal separator is lost in the TEXT() fields with more than two decimals, e.g.

 ArcelorMittal 	11,855

Becomes:

 ArcelorMittal 	11855

While 
 Royal Dutch Shell A 	27,81

gets the same characters "27,81" in the second TEXT-field.
Comment 6 Rainer Bielefeld Retired 2012-09-16 07:42:52 UTC
It seems that I can not reproduce that problem with "Server Installation of "LibreOffice 3.5.1.2 English UI/Locale [Build-ID: dc9775d-05ecbee-0851ad3-1586698-727bf66] on German WIN7 Home Premium (64bit)", 'Tools -> options -> LoadSave -> HRML Compatibility' Check Import = Use English (USA)

1. New Spreadsheet
2. Menu 'Insert -> Link To External Data' for sample document "Table with three
   columns, second column is date
3. In dialog "Import Options" check "Language=Automatic", "Detect Special 
   Numbers"  <ok>
   > HTML contents listing appears after few moments
4. Select "HTML_tables" <ok>
   HTML page table conetents will appear in spreadsheet after few moments

To check what has been imported I formatted 3 other Columns as Number and in the cells I did a SUM of 2 vertically adjacent cells, what showed that the "dates" in the cells like "01/16/2012" were text, because the result for the SUM was "0.00"

So I can confirm reporter's observations. 

Now we have to check whether the problem persists with latest versions. 

Additionally we will have to check whether there are additional strange aspects. For example when I import table from <https://wiki.documentfoundation.org/User:RBd/Workbench/Draft1> with 'Tools -> options -> LoadSave -> HRML Compatibility' Check Import = Use English (USA), it seems LibO recognizes "1/2" as date and completes it to "01/02/2012", but the formatting in the Cell is as text what a " =SUM()" test will show.

I am pretty sure that this is a bug, but I will have to think about relations to "Bug 53177 - CONFIGURATION "Use 'English (USA)' locale for numbers" messes with dates and "insert link to external data"" and others.

Any idea with what version this started?
Comment 7 m.a.riosv 2012-09-16 10:25:20 UTC
Reproducible 
Win7x64 LibreOffice 3.3.4 OOO330m19 (Build:401) (Portable)
Comment 8 Rainer Bielefeld Retired 2012-10-27 15:39:49 UTC
How can LibO know that that should be a date and not some very special fraction without correct locale settings? I left a more detailed description of the problem in "Bug 56457 - allow (document related) locale setting for 'Insert - External Data'" Comment 1
Comment 9 joaquin 2012-10-29 11:03:18 UTC
(In reply to comment #8)
> How can LibO know that that should be a date and not some very special

That is not the point here. The point is that LibreOffice already recognises it as a date following its magic to do so. So it the problem reported in this bug is not about special fractions.

The problem is that LibreOffice inserts the recognised date as text - and according to your locale messes with its localization (bug 53177). 

> fraction without correct locale settings? 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 is not about locale or date format either. It is about marking cells as text after recognising dates (you know that because it is able to force them in the US format).
Comment 10 Rainer Bielefeld Retired 2012-10-29 16:35:49 UTC
Comment on attachment 65609 [details]
Example of buggy number import

this bug is only concerning wrong date import, but this document contanins no date at all. So I obsolete it.
Comment 11 Rainer Bielefeld Retired 2012-10-29 18:04:00 UTC
@mariosv
The Date Formatting in the HTML source is MM/DD/YYYY, not DD/MM/YYYY as you state in the summary?
Comment 12 m.a.riosv 2012-10-29 20:16:49 UTC
(In reply to comment #11)
> @mariosv
> The Date Formatting in the HTML source is MM/DD/YYYY, not DD/MM/YYYY as you
> state in the summary?

Hi @Rainer, can you help with a a more understandable summary, if the new content is not right.
Comment 13 Joel Madero 2013-06-27 22:08:38 UTC
Not sure why this wasn't marked as confirmed - I can easily confirm exactly what's in Description.

Thank you for reporting this issue! I have been able to confirm the issue on:
Version: 4.2.0.0.alpha0+Build ID: b0a1666f756aa5f5315366eca9d7d02ddd55d2b
Date:   Tue May 28 08:51:01 2013 +0300 
Platform: Bodhi Linux 2.2 x64

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
As I've been able to confirm this problem I am marking as:

New (confirmed)
Normal - can prevent high quality and/or professional quality work
High - since behavior is different when you insert external data vs. opening the file direct, I am setting this as high. we should expect behavior to be consistent.

Keywords - 

Whiteboard Status - ProposedEasyHack - since it works in one instance, I suspect it's easy to fix it in the other (I could be very wrong)

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link:
https://wiki.documentfoundation.org/QA/BugTriage and join us on freenode at #libreoffice-qa

There are also other ways to get involved including with marketing, UX, documentation, and of course developing -  http://www.libreoffice.org/get-help/mailing-lists/. 

Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Comment 14 Eike Rathke 2013-11-26 22:46:10 UTC
*** Bug 53103 has been marked as a duplicate of this bug. ***
Comment 15 Eike Rathke 2013-11-26 22:51:45 UTC
vice versa..

*** This bug has been marked as a duplicate of bug 53103 ***
Comment 16 Robinson Tryon (qubit) 2015-12-16 22:31:18 UTC
Migrating Whiteboard tags to Keywords: (ProposedEasyHack -> needsDevEval)
[NinjaEdit]