Converting JSON to CSV

In this article, we will delve into the process of converting JSON (JavaScript Object Notation) to CSV (Comma Separated Values). JSON and CSV are widely used data formats with diverse applications in areas such as data interchange, data storage, and data analysis. Sometimes, you may need to convert your JSON data to CSV format for easier data manipulation and analysis. We will explore three different methods for this conversion: using jq, a Perl command, and a Node.js script.

What is JSON?

JSON is a lightweight data-interchange format that is easy for humans to read and write and easy for machines to parse and generate. It is a text format that is completely language-independent but uses conventions that are familiar to programmers of the C family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. JSON is often used when data is sent from a server to a web page.

What is CSV?

CSV is a simple file format used to store tabular data, such as a spreadsheet or database. Files in the CSV format can be imported to and exported from programs that store data in tables, such as Microsoft Excel or OpenOffice Calc. CSV stands for “Comma Separated Values”.

Method 1: Using jq

jq is a lightweight and flexible command-line JSON processor. It’s like sed for JSON data – you can use it to slice and filter and map and transform structured data with the same ease that sed, awk, grep and friends let you play with text.

Installation

You can download and install jq from the official website.

Conversion

After installing jq, you can use the following command to convert JSON to CSV:

jq -r '.data.headers | [.to, .fromfull, .id, .subject, .date] | @csv' myfile.json > output.csv

In this command:

• -r is a command-line option that tells jq to output raw strings instead of JSON-encoded strings.
• .data.headers is a jq filter that selects the headers field from the data object in the JSON.
• @csv is a jq filter that formats its input as CSV.
• myfile.json is the input JSON file.
• output.csv is the output CSV file.

Method 2: Using a Perl Command

Perl is a high-level, general-purpose, interpreted, dynamic programming language. Perl was originally developed by Larry Wall in 1987 as a general-purpose Unix scripting language to make report processing easier.

Conversion

You can use the following Perl command to convert JSON to CSV:

perl -n0e '@a= $_ =~ /"to":(".*?").*?"fromfull":(".*?").*?"id":(".*?").*?"subject":(".*?").*?"date":(".*?")/gs; while (my @next_n = splice @a, 0, 4) { print join(q{,}, @next_n)."\n"}' myfile.json > output.csv

In this command:

• -n0e are command-line options that tell Perl to loop over the input file, not print by default, and execute the following code.
• The regular expression (/"to":(".*?").*?"fromfull":(".*?").*?"id":(".*?").*?"subject":(".*?").*?"date":(".*?")/gs) matches and captures the desired fields from the JSON.
• myfile.json is the input JSON file.
• output.csv is the output CSV file.

Method 3: Using a Node.js Script

Node.js is a platform built on Chrome’s JavaScript runtime for easily building fast and scalable network applications. Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient, perfect for data-intensive real-time applications that run across distributed devices.

Conversion

You can use the following Node.js script to convert JSON to CSV:

#!/usr/bin/env node
const fs = require('fs');

const json = fs.readFileSync('myfile.json');
const data = JSON.parse(json);

const csv = `${data.data.headers.to},${data.data.headers.fromfull},${data.data.headers.id},${data.data.headers.subject},${data.data.headers.date}\n`;

fs.writeFileSync('output.csv', csv);

In this script:

• fs.readFileSync('myfile.json') reads the JSON file.
• JSON.parse(json) parses the JSON data.
• The template literal (${data.data.headers.to},${data.data.headers.fromfull},${data.data.headers.id},${data.data.headers.subject},${data.data.headers.date}\n) formats the desired fields as CSV.
• fs.writeFileSync('output.csv', csv) writes the CSV data to the output file.

To run the script, use the following command:

node jsonToCsv.js

In this command, jsonToCsv.js is the name of the script file.

Conclusion

Converting JSON to CSV is a common task in data processing and analysis. Depending on your needs and the tools you have available, you can use jq, a Perl command, or a Node.js script to perform the conversion. Each method has its strengths and weaknesses, so choose the one that best fits your situation.