CSV to JSON using jq

Question

If you have a csv dataset like this:

name, age, gender
john, 20, male
jane, 30, female
bob, 25, male

Can you get to this:

[ {"name": "john", "age": 20, "gender": "male"},
  {"name": "jane", "age": 30, "gender": "female"},
  {"name": "bob", "age": 25, "gender": "male"} ]

using only jq?

I found this article which shows what I'm trying to do, but it uses a 'manual' mapping of the header fields to the values. I don't need/want to rename the header fields and have quite a few of them. I would also not want to have to change a script/command every time the layout changes.

Is it possible to dynamically extract the headers and then combine them with the values with a jq one-liner?

Answer 1

In short - yes, except maybe for the one-liner bit.

jq is often well-suited to text wrangling, and this is especially true of versions with regex support. With regex support, for example, the trimming required by the given problem statement is trivial.

Since jq 1.5rc1 includes regex support and has been available since Jan 1, 2015, the following program assumes a version of jq 1.5; if you wish to make it work with jq 1.4, then see the two "For jq 1.4" comments.

Please also note that this program does not handle CSV in all its generality and complexity. (For a similar approach that does handle CSV more generally, see https://github.com/stedolan/jq/wiki/Cookbook#convert-a-csv-file-with-headers-to-json)

# objectify/1 takes an array of string values as inputs, converts # numeric values to numbers, and packages the results into an object # with keys specified by the "headers" array def objectify(headers):   # For jq 1.4, replace the following line by: def tonumberq: .;   def tonumberq: tonumber? // .;   . as $in   | reduce range(0; headers|length) as $i ({}; .[headers[$i]] = ($in[$i] | tonumberq) );  def csv2table:   # For jq 1.4, replace the following line by:  def trim: .;   def trim: sub("^ +";"") |  sub(" +$";"");   split("\n") | map( split(",") | map(trim) );  def csv2json:   csv2table   | .[0] as $headers   | reduce (.[1:][] | select(length > 0) ) as $row       ( []; . + [ $row|objectify($headers) ]);  csv2json 

Example (assuming csv.csv is the given CSV text file):

$ jq -R -s -f csv2json.jq csv.csv [   {     "name": "john",     "age": 20,     "gender": "male"   },   {     "name": "jane",     "age": 30,     "gender": "female"   },   {     "name": "bob",     "age": 25,     "gender": "male"   } ] 

Answer 2

with Miller (http://johnkerl.org/miller/doc/) is very simple. Using this input.csv file

name,age,gender
john,20,male
jane,30,female
bob,25,male

and running

mlr --c2j --jlistwrap cat input.csv

You will have

[
{ "name": "john", "age": 20, "gender": "male" }
,{ "name": "jane", "age": 30, "gender": "female" }
,{ "name": "bob", "age": 25, "gender": "male" }
]