Why? Aren't the import settings enough?
https://support.office.com/en-us/article/Text-Import-Wizard-...
I'm not just being pedantic, it makes a big difference. If I want to change some values in a spreadsheet I should be able to just open it, change the values, save, and be certain that the document will be identical apart from the deliberate changes. This is especially important for CSV files, which are commonly used for import/export operations.
It was a fun time explaining to accounting why their report showed duplicate billing events. Try telling an accountant "Don't use Excel". The solution of course was to prefix a "'" character, making the file useless outside of spreadsheet programs.
If Excel isn't for opening CSV files why does it associate itself with that extension by default? Why does it implicitly convert the CSV file to a half-assed Excel workbook? Why is there no option to change this behavior?
I'm just saying that editing CSVs regularly, preserving the formatting, while avoiding the import/export functionality is a very specific use case. It's likely not something excel project managers care about that much either. If the use case is popular, then there's going to be an editor that does it better. If not... tough.
After years of hearing "that's not what Excel is for" I am left wondering what it is for. I have asked many "Excel pros" how to solve some problem in Excel and I honestly can't think of any satisfactory answers. Just "that's not a common use case".
The CSV behavior is just one of the annoyances. Conflating display format with data type is another. Silently changing data is another. My list of gripes with Excel is long and on this topic my fuse is short.
Semicolons are really better though, because they aren't used as a decimal separator unlike commas in most countries.
I don't know about Excel, but LibreOffice allows very easily to select which parameters to use when opening a CSV file, it works just fine.
If you're going to separate values with semicolons--which is perfectly reasonable--I feel like you probably shouldn't do that with a format called Comma Separated Values.
0x1C FS File Separator
0x1D GS Group Separator
0x1E RS Record Separator
0x1F US Unit Separator
https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text
\ is AltGr+?
] is AltGr+9
^ is Shift+^, then space
_ is Shift+-
So none of those are single keys, which means that combining them with Ctrl to write control characters becomes almost comically difficult. Not very accessible to typical users, I'd say.
P.S. Aren't .xlsx files subject to the same problem as the .csv?
It does have similar "executability" issues as CSV (and more), but 1. the formula evaluation is documented and expected behavior, 1b. there is a documented way to suppress it, and 2. programs reading it are aware that security is a thing, and either a) constrain/sandbox it (in the case of table processors such as MSOffice or LibreOffice), or b) don't execute its macros and expansions at all (in the case of libraries such as PhpExcel). Not sure about the Google Docs issue.
(As far as "common knowledge" - knowledge for manual inspection of strings is IMNSHO not required, all that's needed is that it's program-readable; in this respect, most table processors are capable of this. The point "but you can inspect CSVs by hand" comes from experience: it is also possible to inspect binaries by hand, neither of these is intuitive, both are a learned skill)
To get comma separated CSVs to show properly in Excel we have to mess around with OS language settings. CSV as a format should have died years ago, it's a shame so many apps/services only export CSV files. Many developers (mainly US/UK based) are probably not aware of how much of a headache they inflict on people in other countries by using CSV files.