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 compatibilty 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.

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"
}]

[{

"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. Nexted 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"}
}

{

"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 users 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 like 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 libriaries at the top of our python script:


import json
import csv

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)

#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'])

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 list of keys in the JSON


keylist = []
for key in jsonobj[0]:
  keylist.append(key)

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)

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)

The 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

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)

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')

jsontocsv(jsonobj,'test.csv')

Converting 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 in the CSV file w created in the previous section. But first we need to import our JSON and CSV libraries:


import json
import csv

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')

csvfile = open('test.csv', 'r')

jsonfile = open('test2.json', 'w')

Next we convert our CSV file into a dictionary


reader = csv.DictReader(csvfile)

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(']')

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 contens 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"},]

[{"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.

Here is what that function might look like:


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(']')

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')

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.

I Don't Know, Read The Manual

Uncomplicating Technology

Converting JSON to CSV and back again using Python

Convert JSON to CSV

Converting CSV into JSON

Summary

Like this:

Related

Be the first to comment on "Converting JSON to CSV and back again using Python"

Leave a comment Cancel reply

Convert JSON to CSV

Converting CSV into JSON

Summary

Share this:

Like this:

Related

Be the first to comment on "Converting JSON to CSV and back again using Python"

Leave a comment Cancel reply