Bug 88359 - CSV import could recognize ISO 8601 date/time fields without having to check "Detect special numbers" on
Summary: CSV import could recognize ISO 8601 date/time fields without having to check ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.8.2 release
Hardware: All All
: low enhancement
Assignee: Eike Rathke
URL:
Whiteboard: target:7.3.0
Keywords:
Depends on:
Blocks: CSV-Import
  Show dependency treegraph
 
Reported: 2015-01-13 09:00 UTC by Nemo_bis
Modified: 2021-10-18 13:19 UTC (History)
6 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 Nemo_bis 2015-01-13 09:00:34 UTC
I've read http://erack.org/blog/archives/8-LibreOffice-date-acceptance-patterns.html and I don't find this situation helpful. ISO 8601 is a standard and is not locale-independent. I don't care that much about the output/display format, but formats such as "2014-11-08T19:24:24" should always be recognised.

User story: as a user with a a list of ISO 8601 timestamp, I want them to be consistently recognised as timestamps, so that they are displayed as dates and can be worked on as dates (e.g. to calculate time intervals/differences).

Bug 72040 is related (or can be considered a blocker/subtask) but seems focused on a single ISO 8601 format and on display.

I'm using 4.2.8.2-1.fc20 with Italian locale. My specific input was https://frdata.wikimedia.org/campaign-vs-amount.csv
Comment 1 raal 2015-01-13 12:34:45 UTC
Hello,
in your .csv file is format of date as in bug 72040. I think you have to specify which other format Calc should accept, because  ISO 8601 allows reduced precision.
https://en.wikipedia.org/wiki/ISO_8601
setting as enhancement
Comment 2 GerardF 2015-01-13 13:10:28 UTC
Your .csv file opens with ISO dates recognized as date whatever locale I'm using (my default is French) with the box "Detect special numbers" checked.
Comment 3 Robinson Tryon (qubit) 2015-01-17 01:00:40 UTC
(In reply to Nemo_bis from comment #0)
> I've read
> http://erack.org/blog/archives/8-LibreOffice-date-acceptance-patterns.html
> and I don't find this situation helpful.
> ...
> User story: as a user with a a list of ISO 8601 timestamp, I want them to be
> consistently recognised as timestamps, so that they are displayed as dates
> and can be worked on as dates (e.g. to calculate time intervals/differences).
> ... 
> I'm using 4.2.8.2-1.fc20 with Italian locale. My specific input was
> https://frdata.wikimedia.org/campaign-vs-amount.csv

So we have a test file. What are the repro steps to take and the RESULTS vs what you EXPECTED to see?

Status -> NEEDINFO

Thanks.

(set status back to UNCONFIRMED after you provide those repro steps)
Comment 4 Eike Rathke 2015-02-19 16:49:42 UTC
As comment 2 states, the full ISO 8601 date/time format is recognized if the "Detect special numbers" checkbox in the import dialogue is checked on.

What maybe could be done is to detect the date/time also if that is unchecked, but that would require to apply the same exact cell format as people may want to export the file again and preserve the ISO format, for which at the moment the unchecked "special numbers" can be used.

Removing NEEDINFO, changing summary as this is not related to date acceptance patterns but only the CSV import.
Comment 5 Nemo_bis 2015-02-20 09:35:55 UTC
Thanks for figuring it out and sorry for jumping to conclusions based on docs.

I'm still not sure what steps I'm supposed to follow, this is what I tried now:
0) LibreOffice 4.2.8.2-2.fc20; it-IT interface, locale and default document language
1) "libreoffice campaign-vs-amount.csv"
2) In the import window select UTF-8, check "detect special numbers", confirm

I. Observed: The table is there, as is in the CSV.

3) Apply a cell format of any "date" kind to the date columns

II. Expected: dates are displayed in the chosen format.
III. Observed: dates remain in their "native" CSV format.

4) Apply any date function to any of the date cells, e.g. DATA.DIFF(E4;I4)

III. Expected: the date function gives an output.
IV. Observed: Err:511

5) Repeat steps 3-4 after changing locale to fr-FR in Tools > Options > Language settings > Language > Locale (but not interface, nor default document language, nor this document's language).

V. Observed: the date patterns in the same options panel vary.
III and IV again.

Am I still doing something wrong?
Comment 6 Eike Rathke 2015-02-20 12:31:48 UTC
Strange, import works with other locales (you could change in step 2 above), but not with it-IT. Also manual input of 2014-11-23T12:34:56 is not accepted as date in it-IT, but is in other locales.
That's a bug, I'll investigate.
Comment 7 Eike Rathke 2015-02-20 14:08:13 UTC
Split-off bug 89500 for this.
Comment 8 Dennis Schridde 2021-03-01 00:48:54 UTC Comment hidden (off-topic)
Comment 9 Dennis Schridde 2021-03-01 00:52:03 UTC Comment hidden (off-topic)
Comment 10 Eike Rathke 2021-03-01 12:07:21 UTC Comment hidden (off-topic)
Comment 11 Eike Rathke 2021-10-14 19:36:14 UTC
Fwiw, ISO date(+time) is recognized if in the import dialog a column's type is set to "Date (YMD)", also if "Detect special numbers" is off, just that the display format is not set to a matching format but the document locale's standard date(+time) formats are used.

Keeping this RFE to apply proper formats for these cases, so saving again can save with the intended format.
Comment 12 Commit Notification 2021-10-14 23:40:01 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/9bcdfcb74dae7ccd07be7159c0e1deba110d2a6f

Related: tdf#88359 CSV: choose proper ISO date(+time) format if detected

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2021-10-15 11:19:23 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/99adfcad3517de6530c9a2f025c528001f543ae2

Resolves: tdf#88359 CSV: import strict ISO date(+time) with standard settings

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2021-10-18 13:19:33 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/bb5968d199e5f185b5dbbc9be6b6bca992b94932

tdf#88359: sc: Add UItest

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.