How to Convert JSON to CSV

While JSON is great for handling complex, nested data, CSV is the more appropriate choice when it comes to presenting data in a simple, tabular format. This article will explore the differences between these two formats and guide you through various methods for converting JSON to CSV, both manually and automatically.

Understanding the Differences Between JSON and CSV

JSON (JavaScript Object Notation) and CSV (Comma-Separated Values) are both formats used for storing and exchanging data, but they serve different purposes and have distinct structures.

• JSON: JSON is a hierarchical format that organizes data in key-value pairs, allowing for complex and nested data structures such as arrays and objects. It is widely used in web APIs and applications due to its human readable nature and its compatibility with programming languages.

  Example JSON

  [
    {
      "name": "John Doe",
      "age": 28,
      "email": "john.doe@example.com"
    },
    {
      "name": "Jane Doe",
      "age": 25,
      "email": "jane.doe@example.com"
    }
  ]

• CSV: In contrast, CSV is a flat, tabular format where data is stored in rows and columns, with each row representing a record. CSV is simpler and more compact but lacks the ability to represent nested or complex data, making it better suited for spreadsheet software and basic data processing tasks. JSON is more flexible for complex data, while CSV is ideal for simple, structured data.

  Example CSV

  name,age,email
  John Doe,28,john.doe@example.com
  Jane Doe,25,jane.doe@example.com

Converting JSON to CSV Manually

If you're dealing with a small amount of data, you can manually convert JSON to CSV using a text editor or a spreadsheet program. Here's how you can do it step by step:

1. Open a text editor: Fire up your favorite text editor to begin constructing your CSV. Common text editors include Notepad (Windows), TextEdit (Mac), and Visual Studio Code (multi-platform).
2. Create the headers: Use the keys from your JSON object (e.g., name, age, email) as headers for the CSV. Make sure to separate them with commas.
3. Create the rows: Copy the corresponding values into CSV rows, carefully ensuring each value goes in the correct column. Each row should occupy its own line.
4. Save the file: Save the file with the .csv file extension. Test that your CSV can be opened and parsed correctly using a program like Microsoft Excel or Google Sheets.

Methods for Converting JSON to CSV Automatically

While manual conversion is possible, it can be time-consuming and error-prone, especially when dealing with large or deeply nested JSON files. Fortunately, there are several automated methods to simplify the process.

Using Online Tools

One of the easiest ways to convert JSON to CSV is by using a purpose-built, online tool. This site, betterjson.com, is one such tool that—in addition to parsing, validating, and formatting your JSON—allows you to export your JSON as CSV.

To convert your JSON to CSV on betterjson.com, simply upload or paste your JSON into the JSON Editor and click the "Format" button. Once your JSON appears in the JSON Viewer, click the download button and then select the CSV option to open the CSV export modal. From there, select any of the available export options and then click the download button to download a file with the .csv extension to your device.

Combining the CSV Export Feature with JSONPath Filtering

As described in our article How to Query JSON using JSONPath, betterjson.com enables users to query or filter their JSON using JSONPath. This feature can be used to pre-process your JSON before exporting it as a CSV. For example, if you only wish to export data for a particular key within your JSON, you can write a JSONPath expression that targets that key before converting it to CSV.

Using Command Line Tools

For developers or those familiar with the command line, there are several CLI tools that allow you to convert JSON to CSV. One popular tool for this purpose is jq.

jqis a lightweight and flexible command-line JSON processor that can be used for a variety of data processing tasks. To convert your JSON to CSV withjq, first install it using a package manager that's appropriate for your operating system, such as brew for macOS. Then, once installed, simply write a jq expression tuned to your JSON data and execute it. For example, if you had the following JSON file:

[
  {
    "name": "John Doe",
    "age": 28,
    "email": "john.doe@example.com"
  },
  {
    "name": "Jane Doe",
    "age": 25,
    "email": "jane.doe@example.com"
  }
]

You might execute the following jq command to convert it to CSV:

(echo 'name,age,email'; jq -r '.[] | [.name, .age, .email] | @csv' data.json) > output.csv

Here's the breakdown of the above command:

1. echo 'name,age,email': Adds the header row for the CSV.
2. jq -r: Runs the jq tool, which processes JSON data. The -r option stands for “raw output,” which ensures the output is formatted as plain text instead of JSON (in this case, as CSV).
3. .[]: Filters the JSON array in data.json, iterating over each object (or element) within the array.
4. [.name, .age, .email]: For each object, selects the values associated with the keys name, age, and email, creating an array of these values.
5. @csv: Converts the array into a CSV-formatted string.
6. data.json > output.csv: The command reads the input from data.json, applies the transformations described above, and writes the CSV-formatted output into the file output.csv.

Using Python

Python is a versatile programming language that makes it easy to convert JSON to CSV using libraries such as json and csv. Here's an example Python script that converts a JSON file to a CSV:

import json
import csv

# Load JSON data
with open('data.json') as json_file:
    data = json.load(json_file)

# Open a CSV file for writing
with open('output.csv', 'w', newline='') as csv_file:
    csv_writer = csv.writer(csv_file)
    
    # Write CSV headers (keys from the first dictionary)
    headers = data[0].keys()
    csv_writer.writerow(headers)
    
    # Write rows (values from each dictionary)
    for row in data:
        csv_writer.writerow(row.values())

Here's how the Python script above works:

1. The script first loads your JSON file into Python using the json module. The sample script above is loading a JSON file called data.json.
2. Next, the script opens a CSV file for writing using the csv module.
3. After that, the script extracts the keys from the JSON data and writes them as headers in the CSV.
4. Finally, the script loops through the JSON data and writes each object's values as rows in the CSV file.

Conclusion

Converting JSON to CSV can be done manually or with the help of automated tools. For small or simple datasets, manual conversion might suffice, but for larger or more complex data, automated methods such as online tools, command-line utilities, and Python scripts offer more efficient and scalable solutions. The method you choose will depend on the size of your data, the complexity of its structure, and your comfort level with different tools.

Whether you opt for online tools or prefer to write a Python script, understanding how to perform this conversion will make handling data across different systems much smoother.