Calc does not sort an imported .csv format bank statements correctly

[JJJoseph]

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!

[JJJoseph]

" 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!

[karolus]

@JJJoseph

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!

stop with all this unrelated nonsense!

Select Column A and do: ⇒Data⇒Text to Columns:

[erAck]

@JJJoseph

How does it look now with “English Canada” selected? Are we getting anywhere?

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.

@JJJoseph

It makes no difference what you do in OO/LO because the underlying basics are primitive and unfinished.

If you want to continue with that meme and ignore all advice given, why still waste people’s time here?

@JJJoseph

I’m still not sure why LO thinks that the USA is a Hebrew nation requiring a Hebrew calendar. That’s a real puzzler!

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.

[Villeroy]

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.

1. Always check “detect special numbers”. This is a non-option. It should not even exist.
2. 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.

[Wanderer]

@JJJoseph

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.

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.
.

@JJJoseph

It works up to the 12th day of the month when it decides that “days” are now “months”,

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).

@JJJoseph

possibly since Oracle fired all the original developers and donated the source to charity.

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.

@JJJoseph

ISO8601 calendar dates which have been standard in data-processing since 1988!

Did your csv really contained ISO-dates? I don’t think so…

[Kilkis]

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.