Convert JSON to CSV

Question

Hi,

In python I have a complex object hierarchy made of lists and dictionaries. I want to spit it all out to CSV or some other kind of database format. Any answers in Python or Javascript very much appreciated.

I understand that one CSV file (or table) can only represent one 'level' of object in my hierarchy, so the solution would need to create multiple files.

Here is an example:

{
    "Person" : [{"name":"Greg","age":"35","car":["honda civic","ford focus"]},
                {"name":"Steve","age":"28", "car":["mazda 323", "toyota camry"]}]
}

would become

Person.csv:
id,name,age
1,Greg,35
2,Steve,28

car.csv:
id,Person_id,value
1,1,honda civic
2,1,ford focus
3,2,mazda 323
4,2,toyota camry

Basically the only thing interesting going on here is the assignment of new ids so that the rows in the tables can be associated.

Cheers, Dave

Answer 1

assignment of new ids so that the rows in the tables can be associated.

As in:

1. Create a Primary Key (PK) for each row.

2. Create a Foreign Key (FK) relationship between Car and Person. It appears that Car has a "dependent" relationship on Person.

Step 1. Use enumerate on your JSON objects. This will give you a handy PK for each Person.

Step 2. Use the handy PK for each Person as the FK for each Car you create.

The only thing that's unpleasant is assigning PK's to the Cars since there's no handy way to use enumerate on your particular data structure. For that, you have to use a good old counter.

http://docs.python.org/library/functions.html#enumerate

Answer 2

try something like this.

json_dict = {
    "Person" : [{"name":"Greg","age":"35","car":["honda civic","ford focus"]},
                {"name":"Steve","age":"28", "car":["mazda 323", "toyota camry"]}]
}

for entity in json_dict:
    csv_file = open('%s.csv' % entity, 'wb')
    headers = a[entity][0].keys()
    csv_writer = csv.DictWriter(csv_file, headers)
    map(csv_writer.writerow, json_dict[entity])
    csv_file.close()

# Now you have your json to csv file for formatting you can use awk;

awk  -F , '{print NR ", " $2 ", " $3 }' Person.csv > person.csv

...