5 ms·

CSV is a great example of this phenomenon. There is a "spec" RFC4180 and there are tools that generate CSV files that do not technically conform to the spec.

CSV is a great example of this phenomenon. There is a "spec" RFC4180 and there are tools that generate CSV files that do not technically conform to the spec. One such tool is Excel. For most users, Excel is doing the right thing. Blaming Excel for not handling CSV files according to the spec is passing the buck. The CSV tools that are worth using generally take great pains to work with Excel files at the cost of some ideological purity. IMHO it's a reflection of the software developers involved. The best tools, the ones we turn to time and time again, generally just work.

niftich 10y ago
In fairness, usage of CSV-like formats pre-dates the CSV RFC by almost 30 years, which was authored in 2005 specifically to try to formalize a de-facto spec: > Surprisingly, while this format is very common, it has never been formally documented. [1] [1] https://tools.ietf.org/html/rfc4180#section-1 https://tools.ietf.org/html/rfc4180#section-1
fnord123 10y ago
> For most users, Excel is doing the right thing. Blaming Excel for not handling CSV files according to the spec is passing the buck. No. Excel is wrong when it comes to CSV. Paste a Unicode string into Excel. e.g. Beijing in Simplified Chinese (北京市). Now Save As Windows CSV as beijing.csv. Close the file. Open beijing.csv. The cell now reads `___` (on Excel for Mac 2011 - maybe they deigned to fix it). Excel just outputs bad data.
- jefffan241 10y ago
  I don't know how you do it in excel but if you generate a csv with UT8 data you can add a byte order mark[0] as the first byte and it will render correctly. Once you add that, excel will open the file with utf8 encoding (if you use the utf8 byte order mark obviously). I haven't tried with other utf-* encodings. Again don't know how to tell excel how to add that though :/ I've only had to deal with arabic in generated csv's. [0] https://en.wikipedia.org/wiki/Byte_order_mark https://en.wikipedia.org/wiki/Byte_order_mark
  - tedunangst 10y ago
    Excel for Mac can't process BOM correctly.
    - jefffan241 10y ago
      I did not know that. I'm on Linux and the client is on windows so only had experience with it on those 2 OS's. Good to know.
  - mjevans 10y ago
    Byte order marks are wrong and not part of 'clean' 8-bit handling. All applications and operating systems should assume files WITHOUT a BOM are either ASCII, or the superset there of, UTF-8. Please remember to say WHY you disagree if you do.
    - joncrocks 10y ago
      Which, ASCII or UTF-8? If you have a file without a BOM, you have to pick one. As every 8 bit combination is an ASCII character of some kind, you can interpret every UTF-8 character as a combination of ASCII characters. And what you output will be different to what was input (unless you restrict yourself to single byte UTF-9 characters). Without some other way of indicating the encoding format of a file, a BOM can be a tool to indicate "It's probably encoded using UTF-X".
      - caf 10y ago
        What? No. ASCII is a 7-bit encoding: only bytes with the top-bit zero are valid ASCII, and all of those bytes represent exactly the same character in UTF-8. UTF-8 is a strict superset of ASCII and this is not by accident.
      - LukeShu 10y ago
        As caf said, ASCII is a 7-bit encoding. However, the question "which?" can still apply. There are many encodings that are a superset of ASCII. UTF-8 is a superset of ASCII, but so are ISO-8859-X (for any "X"), Windows-1252, and many others.
- alogray 10y ago
  IIRC, it was not fixed at release for Excel for Mac 2016 but there is now an option to Save As a "CSV UTF-8 (Comma Separated)".
  - rspeer 10y ago
    That's amazing. Finally. I don't suppose I should get my hopes up too much that that option is going to be more prominent than the terrible default of saving as Mac OS Roman, right? (Whoever decided that Excel on OS X should export CSVs in an obsolete encoding for Mac OS Classic must have been trying to hurt Mac users.)
  - fnord123 10y ago
    I tried it out on Office 2016/365/15.25 whatever on OS X. It works when writing data out. That's great news! And they also put it high up in the possible formats to output. Unfortunately, to open the UTF-8 file again, it doesn't work ("åŒ—äº¬å¸‚"). You have to make a new workbook and then import the csv in, specifying UTF-8 csv. It's pretty messed up but at least it's possible now.
- mnarayan01 10y ago
  You can make it work with Unicode (see e.g https://gist.github.com/mnarayan01/61bedce2b55e258d8f8c https://gist.github.com/mnarayan01/61bedce2b55e258d8f8c), it's just an enormous PITA.

niftich 10y ago

In fairness, usage of CSV-like formats pre-dates the CSV RFC by almost 30 years, which was authored in 2005 specifically to try to formalize a de-facto spec: > Surprisingly, while this format is very common, it has never been formally documented. [1] [1] https://tools.ietf.org/html/rfc4180#section-1 https://tools.ietf.org/html/rfc4180#section-1

fnord123 10y ago

> For most users, Excel is doing the right thing. Blaming Excel for not handling CSV files according to the spec is passing the buck. No. Excel is wrong when it comes to CSV. Paste a Unicode string into Excel. e.g. Beijing in Simplified Chinese (北京市). Now Save As Windows CSV as beijing.csv. Close the file. Open beijing.csv. The cell now reads `___` (on Excel for Mac 2011 - maybe they deigned to fix it). Excel just outputs bad data.

jefffan241 10y ago
I don't know how you do it in excel but if you generate a csv with UT8 data you can add a byte order mark[0] as the first byte and it will render correctly. Once you add that, excel will open the file with utf8 encoding (if you use the utf8 byte order mark obviously). I haven't tried with other utf-* encodings. Again don't know how to tell excel how to add that though :/ I've only had to deal with arabic in generated csv's. [0] https://en.wikipedia.org/wiki/Byte_order_mark https://en.wikipedia.org/wiki/Byte_order_mark
- tedunangst 10y ago
  Excel for Mac can't process BOM correctly.
  - jefffan241 10y ago
    I did not know that. I'm on Linux and the client is on windows so only had experience with it on those 2 OS's. Good to know.
- mjevans 10y ago
  Byte order marks are wrong and not part of 'clean' 8-bit handling. All applications and operating systems should assume files WITHOUT a BOM are either ASCII, or the superset there of, UTF-8. Please remember to say WHY you disagree if you do.
  - joncrocks 10y ago
    Which, ASCII or UTF-8? If you have a file without a BOM, you have to pick one. As every 8 bit combination is an ASCII character of some kind, you can interpret every UTF-8 character as a combination of ASCII characters. And what you output will be different to what was input (unless you restrict yourself to single byte UTF-9 characters). Without some other way of indicating the encoding format of a file, a BOM can be a tool to indicate "It's probably encoded using UTF-X".
    - caf 10y ago
      What? No. ASCII is a 7-bit encoding: only bytes with the top-bit zero are valid ASCII, and all of those bytes represent exactly the same character in UTF-8. UTF-8 is a strict superset of ASCII and this is not by accident.
    - LukeShu 10y ago
      As caf said, ASCII is a 7-bit encoding. However, the question "which?" can still apply. There are many encodings that are a superset of ASCII. UTF-8 is a superset of ASCII, but so are ISO-8859-X (for any "X"), Windows-1252, and many others.
alogray 10y ago
IIRC, it was not fixed at release for Excel for Mac 2016 but there is now an option to Save As a "CSV UTF-8 (Comma Separated)".
- rspeer 10y ago
  That's amazing. Finally. I don't suppose I should get my hopes up too much that that option is going to be more prominent than the terrible default of saving as Mac OS Roman, right? (Whoever decided that Excel on OS X should export CSVs in an obsolete encoding for Mac OS Classic must have been trying to hurt Mac users.)
- fnord123 10y ago
  I tried it out on Office 2016/365/15.25 whatever on OS X. It works when writing data out. That's great news! And they also put it high up in the possible formats to output. Unfortunately, to open the UTF-8 file again, it doesn't work ("åŒ—äº¬å¸‚"). You have to make a new workbook and then import the csv in, specifying UTF-8 csv. It's pretty messed up but at least it's possible now.
mnarayan01 10y ago
You can make it work with Unicode (see e.g https://gist.github.com/mnarayan01/61bedce2b55e258d8f8c https://gist.github.com/mnarayan01/61bedce2b55e258d8f8c), it's just an enormous PITA.