Hacker News Reader

Comment by nightcracker | Hacker News Reader

I disagree with this list of "falsehoods programmers believe".

Most of those lists describes objectively wrong assumptions programmers have about some real-world phenomenon. But in this case I'd argue that a lot of these points are simply not correctly formatted CSV files. Just because someone handed you a file with the extension .csv does not mean it's a CSV file proper, and it certainly does not mean that you have to guess at what it intends to encode without assuming any of the things on the list.

For example, "All CSVs contains a single consistent encoding". If this is not the case I'd (rightfully) reject the file as being a proper CSV file.

View on HN

You can certainly reject the file, but that's not going to make your user happy. The (sad) reality is that you have often to deal with these edge cases in software.

The user does not care that the CSV is not proper. He just wants to open it, and if it doesn't work, they will blame your program, not the source of the CSV.

And you only control the former. I have had to deal with a CSV export from a web service that was "not proper". I notified their support, but that problem was never fixed.

Luckily, the user in that case was just me. But if I was making software for someone else, blaming the right source of the problem would have not taken me anywhere.

There are different use cases. What you say is not really CSV specific and true about most data sets: you receive data sets that are not compliant to whatever spec you expect. Different projects must handle it in different ways, either making some efforts to correct errors or not.

In some cases data users wield enormous power over data submitters (e.g., many for-record safety analyses or real-time systems). If any part of the data collection process is suspect the sample is likely to get tossed. And for spec compliance, if a data set does not pass perfectly through a validator the sender will likely get some sort of a "shame on you" nastygram.

If you put a phone number form and the user put letters inside, you will just say "invalid number". You won't try to parse it.

Same for invalid CSV.

There is nothing wrong with this. You can't accept all the garbage from the user.

The real problem is when the user complains "but it works fine in Excel." If it does work elsewhere then in the user's eyes it's your app's fault that it cannot read a working file.

Yes, it does work in Excel but it's because of 40 years of various edge case fixes that you just cannot replicate in their entirety in your app.

Really what this comes down to is that the CSV "standard" is in actuality an undocumented standard with one reference implementation - Excel. If it works in Excel, it's valid CSV, if it doesn't, then it's not. It's a sad state of affairs, and probably one reason to avoid working with CSV unless absolutely necessary, but there's no getting around that fact.

This is so wrong it’s hard to figure out where to start. Excel doesn’t make proper CSVs and it’s not a reference for anything. I can change the extension of an html file to CSV and open it in excel, it’s still an html file.

Excel makes proper CSVs insofar as when you say that you allow people to import CSV files into your application, the vast vast majority will be using Excel to create / manipulate those CSV files (and if they don't, whatever tool they DO use will have made efforts to work consistently with Excel).

What any other spec actually says is irrelevant. For most non-technical users, anything Excel outputs as a CSV is a CSV file (and anything you output that doesn't work with Excel is not)

Excel is certainly a reference for what your end-users expect your software to be able to do.

The main reason you'd choose CSV is precisely that you can open it in Excel.

I use CSV (really tsv) because big data tools can open them extremely easily, because they're human readable, and because they're easy for my software stack to parse and use. I do most of my data processing for local consumption on a repl-like tool. We have a defined configuration for TSV we use internally that is lossless in terms of storing and extracting a number of complex data types. Embedded JSON with embedded separators in that JSON? No problem.

Originally the xls format was the only open format in the office family and it was actually much easier to create xls files than to deal with the stupidity of csv

They're all open now, but the spec is monstrously large and difficult to work with. Definitely not easier to do than CSV.

.xlsx is not .xls

Isn't xls a binary format? I can't imagine that's more pleasant to work in.

I take a defined format like .xls over an unspecified de facto ‘standard’ like .csv anytime. In many locales .csv files don’t even contain comma separated values because the comma is the decimal symbol already. It’s a mess, half of the time Microsoft can’t make it work either even when you only use their software.

You can use sep for that.

> Yes, it does work in Excel but it's because of 40 years of various edge case fixes that you just cannot replicate in their entirety in your app.

So given its impossible to compete totally with Excel, how do we decide which of these edge cases are worth our time to implement?

The problem is that for many products, if it can't integrate with an Excel workflow, it is dead-on-arrival.

I think his point is that it's not garbage from the user but garbage that the user needs to use.

It's not as simple as invalid input or a bad copy/paste. The user likely cannot correct the CSV.

But every piece of software that accepts csv files cannot be expected to include an editor/data Wrangler mode that will allow the user to resolve encoding errors, ambiguities and inconsistencies, nor can it be expected to resolve those problems automagically

Not only can they be, they are. Or they stop accepting CSV.

> If you put a phone number form and the user put letters inside, you will just say "invalid number". You won't try to parse it.

Wait, what? Phone numbers could always include letters. Many businesses get phone numbers that include or consist of words relevant to their business, to make them memorable. And I've known people whose personal number happens to contain a word or words, so they give the letter version out because it's easy to remember. If a phone number field doesn't include simple logic to map letters into the proper numbers, that's just lazy programming.

That's an american way of seing things (and is only a thing in very few countries), because all your letters are on the num pad. But 555-فودكا is not going to work.

And what next, do you accept exotic unicode numbers ? Letters that you casefold to something that can make to a number ?

Again, you draw the line somewhere. There is no right place for the line, it depends of your business, but there is always a line of what you accept as a user input.

Being offended won't help you on HN, and calling out people names either.

The letters all correspond to numbers. I don't think most applications with phone numbers really need to support putting 1-800-FLOWERS instead of the real number.

Wait what? There are countries that put letters into phone NUMBERS?

This is what the average phone bad looks like in the United States:

http://www.pachd.com/free-images/technology-images/phone-dia...

There is a standard, universal mapping of letters onto numbers. This allows companies to advertise their phone number as something memorable like 1-800-CASH-4-AU and everyone knows how to dial it. People don't even need to remember the mapping of letters, because they're printed on every keypad.

In the US, phone numbers started with 2 letters well into the 1960s. They were usually the first 2 letters of the street name the nearest telephone exchange was on. My grandfather's address book was full of numbers still written that way.

The numbers all have three or four letters above them and people (mostly businesses, really) will sometimes get a number that spells out a memorable mnemonic. That's all he's talking about.

Well there are the A B C and D DTMF tones not sure if those technically count as numbers and from memory they are without the ITU spec for a phone numbers.

Used to be common in the US. You'd have two letters for the exchange, then five digits for the telephone number.

You're right, but non-technical clients don't care. It's a little different to them.

With a ZIP code in the US, it consists of 5 digits that form a unique number for a geographical area with 4 more digits that represents a sub-area within the 5 digit ZIP. But there are no instances where there can be a letter. That's a hard fact. If you put 4308A-65XK as the ZIP, the post office or delivery service will not know how to deliver the mail without trying to cross reference address with possible valid ZIP codes.

Invalid CSV is murky, because CSV is a serialization format. That is, invalid CSV may not play well with a program that accepts well-formed CSV, but a human may still be able to parse it with no problem. Returning "invalid ZIP Code" makes sense. The client would understand that letters do not belong in a ZIP Code.

If you've never had to deal with a nightmare client of that sort, hats off to you.

Perhaps the problem is with the client relationship.

Why not explain to the client that you will need to put in manual or custom work to clean up the CSV or batch of CSVs and charge for the extra time writing a script or cleanup?

Literally the only time this may not apply is if you are writing a general purpose program.

You can also have clients that don't have a working OS. But you can't fix that. You need to choose a limit after which you say "no, I won't do that, get you s* together"

Well, it all depends on the money, doesn't it?

The thing is, a lot (all?) of these assumptions are not part of the CSV standard. Things you view as "garbage" might still be technically valid CSV.

Out of interest what do you regard as a valid phone number?

as defined in E.164 (https://en.wikipedia.org/wiki/E.164)

How could you possibly handle a single file that mixes and matches encodings? I'd think opening that in any software is going to result in some garbled characters.

Why not create a transformation function which uses some heuristics to take a non-compliant sort-of-CSV and turn it into a CSV.

Doing so doesn't mean that it is CSV, it just means you can parse it.

That non-compliant sort-of-CSV can be cleanly (i.e. without errors) decoded as several "dialects", producing multiple different results, of which only one (or none) will be correct.

Why would you do that?

I guess I would do it, because I could then be explicit about the differences between the two, and explicit about how I would decode something if it was indeterminate.

True, but if your job involves data transformations or migrations, then bad files, dirty data and such things are just part of the job. You deal with them. Rejecting incoming data because it isn't perfect is a path to bad customer service, and a poor reputation.

Yeah but this has nothing to do with CSV. It's just about handling user input in general.

try standard parsing, if fail try exotic features, if fails try so common hacks around common mistake, if fails just reject it as invalid data.

We all do that all the time for everything. Form input, video upload, user click in a MMO, etc.

One challenge is that rejecting, or even detecting that something is wrong is often not a simple decision to make. Parsing a CSV in an invalid encoding will usually produce "valid" but nonsensical output. Determining that that has happened when your destination schema is somewhat freeform can be a challenge.

It depends on the context. If you're pulling in data from a source whose format the user can't control, then you have to deal with the broken CSV emitter. But if you're pulling in data from Excel, you're better off telling the user to save as xlsx because Excel makes such a hash of CSVs. In Python, I've had way fewer problems using xlrd and openpyxl than I have with any of the ways you can read CSVs.

> For example, "All CSVs contains a single consistent encoding". If this is not the case I'd (rightfully) reject the file as being a proper CSV file.

"CSV" only specify the metacharacters (at best), unless it's explicitly stated that the CSV is a single consistent encoding it's no more improper than inconsistently encoded Linux paths. It's stupid and a pain in the ass to be sure, but it's not improper.

And when you don't control the entire pipeline, you will eventually hit this issue.

In fact the first falsehood programmers believe about CSVs is that it's a good serialisation, interchange (between separate systems) or human-edition format. While it's routinely used for all of those it's also absolute garbage at all of them.

>"CSV" only specify the metacharacters (at best), unless it's explicitly stated that the CSV is a single consistent encoding it's no more improper than inconsistently encoded Linux paths. It's stupid and a pain in the ass to be sure, but it's not improper.

I think it's generally agreed that a CSV file is a plain text file. And I'm not aware of any school of thought that says that valid text files may have multiple encodings. IMO, it's ridiculous to expect any software to detect and accommodate that.

I don't expect a CSV to be a plain text file, unless all the data it's encoding is plain text. CSV is a data interchange format, not text. If I open a CSV in a plain text editor and get junk out of it, I would be mildly surprised but I wouldn't immediately conclude the file is invalid or corrupt, I would at least entertain the notion that the data is correct but indisplayable.

Mixed encodings would make sense if the columns in a single CSV are coming from multiple data sources. Any data processing software that expects to work in the real world is expected to be able to handle inputs in different formats. Depending on the relationship between the parts of the processing pipeline, a trying to reconcile formatting issues before/while creating the CSV may be an objectively incorrect design decision.

>I don't expect a CSV to be a plain text file, unless all the data it's encoding is plain text.

Really? So you might write a floating point value to a "CSV" file by writing the IEEE 754 bit pattern for that value? And you would call that a valid CSV file, and would expect generic CSV parsers to properly decode that value?

That's a new one on me. I have always been under the impression that CSV files are plain text. Perhaps you should edit the Wikipedia page so it doesn't continue to give unsuspecting people the wrong information:

  In computing, a comma-separated values (CSV) file is a 
  delimited text file that uses a comma to separate values 
  (many implementations of CSV import/export tools allow 
  other separators to be used). It stores tabular data 
  (numbers and text) in plain text. [1]

[1] https://en.wikipedia.org/wiki/Comma-separated_values

> In fact the first falsehood programmers believe about CSVs is that it's a good serialisation, interchange (between separate systems) or human-edition format.

I would argue that most programmers realize CSV sucks and would rather use something structured and sane. In my experience CSV support almost always comes about because someone from another business unit (e.g., marketing) needs self-service import facilities. Those folks live in Excel and CSV becomes the de facto import format.

> Those folks live in Excel and CSV becomes the de facto import format.

Which is an error, and you'd save yourself much pain if you just worked directly with excel files. Most languages have libraries allowing that.

IME none works exactly like Excel and therefore will inevitably fail as users push more and more of it's edges and bugs on the system

Csv files don’t work like excel either. Typically all you need is a table with headings and then text and number fields.

CSV is simpler and therefore harder for Excel to screw up. Not for lack of trying: exponential values often truncated, Windows 1252 encoding, BOM, etc.

Heaven help you if whatever you're working in doesn't, though.

Then you make a wrapper program in a language that has an Excel library which e.g. takes your own data in some structured format (JSON, YAML, or whatever) and emits Excel.

> For example, "All CSVs contains a single consistent encoding". If this is not the case I'd (rightfully) reject the file as being a proper CSV file.

That's literally the only one on there I felt that way about. All the other ones can and should be accounted for in your application if it accepts arbitrary CSVs.

Encoding is a special case because it's impossible to do anything other than guess at a plaintext document's encoding. If you're getting byte code parsing errors, the code should try a few different encodings before rejecting the document.

This won't help if there's a mix of encodings in the document. If this is the case, nothing can help and you will have garbage going in.

The vast amount of entries are duplicates. Sorting those out, you're really not left with that much, and some of those are just invalid CSV's. Assuming I haven't miscounted:

There are fifteen points effectively addressing delimiters, which all sum up to "delimiters may be arbitrary byte sequences, and they should be ignored in quoted fields or if escaped".

There's nine points about encoding, three of which if violated just result in a flat out invalid file.

There's four points that effectively claim that you must parse CSV files that adhere to no known formal or informal standard, which is just silly.

These "Falsehoods programmers believe about X" articles lists all revolve around the same thing, accepting arbitrary inputs. If you can constrain your input, then great, you don't need to read the article. Or read it anyway, just so you get a better idea for exactly how to constrain it.

But if you can't, and business needs force you to allow anyone and their dog Sue to pass input into your software system, then you need to be able to handle garbage input effectively, up to and including just correcting it for them if that's possible.

The biases listed are good to check your code against if that's indeed the case.

The idea of "accepting arbitrary input" is nonsense. Arbitrary input would imply that I can feed the CSV parser a JPEG and expect some non-error result.

A CSV parser will obviously only accept CSV. That list includes a few (5?) things that might end up being common bugs in hand-rolled CSV parsers. However, most of is is just bogus.

The only possible way to handle "garbage" input is sensible errors. Of course, is someone is presenting a valid CSV variant that you do not support, then it's not garbage, but just a variant that you may wish to implement support for.

A parser that tries to continue on error would be absolutely outrageous for a business to implement. How do you think the finance department would feel about their accounting being rendered invalid because of a parser trying to be smart?

> The idea of "accepting arbitrary input" is nonsense. Arbitrary input would imply that I can feed the CSV parser a JPEG and expect some non-error result.

This is completely wrong. The idea is to sift through the input and return appropriate error codes for garbage input, while remaining flexible enough to handle malformed input that's not quite garbage. As the maxim goes, be flexible in what you accept, strict in what you hand out.

Any input that your program accepts that produces a sensible output is within the set of inputs for which your program is designed to be valid, which is a subset of all possible inputs. Arbitrary input means any possible input, beyond the set for which your program is valid, including those that causes your program to segfault or set your cat on fire.

Expecting to handle "arbitrary input data" would mean to try to accept data outside of that set. In the case of a CSV parser, it would mean parsing /dev/urandom as a CSV and producing sensible output ("sensible" defined by the users, which in your example is the dog "Sue").

What the maxim says about being flexible in what you accept is not trying to tell you that everything should be considered valid, but instead that it doesn't hurt to allow certain rules to be broken if it cannot do any harm. This could mean supporting trailing commas in JSON arrays, which while illegal is a harmless "extension".

However, it is not trying to tell you that you should accept garbage and "silently recover", trying to do something with invalid input. Doing so does more harm than good. Be flexible, not stupid.

Not surprisingly, most lists of "falsehoods" are like that.

I believe it is naturally impossible or intractable to consider every item of those lists. Rather, they are convenient checklists for consideration when you are delving into the realm of those complicated subjects. Do you reject, say, 30% of those items? That's fine. Just make sure that you are explicitly making tradeoffs and will consult the lists again when your initial tradeoffs wear off.

Dealing with real world data and applications means being liberal in what you accept.

If you're producing CSV files, then you can be more strict.

https://en.wikipedia.org/wiki/Robustness_principle

The original statement was about coverage of the breadth of the specification: if you're consuming data you need to accept everything the specification allow, but if you're generating data you should be producing as little of the specification's productions as you can.

This does not work when the format is mostly unspecified and the vast majority of what you'll find in the wild is a tire fire.

A colleague got stuck trying to parse a 1-million-lines CSV for a while, then he found out that the last line was a PHPMyAdmin timeout error. Maybe they should add "All CSV contain no error messages" to the list :)

Lol "reject" the file. The author in this story could be me and the reason we both have so much experience working with that long list of fucked up CSV files is because we don't have the power to 'reject' anything. Our client gives us the data and tells us to fuck off until we solve their problem. Professional services != consumer services in servicer/servicee power disparity.

Yeah, almost by definition if you’re dealing with crappy CSV files it’s because you’re part of a pipeline crossing organization boundaries with manual processes and you rarely have the authority or means to reject input.

This.

I mean if an API sends invalid JSON you can't parse it...

JSON is pretty extensively specified.

Saying that CSV is not is generous.

Not a standard _per se_ [0] but goes to show that some level of effort has been applied to CSV standardization.

[0]: https://tools.ietf.org/html/rfc4180

Yeah, that's the most commonly referenced standard.

Approaching it from a database perspective (eg sqlitebrowser.org), where we need to both import and export CSV's... it can be tricky.

For example, if an imported CSV file has two commas ",," that generally indicates an empty field. But, does that mean an empty string? Or does it mean we should use the default value for the field (as per database spec)?

That specific case can be handled by a user preference on the import/export dialog ("What to do with empty fields?").

Other's less so. For example, database fields can contain data types (eg images, binary blobs) that have no representation defined in the RFC. Literally any decision about how to encode them is going to be non-standard. Ugh.

There really needs to be a follow up RFC that specifies useful, practical things to tighten up the gaps.

But....

* Do you accept trailing commas?

* Do you accept multiline comments?

* Do you accept single line comments?

* Do you accept literal newlines in strings?

* Do you accept single quotes in place of double quotes?

* Do you accept integers over 8 bytes?

* Do you autodetect Unicode encoding?

* Do you accept numbers with leading zeros?

* Do you accept numbers with a leading plus sign?

* Do you accept NaN?

* Do you accept non-quoted object keys?

* Do you accept duplicate object keys?

In other words,

> A JSON parser MAY accept non-JSON forms or extensions.

https://tools.ietf.org/html/rfc7159

As the quote states, none of that would be JSON. Thus, if you have a document that contains that, it is no longer JSON. A parser may accept things that are not JSON, but that's rather irrelevant.

JSON does not have comments, support trailing comments, single quotes, etc. etc. etc.

Certainly. And some programs may accept things are not CSVs.

The vast majority of programs support things that are not CSVs. :)

(Couldn't help it.)

Why is parsing JSON a minefield then? ;) http://seriot.ch/parsing_json.php

Where is the minefield? In accepting things that are not JSON, such as trailing commas and comments? That's silly.

Everything blue and yellow in that matrix is entirely OK and should be green ("undefined" means "anything goes", and the spec specifically allows accepting more than JSON, so "should have failed" is bogus).

That leaves a few parser bugs in form of orange and red, but they're corner cases (like surrogate pairs or 10000 nested arrays) and fixable.

Because "pretty extensively specified" does not mean "fully specified and perfectly implemented".

The equivalent page for CSV would not have an end.

Fine, then call it "falsehoods programmers believe about people who try to create CSV files".

Something that's being lost here is that this article is in the same vein as the seminal "Falsehoods programmers believe about time" [0] which contains quite a few statements which describe user error that developers still have to deal with (e.x. "The system clock will always be set to the correct local time.")

0. http://infiniteundo.com/post/25326999628/falsehoods-programm...

Well, you can create a zip full of nulls, that doesn't make it a zip.

You can't deal with all the configuration of all the atoms in the universe, you need to draw a line.

If something just can't be reasonably parsed, it's fair to reject it as invalid CSV.

The problem is that your manager draws the line, not you.

Sometimes you have to, or customers will hate you for it

Reply on news.ycombinator.com