Python Parse generic Json into table format

Question

I am just trying to convert simple Json into CSV making it like a table format, so I can easily load them into my database.

I am trying to create some generic code to parse some Json with different metadata, so I hope I don't have to specify the column name and instead hoping Python will generate the column name itself. just like this Json

[
 {
  "name":"mike",
  "sal":"1000",
  "dept":"IT",
 },
 {
  "name":"Joe",
  "sal":"1200",
  "dept":"IT",
 }
]

to make it format like this:

name   sal   dept
Mike   1000  IT
Joe    1200  IT

I use the below code but it doesn't work

import json
import csv
infile = open(r'c:\test\test.json', 'r')
outfile = open(r'c:\test\test.csv', 'w')
writer = csv.writer(outfile)
for row in json.loads(infile.read()):
    writer.writerows(row)

Can someone show me some sample code to do this?

Thanks

score 1 · Answer 1 · answered Sep 20 '17 at 19:42

1

This will help you:

writer = csv.DictWriter(f, fieldnames=['name', 'sal', 'dept'])
writer.writeheader()
for i in json.loads(a):
    writer.writerow({'name': i['name'], 'sal': i['sal'], 'dept': i['dept']})

answered Sep 20 '17 at 19:42

amarynets

1,666
7
20

score 0 · Answer 2 · answered Sep 20 '17 at 19:43

I tried your sample code and it seems to be necessary to format your .json with spaces after the colon, like this.

[
 {
  "name": "mike",
  "sal": "1000",
  "dept": "IT",
 },
 {
 "name": "Joe",
 "sal": "1200",
 "dept": "IT",
}
]

Then you can read in your code line by line into a dict. Everything else can be found here:

How do I convert this list of dictionaries to a csv file? [Python]

score 0 · Answer 3 · answered Sep 20 '17 at 19:53

Reading the JSON is fine. You need to make use of csv.DictWriter to write to the csv. It will also allow you to provide fieldnames and hence the headers in the csv file.

This will do the conversion -

import json
import csv
infile = open(r'test.json', 'r')
with open('test.csv', 'w') as csvfile:
    fieldnames = ['name', 'sal', 'dept']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()
    for row in json.loads(infile.read()):
        print(row)
        writer.writerow(row)

Do refer https://docs.python.org/2/library/csv.html for further helps.

Python Parse generic Json into table format

3 Answers3