For me,
Microsoft® Excel® para Microsoft 365 MSO (versión 2312 compilación 16.0.17126.20126) de 64 bits
sorts in the very same order than LibreOffice.
3 Likes
Actually, no they haven’t, LibreOffice is, without a doubt, one of the worst file types I have ever come across in my 30 year book-keeping career! Try sorting calendar dates, for example. Excel manages, but LibreOffice falls flat on its face every time. Unfortunately, my office thinks that LibreOffice is the"bees knees" and I’m outvoted!
If your dates are actually date values and not just text strings in some ugly date “convention” like “month/day/year” or even worse, then they sort perfectly fine. In both Excel and LibreOffice Calc.
The notion of asking ANY bank to change their output is hilarious! What are people thinking? The fact that people would make suggestions like this is a symptom of something seriously wrong in the Open/LibreOffice back-end. Actually, the problems of this type in Open/LibreOffice are the result of an extremely old foundation. The innards haven’t changed since it first appeared as StarOffice about 30 years ago, regardless of the spiffy-looking appearance. Many Askers sign off with “Maybe a change will happen with the next release . . .” Well, listen up, sports fans: the innards never change; they’re as primitive and clumsy as they were 30 years ago. Look at the calendar tools: selecting “Canada” still produces a Hebrew calendar! Open/LibreOffice has never heard of ISO8601 calendar dates which have been standard in data-processing since 1988! And importing csv files still asks about “special numbers”, a concept useful only to drug-addicted wierdos with OCD. In 2024 it’s not unreasonable to expect a data tool like Calc to be able to import a CSV text file into an SQL or DBF format in one click. Is anybody in the Calc department even listening?!
No it doesn’t. Look what OO/LO does when trying to change calendar dates format (theoretically a trivial change). It works up to the 12th day of the month when it decides that “days” are now “months”, and then the output morphs:
This behavior has been unchanged for at least 25 years, possibly since Oracle fired all the original developers and donated the source to charity.
PLEASE STOP TELLING BULLSHIT …especially when you can’t even spot the difference between “Canada” and “Kannada”!
(Kannada - Wikipedia)
1 Like
I agree with you that asking a bank to change anything is hilarious. Personally I know two people who worked for major banks, who were tasked by senior staff at the bank to make changes to their system only to find that the same senior staff blocked any changes being made. In both cases they left and found work where they could actually be productive. If you think the innards of Libre/Open Office are old I suspect, but don’t know for sure, that the back office processing in banks is still using 60’s programs written in Cobol running on IBM/ICL mainframe emulators. They daren’t touch it because there are no longer any Cobol programmers around and so they have no idea how it works.
I disagree about the idea that Calc should be able to import a .csv file and correctly assign all variables with a single click. Everything in a .csv file is stored as plain text. Calc has a number of special character types so how is it supposed to know which special character type to assign to each variable. It gives you the option to tell it what variables are. If you ignore that choice it will import everything as plain text and then a date column will not sort correctly. If, however, you tell it that a column contains dates then Calc converts the plain text to date format correctly and it will then sort correctly.
Well I have obviously no idea how you got to that state. My bank exports data in .csv format with what are supposed to be dates in the first column. When I open that with Calc it gives me a panel where I can change whatever defaults it has selected. The top of each column defaults to “Standard”. If I don’t alter anything it treats everything as text and the first column does not behave like normal dates. If, however, I change the selection at the top of the first column to one of the date formats, in my case D/M/Y, then it behaves perfectly correctly.
Karolus, I’m thinking you’re perhaps becoming a bit deranged, that you might not know about Hebrew calendars? This is what you get in LibreOffice when you select Canada as region:
Correct me if I’m wrong, but these sure look like Hebrew calendar dates, my friend.
It seems youre the ONE who’s deranged:
From your screenshot I dont see Canada … but I see English(USA) where it makes perfectly sense to provide …(additional to the stupid US-Format M/D/Y)… also jewish Calendars for jewish citizens!
btw. I’m not your friend!
As you might clearly see that is not English (Canada) but English (USA), which has formats of two calendars defined.
There you are. A mixture of date values and text strings. If that is the result of an import of a CSV file then in the import dialog set the first column to type Date of order DMY (or maybe switch the import locale to en-GB / English (UK)), otherwise if you are working or importing in an en-US locale only MDY dates are recognized as such and 13/01/2023 is not a valid M/D/Y date.
To post-process such erroneous import see this FAQ.
1 Like
… behave. Thanks.
EarnestAl-
Excel and LO are built on different code foundations. They try to mimic each other’s functions but the results are not always similar. Calendar dates being one glaring example!
" btw. I’m not your friend!"
karolus, my friend, I’m trying very hard to not descend to your level of insulting and boorish behavior, like one might expect from 5th grade students in the Special Ed department. How does it look now with “English Canada” selected? Are we getting anywhere?
These settings work OK in Excel because Excel has a more advanced foundation, and LO is built on the totally out-dated Star Office foundation. It makes no difference what you do in OO/LO because the underlying basics are primitive and unfinished. I’m still not sure why LO thinks that the USA is a Hebrew nation requiring a Hebrew calendar. That’s a real puzzler!
No, because applying a display format does not change the underlying data, i.e. the wrong text strings that look like dates but aren’t date values stay text strings. You need to either change the data as already hinted by pointing to the FAQ in Calc does not sort an imported .csv format bank statements correctly - #38 by erAck or reimporting the CSV as also already hinted.
If you want to continue with that meme and ignore all advice given, why still waste people’s time here?
You could read and understand comments given here. As I wrote earlier in Calc does not sort an imported .csv format bank statements correctly - #37 by erAck, the English-US locale offers display formats for two calendars, Gregorian and Jewish. There’s nothing requiring it.
csv is not a file format. csv is mere convention.
Two valid csv lines from bank accounts, same information, different data:
12/31/2023,"text",123.98 (US English style, comma separated, point decimal, MDY date)
31.12.2023;"text";123,98 (German style, semicolon, comma decimal, DMY date)
Calc can import anything with least possible effort.
In most cases you do not need to change the individual column settings. In most cases, the following 2 settings take care of everything.
- Always check “detect special numbers”. This is a non-option. It should not even exist.
- Choose the right locale based on comma decimal or point decimals. In case of point decimals, choose “English (USA)” if there are M/D/Y dates. Choose any other English if the statement has D/M/Y dates.
After import, check menu:View>“Highlight Values”. Now all your dates and currencies should be highlighted in blue, indicating that they are correct numeric cell values.
No. In 2024 we have a lot of better tools than using csv, especially when it comes to using dates. And without external knowledge of the origin no software can tell what 10/11/12 actually is. Main problem of csv is not containing any information on used charsets, language settings etc.
.
My version is, you/somebody imported this with US-settings M/D/Y wich fails for Month>12, so this input is kept as string (after the other had a wrong import).
IMHO even this you got wrong. Oracle bought the company wich had converted the former StarOffice to an open source licence and changed the name to OpenOffice.
Did your csv really contained ISO-dates? I don’t think so…
I completely agree with you Villeroy. If I import without selecting “Detect special numbers”, then select some cells in the first column and go to “Format cells” they all show as “Text”. If I import after selecting “Detect special numbers”, then select some cells in the first column and go to “Format cells” they all show as “Date”. Allowing them to import as “Text” and then changing the format of the cells to “Date” in Calc does not work correctly but getting the import process to format them as “Date”, either by changing type at the head of the column or by “Detect special numbers” does work. Once they are correctly formatted then Calc treats them correctly, e.g. in a sort process. I have absolutely no idea what JJJoseph is doing. I get a strong feeling that neither does he.
I agree with you Wanderer that .csv is not a very good format but it is what my bank provides so I don’t really have a choice.
1 Like