Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

So You Want to Write Your Own CSV code

tburette.github.io

Post reply on HN
Print view View original

121–127 of 127 posts

Re: So You Want to Write Your Own CSV code

#121

Post by lignuist »

zAy0LfpBZLC8mAC wrote: post #119
lignuist wrote: post #116

Earlier quoted context omitted.

I used that strategy for parsing gigabytes of CSVs containing arbitrary natural language from the web - try to get these files fixed, or figure out a grammar for gigabytes of fuzzy data... My approach never failed for me, so telling me that my strategy does not work is a strong claim, where it reliably did the job for me. Your examples are all valid, but what you are describing are theoretical attacks on the method,…

How do you know that the strategy worked reliably if you never compared the results to the results obtained using a reliable method (which you presumably didn't, because then you could just have used the reliable method)? The larger the data you have to deal with, the more likely it is that corner cases will occur in it, and the less likely that you will notice anomalies, thus the more important that you are very str…

Nitpicking much?

As I wrote above, by making sure that I use a placeholder that does not appear in the data, I make sure that it does not cause the issues you describe. And if I was wrong with that assumption, I can at least minimize the effect by choosing a very unlikely sequence as placeholder.

I really see no issue here. How do you find valid grammars for fuzzy data in practice?

Re: So You Want to Write Your Own CSV code

#123

Post by dbro »

zAy0LfpBZLC8mAC wrote: post #115
lignuist wrote: post #114

Earlier quoted context omitted.

> What if there is #COMMA, in one of the fields (but no #COMMA#)? What should happen? Since #COMMA is not #COMMA#, it gets not replaced, because it does not match. Please keep in mind, that I replied to suni's very specific question and did not try to start a discussion about general parser theory. In practice, we find a lot of files that do not respect the grammar, but still need to find a way to make the data acces…

What would happen is that you first would replace #COMMA, with #COMMA#COMMA# and then later replace that with ,COMMA# , thus garbling the data. The way to make the data accessible is to request the producer to be fixed, it's that simple. If that is completely impossible, you'll have to figure out the grammar of the data that you actually have and build a parser for that. Your suggested strategy does not work.

Usually the person parsing the CSV data doesn't have control over the way the data gets written. If he did, he would probably prefer something like protocol buffers. CSV is the lowest common denominator, so it's a useful format for exchanging data between different organizations that are producing and consuming the data.

https://github.com/dbro/csvquote is a small and fast script that can replace ambiguous separators (commas and newlines, for example) inside quoted fields, so that other text tools can work with a simple grammar. After that work is done, the ambiguous commas inside quoted fields get restored. I wrote it to use unix shell tools like cut, awk, ... with CSV files containing millions of records.

Re: So You Want to Write Your Own CSV code

#124

Post by tetha »

daigoba66 wrote: post #99
huherto wrote: post #61

CSV works for simple cases. It is trivial to parse, you shouldn't even need a library. It there are many "what ifs" like in the posted article. You probably need another format like JSON (preferably) or XML.

Off topic, but why JSON over XML? What are the technical advantages for using JSON instead of XML (and don't say anything about "human readable"). If you're consuming the data with JavaScript, I'll grant you that JSON has quite an edge. But most every language has standard libs for XML. Both are easy to parse, but XML is easier to validate given a schema definition.

If your data touches the network, conform xml parsing is an attack vector (billion laughs, external entity exploits, ...) and non-conform xml parsing ends up being a headache. Even more, the sheer absurd complexity of xml contains so much stuff, who knows how many more exploits by specification are in there.

Re: So You Want to Write Your Own CSV code

#125

Post by EvanPlaice »

gavinpc wrote: post #41

My most popular stackoverflow answer [1] includes a CSV writer and reader. Yeah, I'd clean it up a little if I were doing it now (return enumerator instead of array, etc). But people keep using it. It uses regex lookaheads to deal with quoting, so it's not 100% portable. But it's only about one page. As for the other things mentioned by the OP (BOM, encoding), those should be handled by the stream, and are not the pr…

Regex lookaheads are more efficient because you're copying everything between terminal chars at once as opposed to one char at a time.

Unnecessary string copy operations are what make the parser slow.

Re: So You Want to Write Your Own CSV code

#126

Post by zAy0LfpBZLC8mAC »

dbro wrote: post #123
zAy0LfpBZLC8mAC wrote: post #115

Earlier quoted context omitted.

What would happen is that you first would replace #COMMA, with #COMMA#COMMA# and then later replace that with ,COMMA# , thus garbling the data. The way to make the data accessible is to request the producer to be fixed, it's that simple. If that is completely impossible, you'll have to figure out the grammar of the data that you actually have and build a parser for that. Your suggested strategy does not work.

Usually the person parsing the CSV data doesn't have control over the way the data gets written. If he did, he would probably prefer something like protocol buffers. CSV is the lowest common denominator, so it's a useful format for exchanging data between different organizations that are producing and consuming the data. https://github.com/dbro/csvquote is a small and fast script that can replace ambiguous separators…

You tend to have more control over the way the data is produced than you think, and you should make use of it. It's idiotic to work around broken producers over and over and over again, each time with a high risk of introducing some bugs, instead of pushing back and getting the producer fixed once and for all. Often the problem is simply in the perception that somehow broken output is just "not quite right", and therefore nothing to make a fuss about. That's not how reliable data processing works. You have a formal grammar, and either your data conforms to it or it does not, and if it doesn't, good software should simply reject it.

Your csvquote is something completely different, though it seems like you yourself might be confused about what it actually is when you use the word "ambiguous". There is nothing ambiguous about commas and newlines in CSV fields. If it were, that would be a bug in the grammar. It just so happens that many unix shell tools cannot handle CSV files in any meaningful way, because that is not their input grammar. Now, what your csvquote actually does is that it translates between CSV and a format that is compatible with that input grammar on some level, in a reversible manner. The thing to recognize is that that format is _not_ CSV and that you are actually parsing the input according to CSV grammar, so that the translation is actually reversible. Such a conversion between formats is obviously perfectly fine - as long as you can prove that the conversion is reversible, that the round-trip is the identity function, that the processing you do on the converted data is actually isomorphic to what you conceptually want to do, and so on.

BTW, I suspect that that code would be quite a bit faster if you didn't use a function pointer in that way and/or made the functions static. I haven't tried what compilers do with it, but chances are they keep that pointer call in the inner loop, which would be terribly slow. Also, you might want to review your error checking, there are quite a few opportunities for errors to go undetected, thus silently corrupting data.

Re: So You Want to Write Your Own CSV code

#127

Post by JensRantil »

Sami_Lehtinen wrote: post #104

Parsing CSV is easier than handling XML or JSON. I do integrations as my job and most common format used is CSV because it's handy simple and reliable compared to other formats. That is exactly the reason why ini and props file are also preferred over database for data which isn't too volatile or big. Any one can open the datafile and see what's stored and what's wrong.

Have a look at `jq`: https://stedolan.github.io/jq/ It makes working with JSON a breeze.
Post reply on HN