How can I convert JSON to CSV?

Question

I have a JSON file that I want to covert to a CSV file. How can I do this with Python?

I tried:

import json
import csv

f = open('data.json')
data = json.load(f)
f.close()
f = open('data.csv')
csv_file = csv.writer(f)
for item in data:
    f.writerow(item)

f.close()

However, it did not work. I am using Django and the error I received is:

file' object has no attribute 'writerow'

So, then I tried the following:

import json
import csv

f = open('data.json')
data = json.load(f)
f.close()

f = open('data.csv')
csv_file = csv.writer(f)
for item in data:
    csv_file.writerow(item)

f.close()

I then get the error:

sequence expected

Sample json file:

[
  {
    "pk": 22,
    "model": "auth.permission",
    "fields": {
      "codename": "add_logentry",
      "name": "Can add log entry",
      "content_type": 8
    }
  },
  {
    "pk": 23,
    "model": "auth.permission",
    "fields": {
      "codename": "change_logentry",
      "name": "Can change log entry",
      "content_type": 8
    }
  },
  {
    "pk": 24,
    "model": "auth.permission",
    "fields": {
      "codename": "delete_logentry",
      "name": "Can delete log entry",
      "content_type": 8
    }
  },
  {
    "pk": 4,
    "model": "auth.permission",
    "fields": {
      "codename": "add_group",
      "name": "Can add group",
      "content_type": 2
    }
  },
  {
    "pk": 10,
    "model": "auth.permission",
    "fields": {
      "codename": "add_message",
      "name": "Can add message",
      "content_type": 4
    }
  }
]

Answer 1

Unfortunately I have not enouthg reputation to make a small contribution to the amazing @Alec McGail answer. I was using Python3 and I have needed to convert the map to a list following the @Alexis R comment.

Additionaly I have found the csv writer was adding a extra CR to the file (I have a empty line for each line with data inside the csv file). The solution was very easy following the @Jason R. Coombs answer to this thread: CSV in Python adding an extra carriage return

You need to simply add the lineterminator='\n' parameter to the csv.writer. It will be: csv_w = csv.writer( out_file, lineterminator='\n' )

Answer 2

This works relatively well. It flattens the json to write it to a csv file. Nested elements are managed :)

That's for python 3

import json

o = json.loads('your json string') # Be careful, o must be a list, each of its objects will make a line of the csv.

def flatten(o, k='/'):
    global l, c_line
    if isinstance(o, dict):
        for key, value in o.items():
            flatten(value, k + '/' + key)
    elif isinstance(o, list):
        for ov in o:
            flatten(ov, '')
    elif isinstance(o, str):
        o = o.replace('\r',' ').replace('\n',' ').replace(';', ',')
        if not k in l:
            l[k]={}
        l[k][c_line]=o

def render_csv(l):
    ftime = True

    for i in range(100): #len(l[list(l.keys())[0]])
        for k in l:
            if ftime :
                print('%s;' % k, end='')
                continue
            v = l[k]
            try:
                print('%s;' % v[i], end='')
            except:
                print(';', end='')
        print()
        ftime = False
        i = 0

def json_to_csv(object_list):
    global l, c_line
    l = {}
    c_line = 0
    for ov in object_list : # Assumes json is a list of objects
        flatten(ov)
        c_line += 1
    render_csv(l)

json_to_csv(o)

enjoy.

Answer 3

Modified Alec McGail's answer to support JSON with lists inside

    def flattenjson(self, mp, delim="|"):
            ret = []
            if isinstance(mp, dict):
                    for k in mp.keys():
                            csvs = self.flattenjson(mp[k], delim)
                            for csv in csvs:
                                    ret.append(k + delim + csv)
            elif isinstance(mp, list):
                    for k in mp:
                            csvs = self.flattenjson(k, delim)
                            for csv in csvs:
                                    ret.append(csv)
            else:
                    ret.append(mp)

            return ret

Thanks!

Answer 4

JSON can represent a wide variety of data structures -- a JS "object" is roughly like a Python dict (with string keys), a JS "array" roughly like a Python list, and you can nest them as long as the final "leaf" elements are numbers or strings.

CSV can essentially represent only a 2-D table -- optionally with a first row of "headers", i.e., "column names", which can make the table interpretable as a list of dicts, instead of the normal interpretation, a list of lists (again, "leaf" elements can be numbers or strings).

So, in the general case, you can't translate an arbitrary JSON structure to a CSV. In a few special cases you can (array of arrays with no further nesting; arrays of objects which all have exactly the same keys). Which special case, if any, applies to your problem? The details of the solution depend on which special case you do have. Given the astonishing fact that you don't even mention which one applies, I suspect you may not have considered the constraint, neither usable case in fact applies, and your problem is impossible to solve. But please do clarify!

Answer 5

Since the data appears to be in a dictionary format, it would appear that you should actually use csv.DictWriter() to actually output the lines with the appropriate header information. This should allow the conversion to be handled somewhat easier. The fieldnames parameter would then set up the order properly while the output of the first line as the headers would allow it to be read and processed later by csv.DictReader().

For example, Mike Repass used

output = csv.writer(sys.stdout)

output.writerow(data[0].keys())  # header row

for row in data:
  output.writerow(row.values())

However just change the initial setup to output = csv.DictWriter(filesetting, fieldnames=data[0].keys())

Note that since the order of elements in a dictionary is not defined, you might have to create fieldnames entries explicitly. Once you do that, the writerow will work. The writes then work as originally shown.

Answer 6

I know it has been a long time since this question has been asked but I thought I might add to everyone else's answer and share a blog post that I think explain the solution in a very concise way.

Here is the link

Open a file for writing

employ_data = open('/tmp/EmployData.csv', 'w')

Create the csv writer object

csvwriter = csv.writer(employ_data)
count = 0
for emp in emp_data:
      if count == 0:
             header = emp.keys()
             csvwriter.writerow(header)
             count += 1
      csvwriter.writerow(emp.values())

Make sure to close the file in order to save the contents

employ_data.close()