Why Excel does NOT support RFC 4180 standard for CSV?

Question

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.

Usually Microsoft propose two solutions to this problem. The first one is to clear the check box 'Use system settings' in advanced options and to set 'Decimal separator' to a point (dot), and 'Thousands separator' to a comma, save the CSV file, and set the check box 'Use system settings'. Doing this every time is certainly not the best. Furthermore, your decimal values are using the dot, which is not necessarily what you want.

The second solution is the worst, that is, changing in the control panel, in regional settings, the List separator to comma. This affects the behavior of all programs on your machine and will have unpredictable effects.

The best approach would be to ask Excel to support the standard, so that the file would be

Fred,"12,4",276,"Hello World!"

Answer 1

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.

Answer 2

Not meaning to be a stick in the mud, but as you have used the word "standard" a couple of times here to describe MS Excel's non-compliance, I wish to point out the following paragraph from RFC4180 (the bolding of the text is my own addition):

This memo provides information for the Internet community.  It does

not specify an Internet standard of any kind.  Distribution of this

   memo is unlimited.

https://tools.ietf.org/html/rfc4180

I do NOT have any inside information at all, but I expect that the reason this is done, is simply because there are a number of European countries that use the comma (,) as the decimal character - as you mention.  Until there is an actual standard to apply, the fact that Excel is allowing you to specify which options to apply, is (in my opinion) possibly a good compromise.