Difference between Excel .csv and plain .csv?

Question

I am running Windows 7 and have MS Office installed. Any time I download a .csv file the "file type" line in the "save as..." dialog defaults to "Microsoft Office Excel comma separated values file". Is there actually a Microsoft specific format that is distinct from "plain" .csv?

Googling the relevant terms returns various incredibly uninformative pages such as this one. Is any information lost or gained, or anything encoded differently by using this format than by just treating a file as a .csv, conforming to the general standards?

Answer 1

Yes, there are almost certainly differences. From the top of my head: English Excel uses "," as a seperator. German locale uses ";" as a seperator, requiring an additional importing step if you want to import a csv with a comma seperator. This is not unique to german locales, roughly 1/4 to 1/3 of the world uses ";".

Also, there might be differences in how complicated strings are escaped (; and " in texts) which are probably different from program to program. This is not excels fault, since the csv "format" is not really standardised and there are uncountable numbers of programs which are rolling their own csv parser, which leads to all sorts of problems because they forgot to handle corner cases.

I once read the comment that csv is the plague of data exchange formats because it is so difficult to do right. I could not agree more, I have to deal with them on a daily basis and they are extremly annoying to work with. Open source fans will hate me for this, but I think csv is a poor choice for data exchange, even xlsx is better because it has rules which are well defined.

Answer 2

There are two things going on. The abbreviation (and suffix) "CSV" can mean character-separated values or it can mean comma-separated values. "Microsoft Office Excel comma separated values file" is a disambiguation, and means that you have a number of values in a record, with the field values separated by a comma.

The values themselves, in comma-separated value files, may contain commas if they are properly stropped (quoted). Usually, the stropping is putting a double quote around some or all of the field.

MS Excel also supports newlines in the middle of fields, again being properly stropped.