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

A love letter to the CSV format

github.com

Post reply on HN
Print view View original

61–70 of 711 posts

Re: A love letter to the CSV format

#61

Post by mbreese »

mjevans wrote: post #28
andrepd wrote: post #24

Earlier quoted context omitted.

That would be solved by using the ASCII control chars Record Separator / Unit Separator! I don't get how this is not widely used as standard.

The _entire_ point of a CSV file is that it's fully human readable and write-able. The characters you mention could be used in a custom delimiter variant of the format, but at that point it's back to a binary machine format.

And that’s why I tend to use tab delimited files more… when viewed with invisible characters shown, it’s pretty clear to read/write separate fields and have an easier to parse format.

This, of course, assumes that your input doesn’t include tabs or newlines… because then you’re still stuck with the same problem, just with a different delimiter.

Re: A love letter to the CSV format

#62

Post by inglor_cz »

sakjur wrote: post #60
inglor_cz wrote: post #45

"the controversial ex-post RFC 4180" I looked at the RFC. What is controversial about it?

Look at how it handles escaping of special characters and particularly new lines (RFC 4180 doesn’t guarantee that a new line is a new record) and how it’s written in 2005 yet still doesn’t handle unicode other than via a comment about ”other character sets”.

"how it’s written in 2005 yet still doesn’t handle unicode other than via a throwaway comment about ”other character sets”"

Yeah, you are spot on with this one (cries in Czech, which used to be encoded in several various ways).

Re: A love letter to the CSV format

#63

Post by koolba »

mjw_byrne wrote: post #14

CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma at byte 1000000. This has (at least) two annoying consequences: 1. It's tricky to parallelise processing of CSV. 2. A small amount of data corruption can have a big impact on the readability of a file (one missing or extra quote can bugger the whole thing up).…

JSON serialized without extra white space with one line per record is superior to CSV.

If you want CSV-ish, enforce an array of strings for each record. Or go further with actual objects and non-string types.

You can even jump to an arbitrary point and then seek till you see an actual new line as it’s always a record boundary.

It’s not that CSV is an invalid format. It’s that libraries and tools to parse CSV tend to suck. Whereas JSON is the lingua franca of data.

Re: A love letter to the CSV format

#64

Post by roelschroeven »

tengwar2 wrote: post #7

I'm not really sure why "Excel hates CSV". I import into Excel all the time. I'm sure the functionality could be expanded, but it seems to work fine. The bit of the process I would like improved is nothing to do with CSV - it's that the exporting programs sometimes rearrange the order of fields, and you have to accommodate that in Excel after the import. But since you can have named columns in Excel (make the data in…

One problem is that Excel uses locale settings for parsing CSV files (and, to be fair, other text files). So if you're in e.g. Europe and you've configured Excel to use commas as decimal separators, Excel imports numbers with decimals (with points as decimal separator) as text. Or it thinks the point is a thousands separator. I forgot exactly which one of those incorrect options it chooses.

I don't know what they were thinking, using a UI setting for parsing an interchange format.

There's a way around, IIRC, with the "From text / csv" command, but that looses a lot of the convenience of double-clicking a CSV file in Explorer or whatever to open it in Excel.

Re: A love letter to the CSV format

#65

Post by hajile »

The argument against JSON isn't very compelling. Adding a name to every field as they do in their strawman example isn't necessary.

Compare this CSV

    field1,field2,fieldN
    "value (0,0)","value (0,1)","value (0,n)"
    "value (1,0)","value (1,1)","value (1,n)"
    "value (2,0)","value (2,1)","value (2,n)"
To the directly-equivalent JSON

    [["field1","field2","fieldN"],
     ["value (0,0)","value (0,1)","value (0,n)"],
     ["value (1,0)","value (1,1)","value (1,n)"],
     ["value (2,0)","value (2,1)","value (2,n)"]]
The JSON version is only marginally bigger (just a few brackets), but those brackets represent the ability to be either simple or complex. This matters because you wind up with terrible ad-hoc nesting in CSV ranging from entries using query string syntax to some entirely custom arrangement.

    person,val2,val3,valN
    fname=john&lname=doe&age=55&children=[jill|jim|joey],v2,v3,vN
And in these cases, JSON's objects are WAY better.

Because CSV is so simple, it's common for them to avoid using a parsing/encoding library. Over the years, I've run into this particular kind of issue a bunch.

    //outputs `val1,val2,unexpected,comma,valN` which has one too many items
    ["val1", "val2", "unexpected,comma", "valN"].join(',')
JSON parsers will not only output the expected values every time, but your language likely uses one of the super-efficient SIMD-based parsers under the surface (probably faster than what you are doing with your custom CSV parser).

Another point is standardization. Does that .csv file use commas, spaces, semicolons, pipes, etc? Does it use CR,LF, or CRLF? Does it allow escaping quotations? Does it allow quotations to escape commas? Is it utf-8, UCS-2, or something different? JSON doesn't have these issues because these are all laid out in the spec.

JSON is typed. Sure, it's not a LOT of types, but 6 types is better than none.

While JSON isn't perfect (I'd love to see an official updated spec with some additional features), it's generally better than CSV in my experience.

Re: A love letter to the CSV format

#66

Post by 0cf8612b2e1e »

fragmede wrote: post #54
mjw_byrne wrote: post #14

CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma at byte 1000000. This has (at least) two annoying consequences: 1. It's tricky to parallelise processing of CSV. 2. A small amount of data corruption can have a big impact on the readability of a file (one missing or extra quote can bugger the whole thing up).…

I want to push Sqlite as a data interchange format! it has the benefit of being well defined, and can store binary data, like images for product pictures inside the database. not a good idea if you're trying to serve users behind a web app, but as interchange, better than a zip file with filenames that have to be "relinked".

One very minor problem is that you max out storing blobs of 2GB(? I think, maybe 4GB). Granted few will hit this, but this limit did kill one previous data transfer idea of mine.

Re: A love letter to the CSV format

#68

Post by boricj »

I've recently written a library at work to run visitors on data models bound to data sets. One of these visitors is a CSV serializer that dumps a collection as a CSV document.

I've just checked and strings are escaped using the same mechanism for JSON, with backslashes. I should've double-checked against RFC 4180, but thankfully that mechanism isn't currently triggered anywhere for CSV (it's used for log exportation and no data for these triggers that code path). I've also checked the code from other teams and it's just handwritten C++ stream statements inside a loop that doesn't even try to escape data. It also happens to be fine for the same reason (log exportation).

I've also written serializers for JSON, BSON and YAML and they actually output spec-compliant documents, because there's only one spec to pay attention to. CSV isn't a specification, it's a bunch of loosely-related formats that look similar at a glance. There's a reason why fleshed-out CSV parsers usually have a ton of knobs to deal with all the dialects out there (and I've almost added my own by accident), that's simply not a thing for properly specified file formats.

Re: A love letter to the CSV format

#69

Post by taeric »

mjw_byrne wrote: post #14

CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma at byte 1000000. This has (at least) two annoying consequences: 1. It's tricky to parallelise processing of CSV. 2. A small amount of data corruption can have a big impact on the readability of a file (one missing or extra quote can bugger the whole thing up).…

Reminds me of a fatal flaw of yaml. Turns out truncating a yaml file doesn't make it invalid. Which can lead to some rather non-obvious failures.

Re: A love letter to the CSV format

#70

Post by meemo »

Quick question while we’re on the topic of CSV files: is there a command-line tool you’d recommend for handling CSV files that are malformed, corrupted, or use unexpected encodings?

My experience with CSVs is mostly limited to personal projects, and I generally find the format very convenient. That said, I occasionally (about once a year) run into issues that are tricky to resolve.

Post reply on HN