Hacker News Reader

Comment by tbrownaw | Hacker News Reader

From the "criticisms" section: You shouldn't be working with CSV data because CSV is a terrible format.

Er, what's wrong with it? Or is this a case of, people using it for things other than what it's meant for? Is there a better format for sending data between different companies using different enterprisey database systems?

My complaint about csv is that people frequently generate it manually and don't understand how to quote text fields, so they don't double any quote characters that are part of the data. Which means I have to spend time cleaning up malformed files.

View on HN

> what's wrong with [CSV]?

The encoding, line endings, and the used separators are random. Encoding/decoding behavior may be locale-dependent. E.g. if you use Excel with a de-DE locale to create a CSV file, Excel with a en-US locale won't open it. The former uses ';' as separators and the latter expects ','.

Since the format is so (seemingly) simple, there are millions of ad hoc implementations out there. Naturally, most of them are horribly broken.

Fun fact: ASCII actually reserved control characters for this stuff. 1F is the "unit separator" and 1E is the "record separator". There is even a "group separator" (1D) and a "file separator" (1C).

http://en.wikipedia.org/wiki/ASCII#ASCII_control_code_chart

We need the equivalent of CommonMark. CommonCSV anyone?

We have that already, RFC 4180: https://tools.ietf.org/html/rfc4180

The problem is adoption. Most CSV parsers accept a superset of RFC 4180, because the only thing worse than not adhering to a good spec is tripping over CSV data in the wild.

RFC 4180 does not specify the character encoding.

This isn't a problem with JSON, XML, ASN.1, Protobufs, or Thrift. Anything other than CSV, really.

Indeed it does, although not directly. From the RFC:

> TEXTDATA = %x20-21 / %x23-2B / %x2D-7E

So, it is a subset of ASCII whose hex codes must lie in the ranges given above. Basically, any printable ASCII character with the exception of 0x22 (= double quote) and 0x2C (=comma) which serve other purposes.

From "3. MIME Type Registration of text/csv":

  Common usage of CSV is US-ASCII, but other character sets defined
  by IANA for the "text" tree may be used in conjunction with the
  "charset" parameter.

Basically, you can specify that it's UTF-8 or whatever via the Content-Type response header. Setting this response header isn't something an encoder could do. A CSV parsing library also shouldn't be responsible for loading the CSV file via HTTP. In most cases, CSV files aren't directly fetched via HTTP anyways.

Anyhow, ASCII is utter nonsense. We aren't living in an ASCII world. Even the Americans don't do that. An ASCII-only format is completely useless.

>Fun fact: ASCII actually reserved control characters for this stuff. 1F is the "unit separator" and 1E is the "record separator". There is even a "group separator" (1D) and a "file separator" (1C).

Which means you can't safely use it for arbitrary data (since your records themselves could contain these separators). Most of the time that doesn't matter; sometimes it does.

You can't use these control characters in CSV either. The grammar from RFC 4180 excludes the 0x00 to 0x1F range.

I think csv is great. The problem is, as you said, that people don't use dedicated csv parsers, and create utter crap.

The other problem is Microsoft Excel. It is installed on almost any Windows machine, and automatically mapped to *.csv file endings. The first I do on Windows machines is to map _any_ text based file format to notepad++.

Excel destroys any csv file upon saving. It can't handle different decimal point formats. File separation characters are mappes to tabs. Multiline text is destroyed (not sure if true anymore, in Office 2007 it was). And don't even get me started about dates.

>Excel destroys any csv file upon saving.

This isn't exactly true, although I understand why people think it is. The problem is that when you "open" a csv file in Excel it guesses what type the fields are and sets that datatype for the column. This will frequently mangle the data when written back to a file as you noted.

However you can "import" the csv file using the wizard and that allows you to set all of the options, including datatype. Setting this to 'text' seems to prevent Excel from mangling the data.

I tend to use a combination of CSVKit, which seems plenty fast for my usage, and Excel when dealing with csv files.

Maybe there is some hidden wizard (although I've never seen it) but the fact remains that the default behaviour on a Windows machine with Office, is when you double click a CSV Excel opens it, and when you hit save Excel mangles it.

Sure, you can go through and fix every single column one by one to fix it, but defaults matter. And in this case the default Excel behaviour is horrifyingly bad.

I haven't used Excel for this in a while but as I recall the "hidden wizard" is basically file -> import from Excel itself. That said I agree that the double click behavior is pretty rubbish.

"CSV" is bad because it's not well-formed. There are tons of "CSV" parsers in the wild, and they all make reasonable, but different, choices when it comes to some behaviors. INI is the same way.

CSV RFC: http://tools.ietf.org/html/rfc4180

Existence of an RFC doesn't mean that people or tooling conform to it.

The CSV RFC doesn't specify an IETF standard, by the way.

Doesn't matter. Nobody follows it :/

Exactly.

People keep linking to the spec like that matters. The reality on the ground is that CSV has tons of different incompatible implementations or just simply terrible parsers. Even if you produce spec-correct CSV it won't work with a lot of third party's libraries or parsers.

Just ask anyone who works with CSV and lots of companies about double quotes around fields, I assure you they'll have a flag in their system to: include them, exclude them, and include them in records which contain commas only.

Plus CSV and internationalisation is a giant cluster fuck in its own right. Ever seen a U+FF0C or other comma[0] in a CSV? Yeah that is fun.

[0] http://en.wikipedia.org/wiki/Comma#Comma_variants

i think JSONH [1] is a great replacement for csv. it's not as human-readable, but since no editors support elastic tabstops [2] by default, most csvs aren't readable anyhow.

[1] https://github.com/WebReflection/JSONH

[2] http://nickgravgaard.com/elastictabstops/

Ironically enough, I've implemented elastic tabstops in Rust: https://github.com/BurntSushi/tabwriter and they are used in xsv! (See `xsv table`.)

JSONH couldn't replace CSV in the one place where it actually makes sense to use CSV: very large files.

It's possible to process a CSV file of unbounded size with a bounded amount of memory because you can just read it line-by-line. With JSON, you need to read the whole file into memory before you can get at the data.

I'm not familiar with JSONH, but with JSON, you can cheat by encoding a single "record" via JSON on a single line. :-) That way, you can read one line at a time:

    {"field": "some\\ntext with new lines"}
    {"field": "more text"}

well, there's nothing stopping you from writing a streaming json parser. that can read <x> fields at a time. you can format your input json to have whatever whitespace you need, including line breaks. the assumption is that the json will be encoded and not hand-written anyways, so the validity of the entire file isnt important since it will barf at record-level if malformed, similar to csv.

so rather than:

    [2,"col_1","col_2","a","b","c","d"]

you can write:

    [2,"col_1","col_2",
        "a","b",
        "c","d"
    ]

and it will still be valid JSON/H and line-by-line parsed. or as 'burntsushi suggested, you can wrap each record in an array to pass through a normal json parser, though this wouldn't be JSONH any longer:

    [
        ["col_1","col_2"],
        ["a","b"],
        ["c","d"]
    ]

Sure, you can write a custom parser to handle this, but then you're no longer working with JSON/JSONH. It's a subset that's nearly identical, but with an extra constraint imposed on the serialization structure. (We'll call it JSON`)

It sounds like I'm being pedantic, but if you're not your sole producer and consumer of the data you'll inevitably have to deal with someone giving you valid JSON that isn't valid JSON`.

all of the above are valid json. especially the last simple variation where you literally just have a 2-level nested array.

Err, I'm not sure if I understand [2] correctly but Emacs's csv-align-fields displays CSVs pretty nicely: https://cxwangyi.wordpress.com/2014/05/09/editing-csv-files-...

Reply on news.ycombinator.com