I had to export old tables, I did it with csv, of course. But the correct change of dates to be "real dates" is still very cranky in Calc. A lot of changes are necessary while importing csv. And still I don't get what I need: the real date format dd.mm.yyyy to do a sorting. So I tried Excel (version 2007) - and everything worked instantly, precise and correct. How many years more will Libreoffice Calc need to be as accurate and fast as Excel? Just to be clear: I hate Microsoft Office, so this was just to find out if Excel was any better, in the version 2007, which is 17 years old ...
You have not provided anything to understand your issue. 1. What SCV data do you use? 2. What is your locale in LibreOffice (full Help->About)? 3. What settings do you set in the import dialog? 4. What are your expectations? 5. What are the actual problems you see? It *seems* as if you rely on some automatic processing, that happens to work for you in Excel, but doesn't in Calc (so my *assumption* is, that you likely have not configured anything manually). Well, it is known that for *some* cases, that is the case; but for other cases (and there are lots of those, and people complain about them constantly), Excel's automatic settings are wrong - and then Calc does a better job *providing the import settings by default* - where in Excel, people have to do very unobvious actions to get the dialog. There are pros and contras; and in general - your rant about "How many years more" is useless without anything that people can see, and *possibly* improve (if that's really something that can be improved *without* making other important workflows worse).
Well, the process was not as automatic as you think. The csv converters: integrated in Libreoffice Calc / Excel. in LibreOffice: UTF-8 didn't work because of the Umlaute (ä, ö, ü). So I had to change to "automatic", which was then put to "west european" (ISO-8859-1). All my programs are on the same computer (Windows 11, language: Swiss-German with the respective date-time templates). All columns were "standard", the two date columns I changed to Date (TMJ, nothing else to choose from like TT.MM.YYYY). After converting the csv to a format which Calc could work with, I saw that the two date columns were nontheless text entries, with a leading apostroph. In order to be able to sort by date I would have had to change every entry and delete teh leading apostroph. Which is no fun when you have large tables. In contrast: Excel would recognise the date columns and would therefore be able to sort them. So it's not a rant, but a complaint.
(In reply to grac from comment #2) > Well, the process was not as automatic as you think. > The csv converters: integrated in Libreoffice Calc / Excel. > in LibreOffice: UTF-8 didn't work because of the Umlaute (ä, ö, ü). So I had > to change to "automatic", which was then put to "west european" (ISO-8859-1). > All my programs are on the same computer (Windows 11, language: Swiss-German > with the respective date-time templates). > All columns were "standard", the two date columns I changed to Date (TMJ, > nothing else to choose from like TT.MM.YYYY). > After converting the csv to a format which Calc could work with, I saw that > the two date columns were nontheless text entries, with a leading apostroph. Please provide the rest of the missing data: a sample file, the full data from Help->About. (The "TMJ" in the date columns is likely correct, *if* the data in the CSV is like TT.MM.YYYY.)
Created attachment 200119 [details] anonymised csv-file, export from a database
Please find the anonymised csv-export, with two date rows. And here my versions info of LibreOffice Calc: Version: 25.2.0.3 (X86_64) / LibreOffice Community Build ID: e1cf4a87eb02d755bce1a01209907ea5ddc8f069 CPU threads: 8; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win Locale: de-CH (de_CH); UI: de-DE Calc: threaded
(In reply to grac from comment #5) Thanks; and do you say that importing this CSV, and marking the two date *columns* as "DMY", does not import the date columns as true dates?
Created attachment 200120 [details] A screencast
yes, this is what happened, whatever I did, the csv-conversion in LibreOffice did not import these two columns in date columns. That's why I tried Excel.
(In reply to grac from comment #8) Could you please send a screencast, as I did? Thanks.
Created attachment 200121 [details] LibreOffice: text, not date
(In reply to grac from comment #10) Thank you for the screenshot; could you please also send a screenshot of the text import dialog screenshot, with the settings that you use for the import? Thank you
Created attachment 200122 [details] Libreoffice: import csv
(In reply to grac from comment #12) Aha! You checked "Format quoted field as text" checkbox - which means, that in a CSV line like > "XXXXX";"XXXXX";"12.04.2023";"10.04.2023";"CHF";"50.00";"XXXXX";"XXXXX";"";"XXXXX";"XXXXX";"XXXXXXXXXX" the fields "12.04.2023" and "10.04.2023" are quoted -> they are treated as text, no matter how the column in general is configured.
Well, this is how it's exported from the external program called "Crealogix Paymaker". So what would you do? Work on the csv before importing to LibreOffice? In Excel I had nothing to do, it analysed it correctly as date, not text.
(In reply to grac from comment #14) You just uncheck the mentioned checkbox.
Created attachment 200123 [details] Libreoffice: still wrong sorting
Just what I did with the csv file: uncheck the respective checkbox. And see, what happens: the order function is not recognising the year 2024 (middle of the screenshot)! Still not what I want and need!
(In reply to grac from comment #17) > And see, what happens: the order function is not recognising the year 2024 > (middle of the screenshot)! I don't understand what do you mean. What actions you do, that don't work for you? Are you sorting the data? How?
ok, sorry. Now the sorting of the "Termin" column works correctly. So thanks for your help - case closed from my side.