Bug 68437 - Time values in csv import are misinterpreted
Summary: Time values in csv import are misinterpreted
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.0.4 release
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:4.2.0 target:4.1.2
Keywords: regression
: 69050 (view as bug list)
Depends on:
Blocks:
 
Reported: 2013-08-22 14:17 UTC by Rudolf Kollien
Modified: 2013-11-21 20:36 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
raw csv data (414.57 KB, text/csv)
2013-08-22 14:17 UTC, Rudolf Kollien
Details
ODS after import, sort, sum (195.92 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-08-22 14:18 UTC, Rudolf Kollien
Details
ODS after import, sort, sum and cell formating (205.17 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-08-22 14:19 UTC, Rudolf Kollien
Details
ODS from OpenOffice from csv file (231.39 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-08-22 14:20 UTC, Rudolf Kollien
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Rudolf Kollien 2013-08-22 14:17:52 UTC
Created attachment 84458 [details]
raw csv data

Hello,
when a csv file contains duration time values like "00:03:10", the values of this fields are misinterpreted in the way they are not computed correctly. 
I attach a csv file which contains a telephon call list. The calling number, the called number, date and time of the call, the duration, rate and total cost are listed.
- Import the attached file (charset ISO8859-15/Euro), starting line 1, extended number recognition on, separated by ";". Set "A-Rufnummer", "B-Rufnummer" to "Text", "Datum" as date DMY. Let the rest be standard.
- Sort on "A-Rufnummer", "Datum", "Uhrzeit" will give you a well-sorted list
- Now do subtotals ("Teilergebnisse" in german"). Group by "A-Rufnummer" and sum on "Dauer" and "Preis" (last is not really necessary to show the problem).

When you now look at the sums on "Dauer" (the duration of the call), you will see an 0. 

Trying to convert the field with cell formating to "Time format HH:MM:SS" ends up in values like: '00:04:23

They are now converted to implicit text! And the sum now displays "00:00:00".
I tried to convert the value back to time with search and replace (regular expression), but this is very complicated (have to use ^.00: as search item). Also it results in very strange values (a 00:00:57 sometimes converts to 00:57:00 and so on).

Importing the attached csv file into LO 3.x is without any problem. Importing the file in any version of OpenOffice (3.x, 4.x) also gives the right results.

Attached files:
lo-time-test.csv is the raw ascii file
lo-time-test-1.ods is the LO spreadsheet after import, sort, subtotal
lo-time-test-2.ods is the LO spreadsheet after import, sort, subtotal and formating the "Dauer" row as "time format"
oo-time-test.ods is the OpenOffice spreadsheet after import, sorting, subtotals 

oo-timne-test.ods shows how it would be correct (and LO previous to 4.1 did too)
Comment 1 Rudolf Kollien 2013-08-22 14:18:43 UTC
Created attachment 84459 [details]
ODS after import, sort, sum
Comment 2 Rudolf Kollien 2013-08-22 14:19:16 UTC
Created attachment 84460 [details]
ODS after import, sort, sum and cell formating
Comment 3 Rudolf Kollien 2013-08-22 14:20:05 UTC
Created attachment 84461 [details]
ODS from OpenOffice from csv file
Comment 4 Alex Thurgood 2013-08-22 14:49:04 UTC
I followed your steps using :

Version 4.0.3.3 (Build ID: 0eaa50a932c8f2199a615e1eb30f7ac74279539)

OSX 10.8.4

will try on LO-dev master build, could be a regression.


Alex
Comment 5 Alex Thurgood 2013-08-22 14:50:03 UTC
(In reply to comment #4)


Ooops, forgot to mention that it works OK for me on LO 4.0.3.3
Comment 6 Alex Thurgood 2013-08-22 15:02:01 UTC
Hmmm, attempting even just the sort step on my master build sends LO into beachball mode, requiring forced kill. Ho hum, time to download the 4.1.0.x


Alex
Comment 7 Alex Thurgood 2013-08-22 15:40:00 UTC
Confirming - regression over LO 4.0.3.3
Comment 8 Alex Thurgood 2013-08-22 15:41:24 UTC
Adding Eike, Kohei and Markus to CC

Haven't checked to see whether this is a duplicate yet.


Alex
Comment 9 Alex Thurgood 2013-08-22 15:58:26 UTC
Couldn't find any duplicates in BZ
Comment 10 m.a.riosv 2013-08-22 19:37:30 UTC
If time columns a set up as US English, the value is correctly imported but formatted as number not as time.

Win7x64Ultimate
Version: 4.1.1.0.0+ Build ID: c1f0f890f5065f88164add33707228f8c6d5755
Comment 11 Eike Rathke 2013-08-26 10:21:35 UTC
Certainly not a "highest blocker".
Comment 12 Alex Thurgood 2013-08-26 11:43:40 UTC
Hi Eike,

My bad, sorry, I was of the understanding that regressions between 4.0 and 4.1 release were deemed blockers. Thanks for correcting.


Alex
Comment 13 Eike Rathke 2013-08-26 11:49:42 UTC
Working on this.
Comment 14 Commit Notification 2013-08-26 12:23:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

resolved fdo#68437 append the bDetectSpecialNumber "true" string in 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 15 Eike Rathke 2013-08-26 12:28:54 UTC
Pending review for 4-1 as https://gerrit.libreoffice.org/5632
Comment 16 Commit Notification 2013-08-26 12:34:08 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=8875f681585e73f3f37088d58c75bf04ad098e2e&h=libreoffice-4-1

resolved fdo#68437 append the bDetectSpecialNumber "true" string in options


It will be available in LibreOffice 4.1.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 17 Eike Rathke 2013-09-09 12:36:45 UTC
*** Bug 69050 has been marked as a duplicate of this bug. ***
Comment 18 Cor Nouws 2013-10-07 16:52:52 UTC
as per comments in bug 69050