I'm not really sure why "Excel hates CSV". I import into Excel all the time. I'm sure the functionality could be expanded, but it seems to work fine. The bit of the process I would like improved is nothing to do with CSV - it's that the exporting programs sometimes rearrange the order of fields, and you have to accommodate that in Excel after the import. But since you can have named columns in Excel (make the data in to a table), it's not a big deal.
One problem is that Excel uses locale settings for parsing CSV files (and, to be fair, other text files). So if you're in e.g. Europe and you've configured Excel to use commas as decimal separators, Excel imports numbers with decimals (with points as decimal separator) as text. Or it thinks the point is a thousands separator. I forgot exactly which one of those incorrect options it chooses.
I don't know what they were thinking, using a UI setting for parsing an interchange format.
There's a way around, IIRC, with the "From text / csv" command, but that looses a lot of the convenience of double-clicking a CSV file in Explorer or whatever to open it in Excel.
It's really bad if your header row has less columns than the data rows. You really need to do the import vs just opening the file because it's not even obvious that it's dropping data unless you know what to expect from your file.
Excel is halfway decent if you do the 'import' but not if you just doubleclick on them. It seems to have been programmed to intentionally do stupid stuff with them if you just doubleclick on them.
I agree that the default way Excel handles CSV files is terrible. Using Power Query to manage them is the way to go. But it's the general Microsoft approach to backwards compatibility so very unlikely to change now.
In the past I remember that Excel not properly handling UTF-8 encoded text in a CSV. It would treat it as raw ASCII (or possibly code page 1252). So if you opened and saved a CSV, it would corrupt any Unicode text in the file. It's possible this has been fixed in newer versions, I haven't tried in a while.
It's related to how older versions of Windows/Office handled Unicode in general.
From what I have heard, it's still an issue with Excel, although I assume that Windows may handle plain text better these days (I haven't used it in a while)
You need to write an UTF-8 BOM at the beginning (0xEF, 0xBB, 0xBF), if you want to make sure it's recognized as UTF-8.
Ugh, UTF-8 BOM. Many apps can handle UTF-8 but will try to return those bytes as content; maybe ours in 2015 too
I was on the Power Query team when we were improving the encoding sniffing. An app can scan ahead i.e. 64kB, but ultimately the user needs to just say what the encoding is. All the Power Query data import dialogs should let you specify the encoding.
UTF-8 BOM is probably not a good idea for anything other than (maybe) plain text documents. For data, many (although not all) programs should not need to care about character encoding, and if they include something such as UTF-8 BOM then it will become necessary to consider the character encoding even though it shouldn't be necessary.
I have repeatedly seen people getting the spreadsheets altered by excel, and in general a lot of troubles due to localisation reasons. Sometimes these changes can be subtle and be hard to spot until somebody tries to troubleshoot what went wrong down the line.
It works better if you click to "import the data" instead of just opening the csv file with it, and if you then choose the right data types. But having to do this everytime to make it work is really annoying, esp when you have a lot of columns, plus people can easily get confused with the data types. I have never seen that much confusion eg with macos's numbers.
