I ended up saving my mental heath by supporting two different formats: "RFC csv" and "Excel csv". On excel you can for example use sep=# hint on beginning of file to get delimeter work consistently. Sep annotation obviously break parsing for every other csv parser but thats why there is other format.
Also there might be other reasons too to mess up with file to get it open correctly on excel. Like date formats or adding BOM to get it recognized as utf-8 etc. (Not quite sure was BOM case with excel or was it on some other software we used to work with )
Still... Shame on Microsoft of not documenting this and perhaps other annotations that one can use for ex El.
https://support.microsoft.com/en-us/office/import-or-export-...
https://support.microsoft.com/en-us/office/text-import-wizar...
"Delimiters Select the character that separates values in your text file. If the character is not listed, select the Other check box, and then type the character in the box that contains the cursor."
Maybe they should know better their tools instead of plain double clicking and hope for the best.
I presume it's because Germany uses the comma as a decimal separator instead of a dot.
I eventually settled on just exporting Excel because I couldn't get both the encoding and separator to work at the same time.
Another fun story is that a coworker lost data, when they opened a csv, wrote data to a second sheet, and then saved it. A sane program would probably have brought up a save-as window. Excel didn't. It just discarded the second sheet.
The fact that it opens in Excel is just convenient. If Excel doesn’t work, I’d use BBEdit or one of a billion other clients that read CSV.
Far fewer clients can open Excel files. Especially 10 or 100 years in the future.
For example the web version doesn't have a dark mode. Google sheets and docs these days is more useful and feature rich than Excel.
Here are a few specific examples:
1. Editing formulas using the keyboard only is a nightmare in Excel. It often randomly throws errors and warnings when I move the cursor around (like typing parentheses or quotes first and then trying to move back to type text inside, etc.) before finishing editing.
2. Conditional formatting in Excel is so non-intuitive that I actively try to avoid it like the plague. Yet, I use it extensively in Google Sheets because it is so easy to create multiple rules there.
3. The whole copy/paste design choice in Excel is, in my opinion, weird. Firstly, there is a distinction between copying a cell and copying text: if you copy an entire cell, you cannot paste it as text in a formula or any other input area. You have to copy from the formula bar of that cell. Even for pure cell copying, the cells have to remain highlighted. If you copy a cell and then unselect it (by pressing Esc or trying to edit any cells), the copied content is lost. I'm sure there are reasons it's designed this way, but it's so irritating, and I never find any benefit.
For me personally the absolute killer feature is the bidirectional integration with BigQuery, something you won’t get that easily (if at all, correct me if I am wrong) with Excel.
Quattro Pro could easily do all of the sheets stuff but document collaboration, ignoring the fact that sheets is a Web application and Quattro Pro started on MS-DOS.