Where I work we have offices in the US, and in Europe where installing a localized version of windows will swap ',' and '.' when used as the group and decimal separator. Excel when loading a value 100,002 in the US will see one hundred thousand and two, in some parts of Europe it will see one hundred and 2 thousandths.
Character set handing can be just as bad, there is no good way to get Excel to auto open a CSV file as UTF-8 that won't break every other CSV parser in existence. The only cross platform option is ASCII. Excel will happily load your local OS encoding, likely some variant of ISO-8859, but any other encoding requires jumping through hoops.
Here are a couple of cases that I run into frequently:
* Excel is very aggressive about forcing type conversion based on its own assumptions. It will convert strings to dates or numbers, even if data is lost in the process. It will ignore quotes to convert long numeric IDs into scientific notation which truncates the ID unrecoverable.
* Excel cannot deal with quoted strings containing line breaks. It treats them as separate records and you get truncated records and partial records on separate rows.
Zip codes that lose leading zeros.
Gene names that get converted to dates. The names of some genes were recently changed because too many databases were being corrupted by researchers using Excel to read CSV files: https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
Source: dealing with CSV files people exported from Excel and the horrors that flowed from there.
It can't do this because it confuses such files with files in SYLK format, which was YET ANOTHER attempt to standardize spreadsheet data interchange, dating from the 80s.
I always view CSV as a lowest common denominator, of course more precise formats exist, but not everyone can use those. Csvs normally get the job done, but like anything else you need to know it's limitations. Something like a basic phone book should work, your scientific data, with dozens upon dozens of floating point numbers may not work.
There just aren't that many widely used applications that deal with things like this, but Google sheets seems to be the obvious one.
For me personally, I usually view things in Excel/LO but don't save, and if I need to modify anything I'll use a text editor for one-off changes, or I'll use something like Python with the pandas library for more programmatic changes. Pandas does have issues forcing timestamps to convert sometimes, but that can be easily configured. Otherwise, no problems with this method.
There's definitely a lot of friction if you want to edit .CSV's without your formatting/encoding being altered, unfortunately.
LibreOffice does a similar amount of clobbering, but in general it notifies you of any would-be clobbering and allows you to abort beforehand, which is really nice. I still avoid exporting via LibreOffice though for very sensitive situations, but it is noticeably better still.
If the user does Data > From Text/CSV > Transform > use PowerQuery to set the first row as the header, I think it's true that Excel does a good job. It provides the basics at least: configurable charset and column type detection. When the user re-saves to CSV, I'm not aware of any way to configure the output (e.g., force quotation of text content), but that's sort of ok.
The easy path—double clicking on a CSV file or using File > Open—is where all the weird auto-conversion of values happens. But other posts have covered that part.
You can't even hope to keep a file intact upon opening...
Excel has the most inexplicably horrific handling of long number strings that it is borderline unusable.
https://excel.uservoice.com/forums/304921-excel-for-windows-...
I don't really see a need for a metadata file, nor would I ever see Excel or other tools accepting it. The main problem is adoption, CSV isn't perfect but it's what we have. Now if you wrote this as a member of the Excel team at Microsoft, and then Excel had the option of exporting CSV files with a metadata file, then I'd be a bit more excited.