CSV to JSON with jq, or with Miller

mlr --icsv --ojson cat data.csv converts a CSV file to JSON with Miller, and it’s the one-liner to reach for, because jq can’t read CSV. jq 1.7’s @csv is an output format only: it writes CSV and has no counterpart that parses it. Miller is brew install miller or apt install miller.

For this people.csv:

id,name,city,zip
1,"Lovelace, Ada",London,02134
2,Kurt Gödel,Wien,1090
3,"Alan ""Al"" Turing",,

Miller 6.13 prints:

mlr --icsv --ojson cat people.csv

[
{
  "id": 1,
  "name": "Lovelace, Ada",
  "city": "London",
  "zip": "02134"
},
{
  "id": 2,
  "name": "Kurt Gödel",
  "city": "Wien",
  "zip": 1090
},
{
  "id": 3,
  "name": "Alan \"Al\" Turing",
  "city": "",
  "zip": ""
}
]

Miller types what it reads: 1 and 1090 become numbers, and 02134 stays a string. The converter below types these rows the same way; the one difference is the empty cells, which Miller writes as "" and the tool as null. mlr -S --icsv --ojson cat people.csv keeps every value a string.

The jq-only version, and where it breaks

With -R, jq reads each line as a string; -n with input takes the header line and inputs the rest:

jq -Rn 'input | split(",") as $h | [inputs | split(",") as $r | reduce range($h | length) as $i ({}; .[$h[$i]] = $r[$i])]' people.csv

That’s right for a file with no quoted fields. Here, the first and last rows come out wrong:

  {
    "id": "1",
    "name": "\"Lovelace",
    "city": " Ada\"",
    "zip": "London"
  },
  ...
  {
    "id": "3",
    "name": "\"Alan \"\"Al\"\" Turing\"",
    "city": "",
    "zip": ""
  }

The comma inside the quotes shifted the cells, 02134 dropped off the end without an error, and the quotes stayed in. split can’t know which commas are inside quotes, and no amount of gsub makes it a CSV parser.

Two more traps. Every value is a string, so numbers need tonumber. A file with Windows line endings leaves a \r on the last header name and the last cell of each row, {"id":"1","name\r":"Ada\r"}, until you add rtrimstr("\r") | before each split.

jq after Miller

jq is still the tool for reshaping the result. --ojsonl writes one object per line, which pipes well:

mlr --icsv --ojsonl cat people.csv | jq -c '{name, zip}'
# {"name":"Lovelace, Ada","zip":"02134"}
# {"name":"Kurt Gödel","zip":1090}
# {"name":"Alan \"Al\" Turing","zip":""}

For a semicolon file, add --ifs ';'; for tabs, use --itsv.

@csv goes the other way

mlr --icsv --ojson cat people.csv | jq -r '.[] | [.id, .name, .zip] | @csv'
# 1,"Lovelace, Ada","02134"
# 2,"Kurt Gödel",1090
# 3,"Alan ""Al"" Turing",""

@csv quotes every string, the empty one too, and leaves numbers bare. A null, as in the tool’s output, becomes an empty field with no quotes.

No Miller?

Python’s csv module is installed wherever python3 is, and it reads quoted fields correctly, keeping every value a string:

python3 -c 'import csv,json,sys; print(json.dumps(list(csv.DictReader(open(sys.argv[1], newline="", encoding="utf-8-sig"))), indent=2, ensure_ascii=False))' people.csv