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)
> 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 ?
Created attachment 154686 [details] text import date And have you tried to select the type via context menu?
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
Created attachment 154689 [details] test.csv with greyed out
[Automated Action] NeedInfo-To-Unconfirmed
(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.
(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?
(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.
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
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
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.
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.