
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.
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [{ "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" }] |
1 2 3 4 5 6 7 | { "Bob":{"City":"San Diego","State":"CA"}, "Sue":{"City":"New York","State":"NY"}, "Joe":{"City":"Miami","State":"FL"} } |
1 2 3 4 | import json import csv |
1 2 3 4 5 6 7 8 | #read file with open('test.json', 'r') as jsonfile: data=jsonfile.read() # parse file jsonobj = json.loads(data) |
1 2 3 | print(jsonobj[0]['Name']) |
1 2 3 4 5 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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) |
1 2 3 4 5 6 7 | Name,City,State Bob,San Diego,CA Sue,San Francisco,CA Ted,New York,NY Joe,Miami,FL |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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) |
1 2 3 | 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:
1 2 3 4 | import json import csv |
1 2 3 4 | csvfile = open('test.csv', 'r') jsonfile = open('test2.json', 'w') |
1 2 3 | reader = csv.DictReader(csvfile) |
1 2 3 4 5 6 7 | jsonfile.write('[') for row in reader: json.dump(row, jsonfile) jsonfile.write(',') jsonfile.write('\n') jsonfile.write(']') |
1 2 3 4 5 6 | [{"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"},] |
1 2 3 4 5 6 7 8 9 10 11 12 | 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(']') |
1 2 3 | 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.
You must log in to post a comment.