Converting JSON to CSV

The Clearinghouse API returns data in JSON, which saves data in name-value pairs. The output is a list ("array") of cases, where each case is a dictionary -- that's where the name-value pairs come into play. (For more general information about JSON, see here.)

If you want to look at the API output in a spreadsheet, you'll want to convert the JSON data to CSV. There are numerous "JSON to CSV" converters online, but with larger JSON files, these online converters may be slow or unable to handle very large files. This guide demonstrates how to use Python to convert JSON to CSV.

Reading a JSON File

If you already use Python to call the API and are looking to manipulate the data in the same stretch of code, skip this section.

If you have saved your API output as a JSON file, then the first step is to read the file:

import json

# Replace "your-file.json" with your JSON file name
with open("your-file.json") as f:
    json_data = json.load(f)

When reading data, you may find it helpful to decode the data from UTF-8. You can do this with open("your-file.json", encoding="utf-8").

You now have a list of case dictionaries in json_data.

Writing a CSV File

Next, you'll write a CSV file based on the loaded JSON data. The idea is to make a CSV header row out of the names in the name-value pairs of the JSON data and to create a row for every case.

Note: The following code snippet requires Python 3.7 or higher. (Python dictionaries are ordered as of Python 3.7.)

import csv

# Replace "new-csv-file.csv" with your desired output file name
with open("new-csv-file.csv", "w") as f:
    writer = csv.writer(f)

    # Write header row of CSV file based on first case in list
    writer.writerow(json_data[0].keys())

    # Iterate through case list to write CSV rows
    for case in json_data:
        writer.writerow(case.values())

When writing data, you may find it helpful to encode the data as UTF-8. You can do this with open("new-csv-file.csv", "w", encoding="utf-8").

You now have a CSV file you can open as a spreadsheet.

Picking Specific Fields

Because opening a large CSV file in, say, Excel might be a little unwieldy, you can also pick and choose which case fields you want in your CSV file. If you want to go this route, this replaces the code snippet above.

For example, if you want to create a CSV file that contains only the id and summary for every case, here's what that would look like:

import csv

# For more fields that you can add, see
# https://api.clearinghouse.net/api-reference/objects/case
fields = [
    "id",
    "summary",
]

# Replace "new-csv-file.csv" with your desired output file name
with open("new-csv-file.csv", "w") as f:
    writer = csv.writer(f)

    # Write header row
    writer.writerow(fields)

    for case in json_data:
        row = [case[field] for field in fields]
        writer.writerow(row)