Show HN: Transform a CSV into a JSON and vice versa

Kudos for doing the work! :bow:

I see an appropriate beauty in your username representing an information propagation model – of modern waves in modern habitats.

I do hope that my comment about a “rut” and “little formats” doesn’t disparage the work. I try to speak for enlightenment but sometimes I fall into lamenting the darkness.

Done.

As someone who's written parsers for both CSV and jsonlines, I can assure you that you could not be further from the truth:

1. The whitespace is optional. It's just put there for illustrative purposes.

2. Whitespace in CSV can actually corrupt the data where some parsers make incompatible assumptions vs other CSV parsers. Eg space characters used before or after commas -- do you trim them or include them? Some will delimit on tabs as well as commas rather than either/or. Some handle new lines differently

3. Continuing off the previous point: new lines in CSVs, if you're following IBM's spec, should be literal new lines in the file. This breaks readability and it makes streaming CSVs more awkward (because you then break the assumption that you can read a file one line at a time). jsonlines is much cleaner (see next point).

4. Escaping is properly defined. eg how do you escape quotation marks in CSV? IBM's CSV spec states double quotes should be doubled up (eg "Hello ""world""") where as some CSV parsers prefer C-style escaping (eg "Hello \"world\"") and some CSV parsers don't handle that edge case at all. jsonlines already has those edge cases solved.

5. CSV is data type less. This causes issues when importing numbers in different parsers (eg "012345" might need to be a string but might get parsed as an integer with the leading space removed. Also should `true` be a string or a boolean? jsonlines is typed like JSON.

The entire reason I recommend jsonlines over CSV is because jsonlines has the readability of CSV while having the edge cases covered that otherwise leads to data corruption in CSV files (and believe me, I've head to deal with a lot of that over my extensive career!)

`jq` can transform CSV to JSON and vice-versa, especially for simple/naive data where simply splitting on `,` is good enough - and where you aren't too bothered by types (e.g. if you don't mind numbers ending up as strings).

First attempt is to simply read each line in as raw and split on `,` - sort of does the job of, but it isn't the array of arrays that you might expect:

    $ echo -e "foo,bar,quux\n1,2,3\n4,5,6\n7,8,9" > foo.csv

    $ jq -cR 'split(",")' foo.csv
    ["foo","bar","quux"]
    ["1","2","3"]
    ["4","5","6"]
    ["7","8","9"]

Pipe that back to `jq` in slurp mode, though:

   $ jq -R 'split(",")' foo.csv | jq -cs
   [["foo","bar","quux"],["1","2","3"],["4","5","6"],["7","8","9"]]

And if you prefer objects, this output can be combined with the csv2json recipe from the jq cookbook[0], without requiring `any-json` or any other external tool:

   $ jq -cR 'split(",")' foo.csv | jq -csf csv2json.jq
   [{"foo":1,"bar":2,"quux":3},
    {"foo":4,"bar":5,"quux":6},
    {"foo":7,"bar":8,"quux":9}]

Note that this recipe also keeps numbers as numbers!

In the reverse direction there's a builtin `@csv` format string. This can be use with the second example above to say "turn each array into a CSV row" like so:

   $ jq -R  'split(",")' foo.csv | jq -sr '.[]|@csv'
   "foo","bar","quux"
   "1","2","3"
   "4","5","6"
   "7","8","9"

And to turn the fuller structure from the third example back into CSV, you can pick out the fields, albeit this one is less friendly with quotes and doesn't spit out a header (probably doable by calling `keys` on `.[0]` only...):

    $ jq -cR 'split(",")' foo.csv | jq -csf csv2json.jq | \
    > jq -r '.[]|[.foo,.bar,.quux]|@csv'
    1,2,3
    4,5,6
    7,8,9

I don't consider myself much of a jq power user, but I am a huge admirer of its capabilities.

[0] https://github.com/stedolan/jq/wiki/Cookbook#convert-a-csv-f...

This is the kind of comment I came here for.