How to Convert CSV to JSON
JSON (JavaScript Object Notation) is a modern file format for storing and exchanging data and plays an important role in using and processing structured information. So it’s often necessary to convert a CSV file to JSON. There are two non-atomic data types supported by JSON: arrays (ordered lists of values) and objects. Objects are unordered collections of key-values-pairs, where the keys are strings. Each key should be unique within its object.
Go to the command line solution
Using a GUI: Tablecruncher
Tablecruncher (free and open source) provides an export function to easily achieve this task. A CSV file can be either stored as an array of arrays, where each line is represented as an array. Or it is stored as an array of objects, where each line is an object with the header names as the key and the cells from the line being the values. But let’s look at an example to better understand the difference.
Export CSV file with headers to JSON
We’re using a simple file to show how easy it is to convert a CSV file to JSON. Please look at the header row, where there are header names “Name”, “Value” and “Category”.
With the command “File > Export JSON …” you’re storing this CSV file as an array of objects because the header rows have been activated. (The JSON file shown below has been reformatted to highlight the structure of the data. Tablecruncher creates files that are not pretty-printed.)
Export CSV file without headers to JSON
If your CSV file has no header row or the headers should be treated as normal data, you’re deactivating the header row. Now our demo file looks like this:
When we now export that CSV file to JSON, we’ll get a JSON file that’s an array of arrays. (Again I’ve reformatted the JSON to show the structure.)
Using the command line: Miller
Optionally: Install Miller
Go to the releases page for Miller on Github and install the tool for your system. There are binaries for Mac, Linux and Windows. Even FreeBSD is covered.
Convert a CSV file to JSON as an array of objects
Use this command on the command line to convert the CSV file test.csv to JSON:
mlr --icsv --ojson cat test.csv > test.json
--icsv tells the tool that we’re having a CSV file for input, while --ojson says we want the output in JSON format.
For our example CSV, we get this JSON:
[
{
"Name": "First",
"Value": 42,
"Category": "A"
},
{
"Name": "Second",
"Value": 99,
"Category": "B"
},
{
"Name": "Third",
"Value": 1,
"Category": "A"
},
{
"Name": "Fourth",
"Value": 1234,
"Category": "C"
}
]
Miller is a really mighty tool to handle CSV and JSON files. If you’re often have to deal with these kind of files and are an experienced command-line user, you should look into it.
Subscribe now to my newsletter to get more real-world insights into data handling, CSV quirks, and tooling tips:
