10 ms·

CSVs are the worst and only option for data exchange in 99% of the cases. Most CSVs travel through excel at some point in their lives, so we need to talk about

CSVs are the worst and only option for data exchange in 99% of the cases. Most CSVs travel through excel at some point in their lives, so we need to talk about excel: Excel neither reads nor writes UTF-8 CSV files without jumping through hoops. Excel saves files in different character sets on MacOS and Windows. Excel tries to interpret fields with digits as numbers, stripping off leading 0's, etc. (think serial numbers or whatever with leading 0's) Excel will try and interpret date fields and write them out in a different format. Outside of Excel: There is no way to know what character set a CSV file is encoded in. You can only guess. UTF BOM - sometimes there, sometimes not. If the file is malformed but still opens in Excel it is a bug in your CSV parser, and not a problem with the producer of the file (so they will tell you) Most CSV libraries are extremely buggy and cannot generate CSV to spec. Most CSV libraries are extremely buggy and cannot read CSV that meets spec. Many programmers think CSV is easy and do not use a library go generate or read CSV producing even worse results than their language's buggy CSV library. The spec is just a suggestion Nobody reads the spec That's just the tip of the iceberg

fctorial 6y ago
> Most CSV libraries are extremely buggy Examples? (Of libraries and bugs)
- VWWHFSfQ 6y ago
  op is being very hyperbolic. most widely used and supported csv libraries are not nearly as deficient as op claims.
- maerF0x0 6y ago
  golang builtin doesnt handle UTF BOM well . have to preprocess the io.Reader
  - slrz 6y ago
    Seems reasonable to me: this way, you don't have to duplicate the BOM stripping in the decoders of every single format you want to support. It's also more general as there are other commonly-needed transformations that have an even weaker case for explicit support in the CSV library (charset conversion, dropping other bogus bytes, ...).
    - maerF0x0 6y ago
      it's a stark difference compared to, say, https://www.papaparse.com/ https://www.papaparse.com/ in javascript which pretty much does anything it can to parse a file
felixfbecker 6y ago
There's a spec?
- saurik 6y ago
  If nothing else there is RFC4180 (which is the spec that backs the mime type).
glogla 6y ago
Yes, I experienced a lot of pain while working with CSVs in UTF-16, turns out some libraries can't handle it. Also, CSVs don't really scale because parsing generic CSV that may have quote and line-end inside a field is inherently single-threaded, so once you get to multi-gigabyte CSVs, and things start to go slow, there's nothing you can do.
masklinn 6y ago
> Excel saves files in different character sets on MacOS and Windows. Doesn't excel also use different separators depending on the locale? I recall once it would create semicolon-separated files if the locale uses `,` as decimal separator, and will put commas in your file in that case.
- formerly_proven 6y ago
  Yes it does. Excel does not generate valid CSV (as per RFC4180) on e.g. German computers.
- BlueTemplar 6y ago
  This is why I switched to TSV. The risks are too high. (Yes, I know about ASCII delimiters, but IMHO /t and /n are good enough for numbers-first files, especially considering how most keyboards don't feature the aforementioned delimiters, and hardly anyone has even heard of them.) https://ronaldduncan.wordpress.com/2009/10/31/text-file-formats-ascii-delimited-text-not-csv-or-tab-delimited-text/ https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...
  - throw0101a 6y ago
    With commas one uses "CSV" as the file extension, and with tabs presumably TSV. Is there a file extension and MIME type for ASCII-delimited files? ASV?
    - masklinn 6y ago
      CSVs are ascii-delimited. Do you mean files using US as field separator?
      - formerly_proven 6y ago
        .gop?
      - throw0101a 6y ago
        Yes. If I want to use the 0x1c to 0x1f in a delimited file, how would I signal by file extension (and/or MIME type) that is what is happening?
gruez 6y ago
>Excel neither reads nor writes UTF-8 CSV files without jumping through hoops. It's literally as simple as selecting "csv utf-8" when saving. When opening excel also handles it automatically if it has BOM.
- MickerNews 6y ago
  It shouldn't need a BOM. Besides, UTF-8 doesn't need a BOM at all.
  - gruez 6y ago
    It doesn't, but only if you assume all text is utf-8. You can't do that on windows because there's plenty of legacy files that use encoding like windows-1252 which are incompatible with utf-8.
    - BlueTemplar 6y ago
      Start by converting them to UTF-8. As a metaphor : would you want to work with files that can only be opened properly in, say, Lotus 1-2-3 ?
    - MickerNews 6y ago
      The time to drop that support is long passed. UTF-8 has won, the value of pandering to these ancient codepages versus the cost of choking and displaying mojibake just because someone dared to add a kanji character to a CSV should be clear by now.
      - gruez 6y ago
        And what happens to all the legacy office versions out there, or all the legacy csvs floating around? What you described works fine on a rolling release distro, but would be unacceptable for enterprise software. The current approach (opt in utf-8 with BOM to disambiguate between utf-8 and non-utf8) files is the best option that doesn't piss off their customers.
      - 6y ago
Spooky23 6y ago
The worst sin of Excel is the import dialog, which doesn’t scroll on MacOS and if updated since the last revision in 1997 would eliminate most issues.
codeulike 6y ago
Good point about Excel, there's not much point discussing CSVs and leaving Excel out of the discussion
BlueTemplar 6y ago
UTF-8 or GTFO. (Without BOM, you heretic.) Please tell me that you're talking about Excel 2003 ?
- chrismorgan 6y ago
  Microsoft Office worships the concept of backwards compatibility. Outlook renders HTML emails with a buggy and incomplete implementation of HTML 3.2 that has had approximately two changes in the last twenty years (one was to support high DPI, the other I can’t remember). There was that time they switched to using Trident (IE) in Outlook 2000–2003, but they switched back to MSO (Word) in 2007 for terrible reasons (“uh… security, yes, security”; their explanation was atrocious and never made a lick of sense to me; the original article talking about it went down a few years ago, if I recall correctly from last time I tried to find it). Excel sticks with known-bad and known-stupid behaviour because people depend on it. I can’t think of an example off-hand, but they literally have help pages saying “if you encounter this specific bug, here’s how to work around it”, because writing that help page was easier and/or safer than actually fixing the bug. A few months ago, scientists even renamed some human genes to stop MS Excel from misreading them as dates: https://news.ycombinator.com/item?id=24070385 https://news.ycombinator.com/item?id=24070385
  - BlueTemplar 6y ago
    Backwards compatibility doesn't have to mean to give up on forward compatibility ! (How do browsers deal with that ?)
  - SahAssar 6y ago
    Excel even implemented lotus 1-2-3 bugs to ensure compatibility: see 1900-02-29 https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2007/bug-1900-feb-29th-is-a-valid-date-for-ms-excel/682400e0-708e-4dd7-aa22-d72bcbd16443 https://answers.microsoft.com/en-us/msoffice/forum/msoffice_...
jasode 6y ago
>Excel tries to interpret fields with digits as numbers, stripping off leading 0's, etc. (think serial numbers or whatever with leading 0's) Fyi for those unaware... many other tools other than MS Excel also remove leading zeros and interpret as number. Examples of users asking questions on how to preserve them in other software: - Google Sheets issue with leading zeros: https://webapps.stackexchange.com/questions/120835/importdata-in-google-spreadsheet-and-keep-the-leading-zero https://webapps.stackexchange.com/questions/120835/importdat... - LibreOffice: https://ask.libreoffice.org/en/question/246295/how-can-i-import-a-csv-in-calc-with-the-leading-zeros-that-are-in-the-csv-file/ https://ask.libreoffice.org/en/question/246295/how-can-i-imp... - Python Pandas import csv issue with leading zeros: https://stackoverflow.com/questions/13250046/how-to-keep-leading-zeros-in-a-column-when-reading-csv-with-pandas https://stackoverflow.com/questions/13250046/how-to-keep-lea... - R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-csv-file-removes-leading-zeros https://stackoverflow.com/questions/31411119/r-reading-in-cs... - Tableau: https://community.tableau.com/s/question/0D54T00000C5i2iSAB/how-can-i-import-numbers-with-leading-zeros https://community.tableau.com/s/question/0D54T00000C5i2iSAB/... Everybody complains about the misinterpretation of leading zeros and blame Microsoft Excel but for some reason, the programmers of the other non-Excel software have deliberately coded the same default behavior.
- masklinn 6y ago
  > for some reason The reason is Excel.
  - jasode 6y ago
    >The reason is Excel. I've thought about that and it seems plausible but I'm not fully convinced. As another example, Google Sheets does not blindly copy Excel behavior of converting scientific gene names into a date. Excerpt from paper[1]: > To date, there is no way to permanently deactivate automatic conversion to dates in MS Excel and other spreadsheet software such as LibreOffice Calc or Apache OpenOffice Calc. We note, however, that the spreadsheet program Google Sheets did not convert any gene names to dates or numbers when typed or pasted; notably, when these sheets were later reopened with Excel, LibreOffice Calc or OpenOffice Calc, gene symbols such as SEPT1 and MARCH1 were protected from date conversion. There are several Excel behaviors that alternative software don't copy. But for some reason, the leading zero removal in csv import has stuck as a default even though it has repeated complaints and generates very similar Q&A in other software packages. (Why copy a behavior that makes you have to answer identical frustrated questions from users wanting to preserve leading zeros?!? Do programmers of LibreOffice/Google/Pandas all like inflicting pain on their users?!?) Maybe another reason is that other industries or workflows depend on leading zero removal but we never hear from them praising it. Therefore, we're distorted by a biased subset that only complains loudly about it. [1] https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...
tobyhinloopen 6y ago
I’m just here like “there is a spec for CSV?”
bluedino 6y ago
I don’t know how many times I’ve dealt with broken .CSV files that someone ram through Excel or Access
justinclift 6y ago
> CSVs are the worst and only option for data exchange in 99% of the cases. If it helps, for database data (eg not spreadsheet / excel), SQLite is a good alternative single-file format.
Ayesh 6y ago
So true! I wrote a few parsing tools for a client, and pretty much every problem we had was due to BOM in excel-edited CSVs.

fctorial 6y ago

> Most CSV libraries are extremely buggy Examples? (Of libraries and bugs)

VWWHFSfQ 6y ago
op is being very hyperbolic. most widely used and supported csv libraries are not nearly as deficient as op claims.
maerF0x0 6y ago
golang builtin doesnt handle UTF BOM well . have to preprocess the io.Reader
- slrz 6y ago
  Seems reasonable to me: this way, you don't have to duplicate the BOM stripping in the decoders of every single format you want to support. It's also more general as there are other commonly-needed transformations that have an even weaker case for explicit support in the CSV library (charset conversion, dropping other bogus bytes, ...).
  - maerF0x0 6y ago
    it's a stark difference compared to, say, https://www.papaparse.com/ https://www.papaparse.com/ in javascript which pretty much does anything it can to parse a file

felixfbecker 6y ago

There's a spec?

saurik 6y ago
If nothing else there is RFC4180 (which is the spec that backs the mime type).

glogla 6y ago

Yes, I experienced a lot of pain while working with CSVs in UTF-16, turns out some libraries can't handle it. Also, CSVs don't really scale because parsing generic CSV that may have quote and line-end inside a field is inherently single-threaded, so once you get to multi-gigabyte CSVs, and things start to go slow, there's nothing you can do.

masklinn 6y ago

> Excel saves files in different character sets on MacOS and Windows. Doesn't excel also use different separators depending on the locale? I recall once it would create semicolon-separated files if the locale uses `,` as decimal separator, and will put commas in your file in that case.

formerly_proven 6y ago
Yes it does. Excel does not generate valid CSV (as per RFC4180) on e.g. German computers.
BlueTemplar 6y ago
This is why I switched to TSV. The risks are too high. (Yes, I know about ASCII delimiters, but IMHO /t and /n are good enough for numbers-first files, especially considering how most keyboards don't feature the aforementioned delimiters, and hardly anyone has even heard of them.) https://ronaldduncan.wordpress.com/2009/10/31/text-file-formats-ascii-delimited-text-not-csv-or-tab-delimited-text/ https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...
- throw0101a 6y ago
  With commas one uses "CSV" as the file extension, and with tabs presumably TSV. Is there a file extension and MIME type for ASCII-delimited files? ASV?
  - masklinn 6y ago
    CSVs are ascii-delimited. Do you mean files using US as field separator?
    - formerly_proven 6y ago
      .gop?
    - throw0101a 6y ago
      Yes. If I want to use the 0x1c to 0x1f in a delimited file, how would I signal by file extension (and/or MIME type) that is what is happening?

gruez 6y ago

>Excel neither reads nor writes UTF-8 CSV files without jumping through hoops. It's literally as simple as selecting "csv utf-8" when saving. When opening excel also handles it automatically if it has BOM.

MickerNews 6y ago
It shouldn't need a BOM. Besides, UTF-8 doesn't need a BOM at all.
- gruez 6y ago
  It doesn't, but only if you assume all text is utf-8. You can't do that on windows because there's plenty of legacy files that use encoding like windows-1252 which are incompatible with utf-8.
  - BlueTemplar 6y ago
    Start by converting them to UTF-8. As a metaphor : would you want to work with files that can only be opened properly in, say, Lotus 1-2-3 ?
  - MickerNews 6y ago
    The time to drop that support is long passed. UTF-8 has won, the value of pandering to these ancient codepages versus the cost of choking and displaying mojibake just because someone dared to add a kanji character to a CSV should be clear by now.
    - gruez 6y ago
      And what happens to all the legacy office versions out there, or all the legacy csvs floating around? What you described works fine on a rolling release distro, but would be unacceptable for enterprise software. The current approach (opt in utf-8 with BOM to disambiguate between utf-8 and non-utf8) files is the best option that doesn't piss off their customers.
    - 6y ago

Spooky23 6y ago

The worst sin of Excel is the import dialog, which doesn’t scroll on MacOS and if updated since the last revision in 1997 would eliminate most issues.

codeulike 6y ago

Good point about Excel, there's not much point discussing CSVs and leaving Excel out of the discussion

BlueTemplar 6y ago

UTF-8 or GTFO. (Without BOM, you heretic.) Please tell me that you're talking about Excel 2003 ?

chrismorgan 6y ago
Microsoft Office worships the concept of backwards compatibility. Outlook renders HTML emails with a buggy and incomplete implementation of HTML 3.2 that has had approximately two changes in the last twenty years (one was to support high DPI, the other I can’t remember). There was that time they switched to using Trident (IE) in Outlook 2000–2003, but they switched back to MSO (Word) in 2007 for terrible reasons (“uh… security, yes, security”; their explanation was atrocious and never made a lick of sense to me; the original article talking about it went down a few years ago, if I recall correctly from last time I tried to find it). Excel sticks with known-bad and known-stupid behaviour because people depend on it. I can’t think of an example off-hand, but they literally have help pages saying “if you encounter this specific bug, here’s how to work around it”, because writing that help page was easier and/or safer than actually fixing the bug. A few months ago, scientists even renamed some human genes to stop MS Excel from misreading them as dates: https://news.ycombinator.com/item?id=24070385 https://news.ycombinator.com/item?id=24070385
- BlueTemplar 6y ago
  Backwards compatibility doesn't have to mean to give up on forward compatibility ! (How do browsers deal with that ?)
- SahAssar 6y ago
  Excel even implemented lotus 1-2-3 bugs to ensure compatibility: see 1900-02-29 https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2007/bug-1900-feb-29th-is-a-valid-date-for-ms-excel/682400e0-708e-4dd7-aa22-d72bcbd16443 https://answers.microsoft.com/en-us/msoffice/forum/msoffice_...

jasode 6y ago

>Excel tries to interpret fields with digits as numbers, stripping off leading 0's, etc. (think serial numbers or whatever with leading 0's) Fyi for those unaware... many other tools other than MS Excel also remove leading zeros and interpret as number. Examples of users asking questions on how to preserve them in other software: - Google Sheets issue with leading zeros: https://webapps.stackexchange.com/questions/120835/importdata-in-google-spreadsheet-and-keep-the-leading-zero https://webapps.stackexchange.com/questions/120835/importdat... - LibreOffice: https://ask.libreoffice.org/en/question/246295/how-can-i-import-a-csv-in-calc-with-the-leading-zeros-that-are-in-the-csv-file/ https://ask.libreoffice.org/en/question/246295/how-can-i-imp... - Python Pandas import csv issue with leading zeros: https://stackoverflow.com/questions/13250046/how-to-keep-leading-zeros-in-a-column-when-reading-csv-with-pandas https://stackoverflow.com/questions/13250046/how-to-keep-lea... - R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-csv-file-removes-leading-zeros https://stackoverflow.com/questions/31411119/r-reading-in-cs... - Tableau: https://community.tableau.com/s/question/0D54T00000C5i2iSAB/how-can-i-import-numbers-with-leading-zeros https://community.tableau.com/s/question/0D54T00000C5i2iSAB/... Everybody complains about the misinterpretation of leading zeros and blame Microsoft Excel but for some reason, the programmers of the other non-Excel software have deliberately coded the same default behavior.

masklinn 6y ago
> for some reason The reason is Excel.
- jasode 6y ago
  >The reason is Excel. I've thought about that and it seems plausible but I'm not fully convinced. As another example, Google Sheets does not blindly copy Excel behavior of converting scientific gene names into a date. Excerpt from paper[1]: > To date, there is no way to permanently deactivate automatic conversion to dates in MS Excel and other spreadsheet software such as LibreOffice Calc or Apache OpenOffice Calc. We note, however, that the spreadsheet program Google Sheets did not convert any gene names to dates or numbers when typed or pasted; notably, when these sheets were later reopened with Excel, LibreOffice Calc or OpenOffice Calc, gene symbols such as SEPT1 and MARCH1 were protected from date conversion. There are several Excel behaviors that alternative software don't copy. But for some reason, the leading zero removal in csv import has stuck as a default even though it has repeated complaints and generates very similar Q&A in other software packages. (Why copy a behavior that makes you have to answer identical frustrated questions from users wanting to preserve leading zeros?!? Do programmers of LibreOffice/Google/Pandas all like inflicting pain on their users?!?) Maybe another reason is that other industries or workflows depend on leading zero removal but we never hear from them praising it. Therefore, we're distorted by a biased subset that only complains loudly about it. [1] https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...

tobyhinloopen 6y ago

I’m just here like “there is a spec for CSV?”

bluedino 6y ago

I don’t know how many times I’ve dealt with broken .CSV files that someone ram through Excel or Access

justinclift 6y ago

> CSVs are the worst and only option for data exchange in 99% of the cases. If it helps, for database data (eg not spreadsheet / excel), SQLite is a good alternative single-file format.

Ayesh 6y ago

So true! I wrote a few parsing tools for a client, and pretty much every problem we had was due to BOM in excel-edited CSVs.