When working in with data in any programming language, it is very common to use both JSON and CSV data structures.  Today we will discuss how you can convert your JSON files to CSV files.  And how to convert your CSV files into JSON files.

There are a variety of reasons you might want to convert your JSON file into a CSV file and vice-versa.  Generally, you would do this for compatibility with whatever application you are trying to work with.  For example, if you have a PowerShell script that you want to consume this data, it is much easier to use CSV files with PowerShell than it is to use JSON.

But, if you have a CSV file and you need to post some data to an API, it may be more appropriate to convert your data into JSON format since JSON is more common when interacting with web APIs.

If you would like the code without the explanation, there is a function at the bottom of each section that you can copy/paste and then use in your python scripts/applications.

How to convert JSON to CSV

Before starting this section, I recommend you read our article explaining What is JSON?  In that article, I go into detail about what JSON is, and some basics for working with JSON.

Before we can start, we need some example JSON data.  Let’s save the following into test.json:

[{
"Name" : "Bob",
"City" : "San Diego",
"State" : "CA"
},
{
"Name" : "Sue",
"City" : "San Francisco",
"State" : "CA"
},
{
"Name" : "Ted",
"City" : "New York",
"State" : "NY"
},
{
"Name" : "Joe",
"City" : "Miami",
"State" : "FL"
}]

We will use this dataset for the rest of the examples in this article.  But, before we continue, notice that this is simply an array of JSON objects.  If you have nested JSON objects, this is a much harder process to accomplish.  Nested JSON objects would be something similar to this:

{
"Bob":{"City":"San Diego","State":"CA"},
"Sue":{"City":"New York","State":"NY"},
"Joe":{"City":"Miami","State":"FL"}
}

Notice in the JSON above, you have a first level key of the user’s name, next, you have the JSON object returned as the value for each user.  By nesting the JSON in this fashion, it makes it a lot more difficult to convert to a CSV.  You will need to convert it into the JSON array as I mentioned earlier before you can proceed.

Now that we have covered the structure of the JSON, the first thing we need to do is import our JSON and CSV libraries at the top of our python script:

import json
import csv

Next, we read this file into a variable and parse it into a JSON object:

#read file
with open('test.json', 'r') as jsonfile:
  data=jsonfile.read()

# parse file
jsonobj = json.loads(data)

At this stage we can test if things are working as expected by running:

print(jsonobj[0]['Name'])

If things went according to plan, you should see “Bob” printed on your screen. This means we now have our JSON object in memory.  Next step, we need to write our code to convert the JSON into CSV.

#Create a list of keys in the JSON
keylist = []
for key in jsonobj[0]:
  keylist.append(key)

Now that we have our list of keys from the JSON, we are going to write the CSV Header

f = csv.writer(open("test.csv", "w"))
f.writerow(keylist)

#Iterate through each record in the JSON Array
for record in jsonobj:
#Create placeholder to hold the data for the current record
currentrecord = []
#Iterate through each key in the keylist and add the data to our current record list
for key in keylist:
  currentrecord.append(record[key])
#Write the current record as a line in our CSV
f.writerow(currentrecord)

he Result of this code will be a CSV that looks like this:

Name,City,State
Bob,San Diego,CA
Sue,San Francisco,CA
Ted,New York,NY
Joe,Miami,FL

Now that we have some working code, let’s convert it into a function so we can pass in some JSON and it will output a CSV file

def jsontocsv(input_json, output_path):
  keylist = []
  for key in jsonobj[0]:
    keylist.append(key)
    f = csv.writer(open(output_path, "w"))
    f.writerow(keylist)

  for record in jsonobj:
    currentrecord = []
    for key in keylist:
      currentrecord.append(record[key])
      f.writerow(currentrecord)

To use this function, we can call:

jsontocsv(jsonobj,'test.csv')

How to convert CSV into JSON

In the previous section, we covered reading in some JSON and writing out a CSV file.  In this section, our aim is to do the opposite. We are going to read in a CSV file and write out a JSON file.

For this example, we will read the CSV file we created in the previous section.  But first we need to import our JSON and CSV libraries:

import json
import csv

Now we declare a couple of variables to specify the path to the CSV file we want to read from, and the JSON file we want to write to:

csvfile = open('test.csv', 'r')
jsonfile = open('test2.json', 'w')

Next, we convert our CSV file into a dictionary

reader = csv.DictReader(csvfile)

In the final step, we iterate through each row in the reader object and write it out to our new JSON file using the JSON.dump command

jsonfile.write('[')
for row in reader:
  json.dump(row, jsonfile)
  jsonfile.write(',')
  jsonfile.write('\n')
jsonfile.write(']')

If everything went as planned, you should now have test2.json sitting on your filesystem. The contents of the file should be like this:

[{"Name": "Bob", "City": "San Diego", "State": "CA"},
"Name": "Sue", "City": "San Francisco", "State": "CA"},
{"Name": "Ted", "City": "New York", "State": "NY"},
{"Name": "Joe", "City": "Miami", "State": "FL"},]

Next, let’s turn this into a function so it is a bit easier to call.  We will create a function that takes the path to the CSV file as the first argument, and the path to the output JSON file as the second argument.

def csvtojson(source_file, dest_file):
  csvfile = open(source_file, 'r')
  jsonfile = open(dest_file, 'w')
  reader = csv.DictReader(csvfile)
  jsonfile.write('[')
  for row in reader:
    json.dump(row, jsonfile)
    jsonfile.write(',')
    jsonfile.write('\n')
  jsonfile.write(']')

You would call the function like this:

csvtojson('test.csv','test3.json')

Summary

Today we have discussed what it takes to convert a JSON document into a CSV file. And how to convert a CSV file into a JSON document.  As you can see there is not a built-in function for doing this. You have to do a bit of work to manipulate the data into the right formats.  You can only do this if you have an array of JSON documents, and all the documents have the same structure.  If your original JSON has nested objects inside it, you will need to do additional manipulation of the JSON before you can convert it to a CSV.

If you are starting with a CSV file and converting into a JSON document, the process is much more straight forward.  since they are less likely to have nested documents inside of them.