How to Convert from JSON to CSV at The Command Line
How do you convert JSON values to CSV and back at the command line? I’ve done this task on many occasions and been stung by the edge cases frequently enough that it’s time for me to share my favorite tools for this conversion process. But first, some background.
Background: You Probably Want a CSV Conversion Tool
The CSV format seems simple at first glance: You have a fixed number of fields per row and each field is separated by a comma.
1997,Ford,E350\n
However, if you need to use commas in the value, then the fields must be delimited with "
:
1997,Ford,E350,"Super, luxurious truck"\n
You can use this same trick to delimit a line break, and use double double-quotes if you need to include "
in your values or headings.
1997,Ford,E350,"Go get one ""now""\n
they are going fast"\n
Things get more complex from there, and even the CSV standard does not specify how to handle all the edge cases and some formatting options are non-compatible. Wikipedia puts it this way:
The CSV file format is not fully standardized. Separating fields with commas is the foundation, but commas in the data or embedded line breaks have to be handled specially. Some implementations disallow such content while others surround the field with quotation marks, which yet again creates the need for escaping these if they are present in the data.
So although it seems like CSV conversion can be done by hand in python, or your language of choice, using an existing tool that is known to handle the edges cases well it the way to go.
With that in mind, let’s review some tools for converting from JSON to CSV at the command line.
Convert JSON to CSV via the Command Line
The simplest way to do this JSON to CSV conversion is with dasel
. dasel
is a tool for DAta SELection. Think of it as a jq
that supports selection on formats besides just JSON.
It’s easy to install (brew install dasel
), and it works great as a format converter.
[
{
"id" : 1,
"color": "red",
"value": "#f00"
},
{
"id" : 2,
"color": "green",
"value": "#0f0"
},
{
"id" : 3,
"color": "blue",
"value": "#00f"
}
]
The conversion is easy:
dasel -r json -w csv < sample.json
$ color,id,value
red,1,#f00
green,2,#0f0
blue,3,#00f
dasel
dasel
handles newlines and values containing commas as well.