4 ms·

Given this massive list of falsehoods I have to ask: what can programmers believe about CSVs? How do you go about writing a good CSV parser given that you can'

Given this massive list of falsehoods I have to ask: what can programmers believe about CSVs? How do you go about writing a good CSV parser given that you can't assume anything about your input data? Are there examples of safe, robust CSV parsers that deal with all these falsehoods?

krapp 8y ago
That they probably contain commas used to separate values. Maybe. CSV is less a format and more an article of faith.
- dagw 8y ago
  Sure, but they're used as the decimal separator :)
- em500 8y ago
  Non-English locale software often use semicolumns (since the comma is usually the decimal separator). (Falsehood 27 - 29)
  - krapp 8y ago
    (ノಠ益ಠ)ノ彡┻━┻ <-- CSV Never mind then.
- lmkg 8y ago
  At some point I was led to the conclusion that CSV stands for Character-Separated Values. So as long as you pick one character for your delimiter and stick to it, it's a CSV. This apparently isn't actually true, but the world makes more sense if you pretend it is.
burntsushi 8y ago
The way I've dealt with it in my parser (which is influenced by how Python's standard library csv parser deals with it) is to simply not have any CSV parse errors at all. That is, you always return a parse of the data, even if it's wrong. As long as it's consistently wrong, it at least gives the user of your CSV parser a chance to read the data somehow. Normally, this isn't something I'd do, since it feels icky. But it works well in practice, precisely because CSV data can be so messy. It's often much easier to hack your way around the CSV data with a sufficiently robust parser than to demand that the source of the CSV data clean up their act.
- mrweasel 8y ago
  The CSV parser in Python actually makes least one assumption, that should hold true, but doesn't. The delimiter isn't always a single character. I've had to deal with CSV files containing financial transactions, they used "," as a decimal separator. Fine that perfectly normal, and the same standard used here in Denmark. But what to do with the delimiter then.... that can't be a comma, so rather than making it a ; or something similar, the made the delimiter ", " that's a comma followed by a space. That doesn't actually work for most CSV parsers, they just assume that the space is part of the data, and the value is split in kroner/øre (dollar/cents). The result is that you gain an additional field and need to trim all data.... Oh and headers doesn't match.
  - burntsushi 8y ago
    Sure. Given a choice between a CSV parser that supported multiple character delimiters and one that didn't, sure you'd choose the one with better support for your use case. But given the choice between a CSV parser that gives you an invalid parse and a CSV parser that completely falls over when thrown a curveball (like a multi character delimiter), which would you pick? ;-)
jerf 8y ago
Normally, I kinda think these sorts of articles are a bit defeatist because reality isn't as bad as they suggest. When you set out to put an address widget on your web page, you are not necessarily bound to create something that can correctly ship a package to some random location in Wakanda. Usually you're just shooting for a single country, which has a de facto accepted layout which even if it is technically "wrong" the local delivery services already deal with. So there isn't a great need to worry about 80% of the "falsehoods". Software gets pretty damned expensive if every single address field has to be free of all errors of that sort, and every name field has to handle all 7 billion people in the world with equal fluidity, etc. But this is one case where I'd say the style conveys an accurate impression. The problem is basically that there is no such thing as "CSV". You can't build a parser that handles all the issues because a lot of the mistakes made during generation lead to fundamentally ambiguous file formats. In the simplest case, how do you parse: Quote, Speaker, Time I came, I saw, I conquered, Julius Caesar, 47BC A generic library can't really help with that. You're going to have to do some heuristic munging. And bear in mind this is just a simple example; when you've got megabytes of vaguely CSV-inspired ASCII octets it gets bad fast. You'll yearn for CSV files generated by someone who thinks "\n".join(",".join(str(y) for y in x) for x in [[1,2],[3,4]]) is all you need to write a CSV output function; the real world gets much more perverse than that. As file sizes increase and the errors increase, while you get an increase in the number of issues that a library may be able to help with, you also get an increase in the number of errors in which the resulting CSV is simply fundamentally ambiguous.
michaelt 8y ago
1. They're an OK format if you avoid things like multiline strings, escape strings inside escape strings, data that needs complex character encoding, things that might get parsed as dates or times, numbers that could suffer from rounding problems, lines with different numbers of entries and so on. There's actually quite a lot of stuff, in real business environments, that meets those criteria. Lots of stuff doesn't, obviously, but lots of stuff does. 2. If in doubt, most users will be happy enough if you do what Excel does. 3. A lot of this stuff is in business environments, where the end user doesn't make the purchasing decision, so if the data has to be manually formatted just so, users will often suck it up (and/or won't pay for improvements)

krapp 8y ago

That they probably contain commas used to separate values. Maybe. CSV is less a format and more an article of faith.

dagw 8y ago
Sure, but they're used as the decimal separator :)
em500 8y ago
Non-English locale software often use semicolumns (since the comma is usually the decimal separator). (Falsehood 27 - 29)
- krapp 8y ago
  (ノಠ益ಠ)ノ彡┻━┻ <-- CSV Never mind then.
lmkg 8y ago
At some point I was led to the conclusion that CSV stands for Character-Separated Values. So as long as you pick one character for your delimiter and stick to it, it's a CSV. This apparently isn't actually true, but the world makes more sense if you pretend it is.

burntsushi 8y ago

The way I've dealt with it in my parser (which is influenced by how Python's standard library csv parser deals with it) is to simply not have any CSV parse errors at all. That is, you always return a parse of the data, even if it's wrong. As long as it's consistently wrong, it at least gives the user of your CSV parser a chance to read the data somehow. Normally, this isn't something I'd do, since it feels icky. But it works well in practice, precisely because CSV data can be so messy. It's often much easier to hack your way around the CSV data with a sufficiently robust parser than to demand that the source of the CSV data clean up their act.

mrweasel 8y ago
The CSV parser in Python actually makes least one assumption, that should hold true, but doesn't. The delimiter isn't always a single character. I've had to deal with CSV files containing financial transactions, they used "," as a decimal separator. Fine that perfectly normal, and the same standard used here in Denmark. But what to do with the delimiter then.... that can't be a comma, so rather than making it a ; or something similar, the made the delimiter ", " that's a comma followed by a space. That doesn't actually work for most CSV parsers, they just assume that the space is part of the data, and the value is split in kroner/øre (dollar/cents). The result is that you gain an additional field and need to trim all data.... Oh and headers doesn't match.
- burntsushi 8y ago
  Sure. Given a choice between a CSV parser that supported multiple character delimiters and one that didn't, sure you'd choose the one with better support for your use case. But given the choice between a CSV parser that gives you an invalid parse and a CSV parser that completely falls over when thrown a curveball (like a multi character delimiter), which would you pick? ;-)

jerf 8y ago

Normally, I kinda think these sorts of articles are a bit defeatist because reality isn't as bad as they suggest. When you set out to put an address widget on your web page, you are not necessarily bound to create something that can correctly ship a package to some random location in Wakanda. Usually you're just shooting for a single country, which has a de facto accepted layout which even if it is technically "wrong" the local delivery services already deal with. So there isn't a great need to worry about 80% of the "falsehoods". Software gets pretty damned expensive if every single address field has to be free of all errors of that sort, and every name field has to handle all 7 billion people in the world with equal fluidity, etc. But this is one case where I'd say the style conveys an accurate impression. The problem is basically that there is no such thing as "CSV". You can't build a parser that handles all the issues because a lot of the mistakes made during generation lead to fundamentally ambiguous file formats. In the simplest case, how do you parse: Quote, Speaker, Time I came, I saw, I conquered, Julius Caesar, 47BC A generic library can't really help with that. You're going to have to do some heuristic munging. And bear in mind this is just a simple example; when you've got megabytes of vaguely CSV-inspired ASCII octets it gets bad fast. You'll yearn for CSV files generated by someone who thinks "\n".join(",".join(str(y) for y in x) for x in [[1,2],[3,4]]) is all you need to write a CSV output function; the real world gets much more perverse than that. As file sizes increase and the errors increase, while you get an increase in the number of issues that a library may be able to help with, you also get an increase in the number of errors in which the resulting CSV is simply fundamentally ambiguous.

michaelt 8y ago

1. They're an OK format if you avoid things like multiline strings, escape strings inside escape strings, data that needs complex character encoding, things that might get parsed as dates or times, numbers that could suffer from rounding problems, lines with different numbers of entries and so on. There's actually quite a lot of stuff, in real business environments, that meets those criteria. Lots of stuff doesn't, obviously, but lots of stuff does. 2. If in doubt, most users will be happy enough if you do what Excel does. 3. A lot of this stuff is in business environments, where the end user doesn't make the purchasing decision, so if the data has to be manually formatted just so, users will often suck it up (and/or won't pay for improvements)