Download it now!
Bug 70319 - FILEOPEN: Detect special numbers in Text Import when open a csv
Summary: FILEOPEN: Detect special numbers in Text Import when open a csv
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.0.4 release
Hardware: Other All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: BSA target:4.2.0 target:4.0.6 target:...
Keywords: regression
: 54562 (view as bug list)
Depends on:
Blocks:
 
Reported: 2013-10-09 13:09 UTC by Joan Guillén
Modified: 2013-11-05 18:57 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
test csv (23 bytes, text/csv)
2013-10-10 11:55 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Joan Guillén 2013-10-09 13:09:33 UTC
Problem description:

Opening a csv with Calc, the option 'Detect special numbers' does the opposite effect than expected. 

Steps to reproduce:
1. Open a CSV with a field like: 123e
2. 'Detect special numbers' not selected
3. In the sheet, we'll find 123 (not 123e) converting the scientific notation.
---
4. Open the same CSV with a field like: 123e
5. 'Detect special numbers0 selected
6. In the sheet, we'll find 123e like a normal text


Expected behavior:

Acording https://help.libreoffice.org/4.0/Common/Text_Import :
+'Detect special numbers' unselected -> not converting scientific notation to numbers
+'Detect special numbers' selected -> converting .
              
Operating System: Ubuntu
Version: 4.0.2.2 release
Comment 1 Mike Kaganski 2013-10-10 11:54:06 UTC
Already reproducible with 3.6.0.4 under Win7x64.
Not reproducible with 3.5.7.2 -> regression.
While the report shows a corner case, i.e. importing a unusual (wrong?) syntax 123e, it reveals a real issue here:

When the "Detect special numbers" checkbox is checked, LO 3.6 and later behaves exactly as it used to in earlier versions - i.e. tries to detect "special numbers", and if it finds correct e-notation (i.e. [0-9]+e[-+]{0,1}[0-9]+), it imports it as number and formats the cell in scientific notation. Unrecognized patterns (e.g. 123e) are imported as text. However, if this checkbox is unchecked, LO 3.6+ (unlike previous versions) still tries to recognize those special numbers, and uses some other recognition engine (e.g., it now recognizes 123e as 123e0). It now doesn't apply any special formatting to those recognized numbers.

Consider the following .csv:
1,1e,1e1,1e+1,1e-1,e1

When imported using any LO with "Detect special numbers" checkbox checked, it gives the following:
A1=1; B1="1e"; C1=1,00E+001; D1=1,00E+001; E1=1,00E-001; F1="e1"

When imported using 3.5 and older with "Detect special numbers" checkbox unchecked, it gives the following:
A1=1; B1="1e"; C1="1e1"; D1="1e+1"; E1="1e+1"; F1="e1"

When imported using 3.6 and newer with "Detect special numbers" checkbox unchecked, it gives the following:
A1=1; B1=1; C1=10; D1=10; E1=0.1; F1="e1"

While one can consider this a minor issue (if any), in reality this makes it impossible to entirely avoid recognition of e-notation. This checkbox loses its meaning (and now has only formatting effect). And using different parsers to recognize the e-notation makes it even more cumbersome.
Comment 2 Mike Kaganski 2013-10-10 11:55:32 UTC
Created attachment 87383 [details]
test csv

.CSV to test
Comment 3 Eike Rathke 2013-10-14 12:04:20 UTC
Taking.
Comment 4 Commit Notification 2013-10-14 13:04:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

resolved fdo#70319 exponent must be followed by at least one digit



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 5 Eike Rathke 2013-10-14 13:07:11 UTC
Pending review
for 4-1 as https://gerrit.libreoffice.org/6241
for 4-0 as https://gerrit.libreoffice.org/6242
Comment 6 Commit Notification 2013-10-14 14:07: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=472ad8ba7ef99982025b37aba562f2135ca8a999

work around crappy SbiScanner::NextSym(), fdo#70319



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 Mike Kaganski 2013-10-15 01:15:04 UTC
But what does the "Detect special numbers" checkbox mean?
Comment 8 Eike Rathke 2013-10-15 10:45:52 UTC
Having that checked also various forms of dates, currency amounts, booleans (i.e. TRUE and FALSE) are imported as numerical values, in short everything that would be recognized when manually keying in a value to a cell.
Comment 9 Commit Notification 2013-10-15 11:03:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=66a96c82746118c70a447d7768b0428e15d2f5ad

clean up SbiScanner::NextSym() a little, fdo#70319 follow-up



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 Commit Notification 2013-10-15 16:08:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

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

resolved fdo#70319 exponent must be followed by at least one digit


It will be available in LibreOffice 4.0.7.

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 11 Commit Notification 2013-10-15 16:20:02 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=d8b9d9e84ef2e18bda11d51f6c9eb1fe4f1fb791&h=libreoffice-4-1

resolved fdo#70319 exponent must be followed by at least one digit


It will be available in LibreOffice 4.1.4.

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 12 Commit Notification 2013-10-17 16:10:36 UTC
Stephan Bergmann committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7bbd58eafc3146abcefc73d2d1ca6869bb47ef5a

fdo#70319 "exponent followed by at least on digit" also for special case 0.0



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 13 Commit Notification 2013-10-17 16:11:14 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-0-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d7eae616fcb80e1d77ea981cce363e1414fd5238&h=libreoffice-4-0-6

resolved fdo#70319 exponent must be followed by at least one digit


It will be available already in LibreOffice 4.0.6.

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 14 Commit Notification 2013-10-17 17:08:58 UTC
Stephan Bergmann committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

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

fdo#70319 "exponent followed by at least on digit" also for special case 0.0


It will be available in LibreOffice 4.1.4.

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 15 Commit Notification 2013-10-17 17:09:14 UTC
Stephan Bergmann committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=68befdd269a226e22dabd1b609326b38efc7eb21&h=libreoffice-4-0

fdo#70319 "exponent followed by at least on digit" also for special case 0.0


It will be available in LibreOffice 4.0.7.

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 16 Commit Notification 2013-10-17 18:47:06 UTC
Stephan Bergmann committed a patch related to this issue.
It has been pushed to "libreoffice-4-0-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=23c7aeb5ec633674a51b41e936ad9708e502841b&h=libreoffice-4-0-6

fdo#70319 "exponent followed by at least on digit" also for special case 0.0


It will be available already in LibreOffice 4.0.6.

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 17 Björn Michaelsen 2013-10-18 13:11:00 UTC
Note the last commit was in on the release branch _after_ rc2 was tagged, so unless there will be a rc3 this wont be in 4.0.6.
Comment 18 Eike Rathke 2013-11-05 18:57:04 UTC
*** Bug 54562 has been marked as a duplicate of this bug. ***