Bug 127893 - Detect other date formats when pasting text
Summary: Detect other date formats when pasting text
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.3.2.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsDevEval
Depends on:
Blocks: CSV-Import
  Show dependency treegraph
 
Reported: 2019-09-30 22:52 UTC by Jonny Grant
Modified: 2021-01-20 23:48 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
text import date (26.28 KB, image/png)
2019-10-01 17:48 UTC, Oliver Brinzing
Details
test.csv with greyed out (93.54 KB, image/png)
2019-10-01 19:54 UTC, Jonny Grant
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jonny Grant 2019-09-30 22:52:38 UTC
When I paste a column of text into Calc, I tick "Detect special numbers"

Only (c) below works. It's pretty easy to identify dates, can some more options be added on to to the detection?

a) LibreOffice cannot detect this format as a date.

Jul-19-2019
Jul-18-2019
Jul-17-2019

b) LibreOffice cannot detect this format as a date.
Sep 29, 2019
Sep 28, 2019
Sep 27, 2019


c) LibreOffice can detect this format as a date

Jul/19/2019
Jul/18/2019
Jul/17/2019



Some other options worth adding detection for
2019-Jan-01
01-Jan-2019


Some more advanced
01-Jan-18

Some really advanced
26/12/2019
12/26/2019

(need to check which field goes above 12 to identify the month)
Comment 1 Oliver Brinzing 2019-10-01 17:47:35 UTC
> The selected language influences how such special numbers are detected, 
> since different languages and regions many have different conventions 
> for such special numbers.

Can you please add information from Menu Help/About LibreOffice ?
Comment 2 Oliver Brinzing 2019-10-01 17:48:20 UTC
Created attachment 154686 [details]
text import date

And have you tried to select the type via context menu?
Comment 3 Jonny Grant 2019-10-01 19:53:04 UTC
Hi Oliver
Many thanks for your reply.

Could I ask, how are you pasting to get that dialog?
When I paste, I don't get those options.

If I save Jul-19-2019 in a file.csv, and then open the file, I get your dialog, but the "Column type" is greyed out, even with "Detect special numbers" enabled. Any ideas?

I wonder which version you are using.

I will add a screenshot.
Jonny


Version: 6.3.2.2 (x64)
Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU threads: 1; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: en-GB (en_GB); UI-Language: en-US
Calc: threaded
Comment 4 Jonny Grant 2019-10-01 19:54:26 UTC
Created attachment 154689 [details]
test.csv with greyed out
Comment 5 QA Administrators 2019-10-02 02:57:25 UTC Comment hidden (obsolete)
Comment 6 Oliver Brinzing 2019-10-02 18:51:38 UTC
(In reply to Jonny Grant from comment #3)
> If I save Jul-19-2019 in a file.csv, and then open the file, I get your
> dialog, but the "Column type" is greyed out, even with "Detect special
> numbers" enabled. Any ideas?
> I wonder which version you are using.

i am using 

Version: 6.3.2.2 (x64)
Build-ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: 

Have you tried to select the column header [Standard] first, this works for me.
Comment 7 Jonny Grant 2019-10-02 19:28:43 UTC
(In reply to Oliver Brinzing from comment #6)
> (In reply to Jonny Grant from comment #3)
> > If I save Jul-19-2019 in a file.csv, and then open the file, I get your
> > dialog, but the "Column type" is greyed out, even with "Detect special
> > numbers" enabled. Any ideas?
> > I wonder which version you are using.
> 
> i am using 
> 
> Version: 6.3.2.2 (x64)
> Build-ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
> CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
> Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
> Calc: 
> 
> Have you tried to select the column header [Standard] first, this works for
> me.

Hi
Yes, if I select the field first, it does that. (Sorry that wasn't obvious - seems very obscure)

However, this is only the CSV import. Were you able to get this dialog when pasting text?
Comment 8 Oliver Brinzing 2019-10-02 19:44:37 UTC
(In reply to Jonny Grant from comment #7)
> However, this is only the CSV import. Were you able to get this dialog when
> pasting text?

In that case it seems to work only if there are at least 2 data rows.
Comment 9 Jonny Grant 2019-10-02 21:55:39 UTC
Should I file another ticket for this paste issue?

I'd rather the auto-detection just worked, they are obviously dates.

Jul-19-2019
Jul-18-2019
Jul-17-2019
Comment 10 Oliver Brinzing 2019-10-03 06:31:41 UTC
with:

Version: 6.4.0.0.alpha0+ (x64)
Build ID: 66e45a1ae861d50edf65fed9e39c9c9d5b15e0ac
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

i can reproduce with paste special and enabled detect special numbers as follows:

a) Jul-19-2019
   no auto detection, but setting column to MDY format works

b) Sep 29, 2019
   works with auto detection

c) Jul/19/2019
   no auto detection, but setting column to MDY format works

and with "Locale: en-US (de_DE); UI-Language: en-US" as follows:

a) Jul-19-2019
   no auto detection, but setting column to MDY format works

b) Sep 29, 2019
   works with auto detection

c) Jul/19/2019
   works with auto detection

asking for dev advice
Comment 11 Eike Rathke 2021-01-19 15:38:21 UTC
Jul-19-2019 is *not* obviously a date if the locale in use doesn't define such, specifically not in an en-GB locale that would have a D/M/Y order not M-D-Y, though it may look obvious for you in your context. But not even in the en-US locale it is a common date because even there it would be written as 19-Jul-2019. Are you by chance Canadian? Because en-CA is about the only locale where that date form would be used, https://en.wikipedia.org/wiki/Date_format_by_country and https://en.wikipedia.org/wiki/Date_and_time_notation_in_Canada#English and entering Jul-19-2019 in en-CA locale *does* recognize it as date.

And that's also why the CSV/Text import dialog offers the option to force a column to a specific date type and order.. but setting the import locale to English (Canada) should work as well.
Comment 12 Jonny Grant 2021-01-20 23:48:00 UTC
Good evening, It is a real shame this bug was closed. It does feel like a confirmed issue as it works as normal in Google Sheets, and is easy to reproduce.

Google Sheets doesn't suffer this issue. Sheets also retains the formatting entered correctly. Can you confirm if you can reproduce the same?

Jul-19-2019	
10 Aug 2019

=A1-A2
-22	Days different


We get CSV files from all over the world, USA, Japan, UK, France

In the case of the USA, they often (but not always) put the month before the day, and then end as /19 or /2019 or with hyphen.

There's no reason for us to need to force anything if it can be auto detected

It's a bit strange for us to need to import into Google Sheets, and then export before loading into Calc.

Probably this column forcing on CSV load was only introduced in 7.x which hasn't made it into Ubuntu stable LTS yet. Sorry I am not that up to date.