15 ms·
Problems with CSVs
- prodtorok 10y agoAny known web (JS) workarounds for parsing CSVs with inconsistent file encodings? Right now I am at the conclusion that you must know the encoding type before parsing, though I've come across some decent libraries that can "guess" the encoding type: https://github.com/aadsm/jschardet https://github.com/aadsm/jschardet https://github.com/sonicdoe/detect-character-encoding https://github.com/sonicdoe/detect-character-encoding
- tantalor 10y agoNot sure what you mean by "inconsistent", but in general you have to know the character set before you read the file. https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/ https://www.joelonsoftware.com/2003/10/08/the-absolute-minim... Guessing will lead to tears.
- prodtorok 10y agoA system reading & loading csv's on it's own
- rspeer 10y agoIf it's a mix of UTF-8 and a single-byte encoding, you can guess, because UTF-8 is nice enough to be obvious when you guessed wrong. The command-line version of ftfy [1] can handle a distinct encoding for each line with a near-zero rate of false positives. (No guarantees the output will be correct, especially if you read it as the wrong single-byte encoding initially, but it will be no worse than what you put in, because it won't replace correct text with incorrect text.) There is no JS version of ftfy, and there won't be until JS can represent all Unicode characters without surrogates. [1] https://github.com/LuminosoInsight/python-ftfy https://github.com/LuminosoInsight/python-ftfy
- pjc50 10y agoIf you load an arbitary file from the filesystem, it won't tell you, so guessing is the only possible approach.
- astrodust 10y agoCSV is less a file format and more a hypothetical construct like "peace on Earth".
- deleted 10y ago[deleted]
- ORioN63 10y agoThat's not a bad thought. Maybe RFC4180 shouldn't have defined csv, but another similar alternative.
- aaronblohowiak 10y agoIt doesn't so much as it says here are some things that people have done. > While there are various specifications and implementations for the CSV format (for ex. [4], [5], [6] and [7]), there is no formal specification in existence, which allows for a wide variety of interpretations of CSV files. This section documents the format that seems to be followed by most implementations:
- VLM 10y agoRFC4180 misses some rather famous practical problems, such as until very recently Excel was limited to some 16 bit number of rows, 32767 or 65536, and the bigger and richer the corporation the less likely their software is updated, so you send them a 500K line CSV dump of engineering data, they use Excel as a viewer, and they insta-file a bug report that they are paying good money for 500K lines of data but they checked and we only sent them the first 65536 lines or whatever. Bonus fun if they save the truncated file on itself permanently deleting the excess data. Or if their corporate standard DBMS is Excel, as it often is at larger companies. This has been fixed, but only in very recent versions of Excel, where I work this was a near daily issue until last year or two.
- greggyb 10y ago> This has been fixed, but only in very recent versions of Excel, where I work this was a near daily issue until last year or two. This has been fixed since Excel 2007. I don't know whether 9 years counts as very recently. I also agree with the fact that it has been an issue. Even at customers who have upgraded past Office 2007, a lot of corporate forms and standardized data exports are based on old work that was completed before the new Office file formats. Updates to these documents and exports continue to use the old file formats because people make changes to existing artifacts, which are handled in compatibility mode in Office, and no one changes the settings to export .xlsx files.
- kawsper 10y agoWe recently received a CSV from a bank where they didn't escape properly, so one of the entries was like this: "","C/O COMPANYNAME"SUBHEADLINE"","",(...) So yeah, they probably wrote this themselves, and didn't expect a valid company name to contain these characters. We ended up writing a custom parser around their horrible format, because their technical support and technical lead person we could reach couldn't understand our issues. :(
- pdimitar 10y agoWhat you describe is exactly the problem with CSV: many less tech-savvy people decide to use it to provide some sort of very minimal API (even if it's periodical manual exports sent by mail; some people consider that automation!), don't account for the possible traps (like double quotes in the value of any field), then call it a day -- more like a lifetime. Then there's inertia, there's a boss saying "we absolutely must work with these people", and... the rest is history for many of us. NOTE: I've been writing custom CSV parsers at least 10 times during my career. That makes roughly 2 such parsers every 3 years. And that comes from a guy that's not very active in his career.
- astrobe_ 10y agoNot just with CSV. I remember a similar comment about broken XML. It actually doesn't matter how compliant you are to the standard. Standards are certainly nice when both parties are willing to agree on things. But when one party is in position of strength and wants to minimize its costs or efforts, compliance to a standard won't help you.
- pdimitar 10y agoThat's the sad truth. Sometimes the thought of this makes me wanna go full Hitler on the internet -- if you can't expose a standards-compliant API, your service will be blocked by central tech authorities. We can dream, right? Still though, all giants eventually fall when they become phlegmatic. Sadly it's taking way too long.
- 10y ago
- SixSigma 10y agoSee also: XML
- rpd9803 10y agoI mean, its a bit apples or oranges.. XML's well-formed rules are, in my experience, fairly universal, hence parsing isn't as much an issue as is poorly constructed XML schemas (and schma-less xml documents as well) I know there are edge cases, but overall XML doesn't have many of the problems specified.
- bdcravens 10y agoAdditionally there aren't as many tools that generate XML output as there are for CSV
- duskwuff 10y agoXML has a specification. There are two well-defined categories of well-formed and ill-formed XML files, and there's exactly one correct way to parse the well-formed ones.
- SixSigma 10y agoYou still write a list like 1: it is well formed 2: the person who produces it will fix it when you tell them 3: the encoding is correct etc. Even ycombinator's rss feed is only well formed but not fully specified http://validator.w3.org/check?verbose=1&uri=https%3A%2F%2Fnews.ycombinator.com%2Frss http://validator.w3.org/check?verbose=1&uri=https%3A%2F%2Fne...
- ashmud 10y agoA regional (to me) real estate web site that we had to work with had a data service that output XML. It was naively implemented and managed to output malformed XML. Mostly in the form of a CSV field crammed into an XML field with non-conformant XML markup.
- wolfgang42 10y agoXML doesn't have the problems in theory, but in practice it can be just as bad. As a mildly egregious example, I had a company which wanted a data feed from us in XML format. I wrote a query to produce the relevant data out of MSSQL and sent it to them. They wrote back that I had to have a header line: <?xml version="1.0" encoding="UTF-8"> MSSQL writes SQL in UCS-2 format; I therefore sent them back a feed with the encoding declared as such, and they rejected it. They were fine with the encoding being UCS-2, but they were absolutely insistent I had to declare it as UTF-8 or they wouldn't accept it. After a lot of back-and-forth and quoting of standards at them, I finally gave up and wrote a little script to change the declared encoding so they would accept it. It's this sort of thing that makes me understand why Ode to a Shipping Label[1] exists. [1]: http://imgur.com/4J7Il0m http://imgur.com/4J7Il0m
- Walkman 10y agoOne of the most common mistakes is delimiter in a field. E.g. comma as thousand separator...
- daturkel 10y agoParticularly problematic with this is that a human cannot necessarily determine with 100% confidence where the error is. If you have a CSV where every row has 5 entries, and you come upon this row: > 100,1,200,300,10,100 You suspect that an in-field comma is causing a split where there shouldn't be, but who's to say which number got split up? 1200 into 1,200, or was it 200300 to 200,300, or 10100 to 10,100?
- deleted 10y ago[deleted]
- justinsaccount 10y ago> Excel is a good tool for working with CSVs > Excel is an OK tool for working with CSVs > Excel can losslessly save CSVs it opens Seriously. It's amazing how fast excel can corrupt a simple csv file.
- bdcravens 10y agoI've had odd bugs show up in a CSV import process, where it was a numeric string (think account numbers) that had leading zeros. Turned out the imported CSV had been cleaned by moving records around in Excel and saving as CSV (which of course saw the account numbers as numerics and conveniently truncated the zeros)
- VLM 10y agoExcel thinks anything vaguely scientific notation-ish is a float, which is a nightmare if you have, for example, four hexadecimal digits 2E15 which is 11797 in decimal but excel will helpfuly "fix" it to 2 million billion (depending on your locale of billion LOL) That was a contrived example but I've seen Excel get very confused by foreign postal codes involving digits surrounding a letter "E" or apartment number columns for example "1E"
- tomlock 10y agoExcel enthusiastically and automatically types oh-so-many things. It'll take dates and assume they are in american MM/DD/YYYY in some fields and then treat dates in the same column like 13/01/1986 as strings. That exact problem I had when working with a company who had a stocked item with the code "060E2". When people don't know scientific notation the result is bewildering!!!
- LoSboccacc 10y agoexcel doesn't use comma by default as separator on comma separated value files, enouh said.
- tinus_hn 10y ago
- kodfodrasz 10y agoI love the fact that if I want to work with CSV from C# I need to use a VisualBasic namespace: Microsoft.VisualBasic.FileIO.TextFieldParser see: http://stackoverflow.com/questions/3507498/reading-csv-files-using-c-sharp http://stackoverflow.com/questions/3507498/reading-csv-files...
- teilo 10y agoHaving developed on a system that had to import and collate insurance claims data from multiple providers, I've come to dread CSVs for all of these reasons.
- vortico 10y agoStop this horrible genre now! As discussed in the recent video programming post (https://news.ycombinator.com/item?id=13259686 https://news.ycombinator.com/item?id=13259686), this neither helps the beginner because it is unnecessarily overwhelming, nor the expert because he has already read https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180 and is aware of these problems with bad CSV parsers/emitters. In the intermediate case, it could be useful for someone maintaining a CSV parser or emitter who is not aware of a few of these points and wants 100% compatibility with all CSV parsers/emitters in existence, but the list style is confusing grammatically and offers no counterexamples or suggestions for improvement. With that said, if you are interested in using CSV for encoding and decoding in your own environment, I suggest a new file standard called μTSV. 1. All μTSV files are UTF-8. 2. All values are delimited by "\t", and all lines must end with "\n" (including the last one). 3. If you want to use a "\t" or "\n" character in your value, tough luck---use JSON.
- deleted 10y ago[deleted]
- joshuata 10y agoMany of these problems could be avoided if we used the character codes for unit (31) and record (30) separator. This would avoid any problems with tabs or line feeds, as well as better matching the intended semantics of ASCII/UTF-8
- kedean 10y agoThat's suggested often, and the obvious rebuttal is that CSV is intended to be easily edited and relatively human-friendly. The unit/record characters are not on most(all?) keyboards, so editing the file in a text editor becomes pretty much impossible.
- stouset 10y agoAs a counterpoint, nobody seems to be editing CSV files by hand any more. It appears at this point to be an interchange format between different spreadsheets, row-oriented databases, etc.
- libria 10y agoThis should be titled "What Vendors Should Account For When Publishing CSVs". If someone claims they're exporting a file in X format: * X should be a known standard OR * they should provide a client parser for X OR * they should provide a grammar or list of rules for parsing X This rant is focused in the wrong direction. It should go without saying that someone should deliver what they claim to. If CSV means different things to different people, that's fine too, but the burden should be on the exporter to define what it means to them. I'll accept a retitling of "Bullshit Developers Shouldn't Have to Question About a Common File Format".
- nommm-nommm 10y ago>All CSVs are readable with Excel >Excel is a good tool for working with CSVs >Excel is an OK tool for working with CSVs Also See: Excel: Error when CSV file starts with “I” and “D” https://support.microsoft.com/en-us/kb/323626 https://support.microsoft.com/en-us/kb/323626 HN discussion: https://news.ycombinator.com/item?id=12041210 https://news.ycombinator.com/item?id=12041210
- mnarayan01 10y agoThis has a lot of good stuff in it, but I think it's hurt by including stuff where you just have to make an assumption, E.g. I can interpret the following: "Some\tText" in at least three different non-ridiculous ways, even assuming the encoding is known. It's worth keeping this in mind, but intertwining it with other tractable assumptions feels like it hides some of the super-useful nuggets contained within. That said, I'll admit the dividing line is fairly indistinct, plus it's still a great list.
- dwe3000 10y agoI think many of the problems he lists are because, as he properly notes, a great many people believe "ALL" or "NEVER" are appropriate words to use when considering CSV files (most of his points use one or the other). But I think it is in all fairness to point out that parsing CSV files for value is a lot better than free form text, even if it's nowhere close to XML.
- sbov 10y ago43. This list matters for all applications that parse or create CSVs.
- TallGuyShort 10y agoIt's literally impossible for CSV to be a useful format if you cannot make any of these assumptions. Every field with a distinct encoding? Really? The focus needs to be on making CSVs about which you can reasonably assume a lot of these things so that you can actually write software that processes them.
- chc 10y agoIs there anyone out there who actually believes all CSVs are UTF-16? That's such a weird idea that I have no idea how somebody would arrive at it. That's what always gets me about this genre of list — I often find myself scratching my head, trying to figure out if I've missed something or if they really listed that thing as a falsehood programmers believe.
- elastic_church 10y ago"I'm a programmers and thats an oddly specific thing I've never thought about"
- VLM 10y agoPossibly its asymmetric political power at its finest but I am aware of at least one "business intelligence" application that outputs its CSV files consistently as UTF-16BE (not LE) I'm not even sure what planet its from, much less the application, I just get stuck importing what they feel like sending me. I don't know why everything that upstream sends me is in UTF-16BE format, its just what they do. Perhaps they hate me, or my employer. I donno. Perhaps its an intelligence test to cut down on support costs, you must be this smart or smarter to mess with our data or go away.
- leesalminen 10y ago> Perhaps its an intelligence test to cut down on support costs, you must be this smart or smarter to mess with our data or go away. I like this design choice. I'll keep it in mind ;)
- pjc50 10y agoWindows MFC developers end up believing that all strings are UCS-2.
- Pxtl 10y agoSomebody who does a lot of Microsoft stuff? MS has a lot of CSV tools (excel) and tends to treat "unicode" and "utf-16" as synonyms.
- rch 10y agoLast time CSVs came up I was downvoted without comment for mentioning HDF5. I know the CLI tools could use some improvement, and a vim plugin would be nice, but are there other concrete objections?
- chc 10y agoI don't exactly understand why you feel compelled to mention it. Is HDF5 a kind of CSV? If so, what is there to recommend it in particular?
- dsp1234 10y agoIf the requirement is to read CSV files from a third party, then suggesting HDF5 isn't helpful. If the requirement is to write CSV files to send to a third party, then suggesting HDF5 isn't helpful. So this limits the usefulness of mentioning HDF5 to when you are both the producer and consumer of the data, but that's the easy case because then you can choose anything up to and including strongly typed binary formats.
- rch 10y agoThanks - that's fair criticism.
- afarrell 10y agoOne thing to remember if your company offers a customer-facing "export to CSV" button: The format of that CSV is part of your public API and changes to it can cause pain for your customers. Don't be caught off-guard if they've built a workflow around "download this CSV, put it into this excel spreadsheet and run this VBscript on it" or "download this CSV into this iPython notebook and feed it to FOO".
- DonHopkins 10y agoFalsehood: You can safely name your first column "ID". https://support.microsoft.com/en-us/kb/323626 https://support.microsoft.com/en-us/kb/323626 https://news.ycombinator.com/item?id=12041210 https://news.ycombinator.com/item?id=12041210
- IanCal 10y agoHah, I've spent years munging broken csvs and this one hit me on our first public data release. I still maintain my release was fine and I blame excel ;)
- donatj 10y agoI question the title change from "Falsehoods Programmers Believe About CSVs" It's not really a list of problems as much as incorrect things people think and simply reading that title alongside the list without the page title/description would make no sense at all to the reader. I guess the HN gods know best though.
- amyjess 10y agoI remember at my last job, mentioning something about saving a Google Sheet in CSV format, and my boss immediately said "I'm morally opposed to CSV." He then told me about TSV, and since then I've come to accept it as a superior format. With the kind of data I dealt with at my job, we never encountered tabs within a column, but commas within columns were very common, so TSV was perfect for us. A lot of what I was doing was ad-hoc processing of various sheets (nothing that was really production-grade), so a lot of time I'd just write code to read in a file and split each line on \t, and that was good enough. And maybe I'd throw out any lines that began with #, but that was about it for processing. Actually, we did have a TSV parser in our codebase, but my boss told me that it has problems and he never liked it and that I should just roll my own inline with my code.
- justinclift 10y agoIs probably time someone did a follow up RFC, specifying further useful things. eg: * how to encode binary data * how to encode NULL's (for transport between databases) * how to specify encoding * (etc)
- KC8ZKF 10y agoASCII defines a unit separator, and a record separator. I wish we would just bite the bullet and start using this 50 year old solution. Modern editors/spreadsheets could insert the characters without troubling the user too much, and show visible glyphs when that make sense. https://news.ycombinator.com/item?id=7474600 https://news.ycombinator.com/item?id=7474600