4 ms·
CSV are a headache. Like the article says, RFC4180 doesn't necessarily represent the real world. However sometimes you just have to reject things that aren't s
by slg 12y ago
CSV are a headache. Like the article says, RFC4180 doesn't necessarily represent the real world. However sometimes you just have to reject things that aren't spec.
Not too long ago I was struggling with one of these CSV issues and received some good advice from Hans Passant [1] on a Stack Overflow question pertaining to my problem (emphasis mine):
"It is pretty important that you don't try to fix it. That will make you responsible for bad data for a long time. Reject the file for being improperly formatted. If they hassle you about it then point out that it is not RFC-4180 compatible. There's another programmer somewhere that can easily fix this."
It makes perfect sense in hindsight. If you accept a malformed CSV file, people will expect you to accept any malformed data that has a CSV extension. You are taking on a lot of extra responsibility to cover for the lack of work by another programmer. Odds are they can make a change to fix the problem that takes a fraction of the time it would take you work around it. You just have to raise the issue.
I realize that rejecting bad files isn't really possible in every circumstance. But I have a feeling it is an option more times than you might initially think.
[1] - http://stackoverflow.com/users/17034/hans-passant http://stackoverflow.com/users/17034/hans-passant
- barrkel 12y agoOn the other hand, the ability to handle all kinds of input can be a chief selling point of your product. In my current job, the most common "invalid" CSV format we get is .xlsx files. So I wrote an .xlsx parser (way, way faster than Apache POI). Another interesting hiccup to consider is CSV inside individual fields - i.e. recursive CSV. There are various ways to handle this, but in my company's line of business the usual route is to duplicate that line once per CSV element found in the field. Likely the next invalid format we'll have to parse is PDFs containing tables...
- mschuster91 12y ago> Likely the next invalid format we'll have to parse is PDFs containing tables... cough people doing e-invoicing with pdf's...
- Mister_Snuggles 12y ago> Likely the next invalid format we'll have to parse is PDFs containing tables... And after that you will have to parse PDFs containing scans (as images, not text) of pages containing tables...
- muteh 12y agoI've never really got my head round RFCs, but 4180 is only informational, not a standard. I have used exactly your argument before though, and will again. Have also been on the other side and needed to convert horribly inconsistent data to fit it.
- userbinator 12y agoIt's not a standard but it should very well be one, in my opinion. Whenever someone has to process CSV I always point to it to make them aware of it, that a lot of subtle points like escaping have already been defined to be done in one way. There is no good reason to NOT follow RFC4180 if you want to produce/consume CSV.
- mholt 12y agoThis is good advice. It's the philosophy adopted by Papa Parse, http://papaparse.com http://papaparse.com - try to gracefully handle malformed CSV and report all errors so they're obvious and actually helpful by telling the user where the syntax error is at. Trying to compensate for bad CSV format will more likely cause headaches and confusion rather than clarity. It can also discourage the need for CSV writers to be rigorous about their output formatting.
- brongondwana 12y agoYeah, that's a great idea if you can. I was pulling data from a medical system that I knew full well I would not be able to get changes into for YEARS (and I got to meet the vendor, who was working on a shiny new XML export system - I wonder if that has quoting issues too - it wasn't released by the time I finished working the project) So I wound up writing perl that knew enough to fix all the common problems with the source data, and emailed me any odd lines it couldn't cope with, so I could go in and update the regular expressions. It kinda sucked, but the end result was better antibiotic coverage for a bunch of people. Worst case of a line it really couldn't handle was that person didn't get the benefit of an expert system checking that they didn't have doubled-up medicines, which is no worse than they would have had without this system.
- deleted 12y ago[deleted]