6 ms·

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

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.

leeoniya 12y ago
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 https://github.com/WebReflection/JSONH [2] http://nickgravgaard.com/elastictabstops/ http://nickgravgaard.com/elastictabstops/
- burntsushi 12y ago
  Ironically enough, I've implemented elastic tabstops in Rust: https://github.com/BurntSushi/tabwriter https://github.com/BurntSushi/tabwriter and they are used in xsv! (See `xsv table`.)
- rcfox 12y ago
  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.
  - burntsushi 12y ago
    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"}
    - deleted 12y ago
      [deleted]
  - leeoniya 12y ago
    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"] ]
    - rcfox 12y ago
      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`.
      - leeoniya 12y ago
        all of the above are valid json. especially the last simple variation where you literally just have a 2-level nested array.
- ibrahima 12y ago
  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-using-emacs/ https://cxwangyi.wordpress.com/2014/05/09/editing-csv-files-...
valevk 12y ago
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.
- slm_HN 12y ago
  >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.
  - UnoriginalGuy 12y ago
    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.
    - mcintyre1994 12y ago
      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.
steveklabnik 12y ago
"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.
- valevk 12y ago
  CSV RFC: http://tools.ietf.org/html/rfc4180 http://tools.ietf.org/html/rfc4180
  - tomjakubowski 12y ago
    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.
  - deleted 12y ago
    [deleted]
  - steveklabnik 12y ago
    Doesn't matter. Nobody follows it :/
- UnoriginalGuy 12y ago
  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 http://en.wikipedia.org/wiki/Comma#Comma_variants
- deleted 12y ago
  [deleted]
ahoge 12y ago
> 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 http://en.wikipedia.org/wiki/ASCII#ASCII_control_code_chart
- alexvoda 12y ago
  We need the equivalent of CommonMark. CommonCSV anyone?
  - burntsushi 12y ago
    We have that already, RFC 4180: https://tools.ietf.org/html/rfc4180 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.
    - ahoge 12y ago
      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.
      - shared4you 12y ago
        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.

leeoniya 12y ago

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 https://github.com/WebReflection/JSONH [2] http://nickgravgaard.com/elastictabstops/ http://nickgravgaard.com/elastictabstops/

burntsushi 12y ago
Ironically enough, I've implemented elastic tabstops in Rust: https://github.com/BurntSushi/tabwriter https://github.com/BurntSushi/tabwriter and they are used in xsv! (See `xsv table`.)
rcfox 12y ago
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.
- burntsushi 12y ago
  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"}
  - deleted 12y ago
    [deleted]
- leeoniya 12y ago
  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"] ]
  - rcfox 12y ago
    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`.
    - leeoniya 12y ago
      all of the above are valid json. especially the last simple variation where you literally just have a 2-level nested array.
ibrahima 12y ago
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-using-emacs/ https://cxwangyi.wordpress.com/2014/05/09/editing-csv-files-...

valevk 12y ago

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.

slm_HN 12y ago
>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.
- UnoriginalGuy 12y ago
  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.
  - mcintyre1994 12y ago
    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.

steveklabnik 12y ago

"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.

valevk 12y ago
CSV RFC: http://tools.ietf.org/html/rfc4180 http://tools.ietf.org/html/rfc4180
- tomjakubowski 12y ago
  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.
- deleted 12y ago
  [deleted]
- steveklabnik 12y ago
  Doesn't matter. Nobody follows it :/
UnoriginalGuy 12y ago
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 http://en.wikipedia.org/wiki/Comma#Comma_variants
deleted 12y ago
[deleted]

ahoge 12y ago

> 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 http://en.wikipedia.org/wiki/ASCII#ASCII_control_code_chart

alexvoda 12y ago
We need the equivalent of CommonMark. CommonCSV anyone?
- burntsushi 12y ago
  We have that already, RFC 4180: https://tools.ietf.org/html/rfc4180 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.
  - ahoge 12y ago
    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.
    - shared4you 12y ago
      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.