Why Excel does NOT support RFC 4180 standard for CSV?
The RFC 4180 standard states that the delimiter for CSV must be comma. Excel, however, ignores this requirement and uses semicolon in those countries where the decimal delimiter is comma. So, what in USA would be
Fred,12.4,276,“Hello World!”
in Italy, Spain, and France, the exported file contains
Fred;12,4;276;“Hello World!”
Here we have two problems: the first is that such a CSV file cannot be imported in any application that is compliant to RFC 4180 standard, the second one, is that you are forced to edit the CSV file and add “sep=;” before the first record to ensure that anybody that receive your file in a country where comma delimiters are used, be able to import it in Excel. The latter problem has a collateral effect: that instruction is Excel-specific, not part of RFC 4180 standard, and not used by any application that is compliant to RFC 4180 standard.
OK Well then why does Excel ignore quoted text numerics and strip all leading zeros contained therein without even given an option during opening the file???? It used to have a default import data which seems to be deleted in Office 2016, so you have to hack the extension for force the importer to run. If you do not then primary database keys, in my case, get puked. It should not be easier to manipulate data with SSMS then Excel, but it is. this is the kind of thing that drives folks using unix systems for interoperability absolutely crazy. Even in SSMS they deleted the forced quote option which mangles the exported CSV if the data has a comma in it. Again, this is not even a CSV file any longer per the above standard which calls for text fields to be quoted.
To add insult to injury, AFTER you open the CSV if you change the data to “Text” it keeps the excelified version of the mangled data, instead of using the ACTUAL data in the file; hence, I have Account numbers using scientific notation, and mangled primary keys. They do not even include options in excel so we can force the default datatypes to be Text instead of general, or anything for advanced options. Its just really frustrating trying to migrate from Mysql to sql server and knowing that all my intermediate edits HAVE to be done with notepad++, or sql, because Excel is useless and will mangle the data into unusable format.
If they are not going to support what the rest of the planet does with CSV files, and has been doing for 30+ years if not longer, they shouldn't claim to support CSV files. That is the point of standards, not reinventing formats. Sorry, but this has been driving me nuts for years, and just noticed the forced import to make it display correctly and have a chance of it saving legitimately isn't even a command any longer.