What is the true difference between an Excel CSV and a standard CSV?
For example, when handling columns with line breaks inside one cell, how do they encode it differently?
It absolutely depends on what you define as "standard" CSV. As far as I'm concerned, Excel follows the rules outlined in RFC 4180, the "Common Format and MIME Type for CSV Files".
Consider a table where the first cell in the first row has two line breaks. In Excel, it would look like the following:
Now, how would Excel export this? Let's see – a text editor would display this:
Not very sophisticated. It inserts a carriage return (hex
In order to parse this correctly, a CSV parser would need to
If it didn't do that, you'd end up with something garbled like this – note that there are now four lines instead of two, because it failed to ignore the line breaks.
But, let's see what the RFC says, maybe Excel did it right?
Neat, that's exactly what Excel did. So summarizing, Excel seems to follow the recommendations of a "standard" CSV file. Given a proper CSV parser, it should be able to read Excel CSV files as well.
My understanding from the original csv implementation was that all text fields were enclosed in quotes and numbers didn't need to be. Excel doesn't do this and if you try to upload your Excel generated csv files on non-microsoft platforms they fail. This is a standard microsoft approach, ignore interoperability and focus on blindly following the rules. A similar thing happened with IE, they followed the html/css rules correctly for the padding and margin attributes and ignored the fact that every existing web page and web browser treated them differently. The result, almost every web page now has special rules for IE. I find the csv files produced by excel useless and use a column of "concatenate" functions to manually create them myself properly.