CSV to JSON script

Question

I took this script from here:

import csv
from itertools import izip
f = open( '/django/sw2/wkw2/csvtest1.csv', 'r' )
reader = csv.reader( f )
keys = ( "firm_url", "firm_name", "first", "last", "school", "year_graduated" )
out = []
for property in reader:
    property = iter( property )
    data = {}
    for key in keys:
        data[ key ] = property.next()
    out += [ data ]
print out

When I tried it in IDLE I got the error

Traceback (most recent call last):
  File "<pyshell#13>", line 5, in <module>
    data [key] = property.next()
StopIteration

But I tried

print out

again and then it printed

[{'school': 'The George Washington University Law School', 'last': 'Abbas', 'firm_url': 'http://www.whitecase.com/aabbas', 'year_graduated': ' 2005', 'firm_name': 'White & Case', 'first': ' Amr A '}, {'school': 'Ernst Moritz Arndt University Greifswald', 'last': 'Adam', 'firm_url': 'http://www.whitecase.com/kadam', 'year_graduated': ' 2004', 'firm_name': 'White & Case', 'first': ' Karin '}, {'school': 'Tashkent State Law Institute', 'last': 'Adjivefayev', 'firm_url': 'http://www.whitecase.com/vadjivefayev', 'year_graduated': ' 2002', 'firm_name': 'White & Case', 'first': ' Vilen '}]

But when I try to run it as a script, it doesn't work, I get the same error message.

Can anyone help fix the error?

(And is it outputting valid json?)

Thanks

Edit

Thanks for the answers. It seems that this is not the right way of converting a csv file to json format. I am just trying to convert the csv file with data in it so that I can use loaddata to populate my sqlite3 database in django. See this thread in django group: http://groups.google.com/group/django-users/browse_frm/thread/a00b529ba2147d91 for my attempt to use csv2json.py snippet. And another thread today in OS (Sorry I cannot include 2 links). I would appreciate a simple way of converting csv to json. Or the method you use to populate your django database that I should be using instead. Thanks for the help.

Labourite

The other OS thread: #1882969 – Labourite

Answer 1

Change the nested for loop to:

out = [dict(zip(keys, property)) for property in reader]

and, no, print out will not emit valid JSON -- use print json.dumps(out) (you'll need to import json too of course -- that's a Python 2.6 standard library module but you can find versions working with 2.5 if that's what you need).

Rounded

Thanks! This works. But I couldn't use loaddata with that file. I'll ask that separately after I try a few more things. – Labourite

+1 That's a nice piece of code. I'll be steal^h^h^h^h^h using this idea really soon. – Metallurgy

Answer 2

With the CSV Module you already have a dict reader built in! Here's an example script which can be used as a command line tool:

import csv
import json

def csvToJson( inFile, outFile ):
    out = None;

    with open( inFile, 'r') as csvFile:
        #Note this reads the first line as the keys we can add specific keys with:
        #csv.DictReader( csvFile, fieldnames=<LIST HERE>, restkey=None, restval=None, )
        csvDict = csv.DictReader( csvFile, restkey=None, restval=None, )
        out = [obj for obj in csvDict]

    if out:
        with open( outFile, 'w' ) as jsonFile:
            jsonFile.write( json.dumps( out ) );
    else:
       print "Error creating csv dict!"

if __name__ == "__main__":
     import argparse

     parser = argparse.ArgumentParser()
     parser.add_argument('inFile', nargs=1, help="Choose the in file to use")
     parser.add_argument('outFile', nargs=1, help="Choose the out file to use")
     args = parser.parse_args()
     csvToJson( args.inFile[0] , args.outFile[0] );

Hibernia

Answer 3

import csv
import json
reader = csv.reader(f, delimiter=',', quotechar='"')
keys = next(reader) #skip the headers  
out = [{key: val for key, val in zip(keys, prop)} for prop in reader]
json.dumps(out)

Michey

I really like your solution...just for new in python should be better if you explain that f is: f = open('/path/file', 'r') – Modification

Answer 4

Maybe you are trying to parse an empty line at the end of the file

for property in reader:
    print repr(property)         # <---try adding a print here
    property = iter( property )

Also csv.DictReader may do what you want already

csv.DictReader(f,fields=("firm_url", "firm_name", "first", "last", "school", "year_graduated" ))

Canoness

Answer 5

Since you're not actually creating JSON, I'm not sure about the last question. You're just printing a Python dictionary. They're mostly JSON, but not always.

So you should find a good json module and use that. If you have Python 2.6: http://docs.python.org/library/json.html

Also, csv has a dictionary reader that does all of this in a much shorter and easier to live with form. http://docs.python.org/library/csv.html#csv.DictReader

---

Edit.

import csv
from your.app.models import YourClass

with open( "path/to/your/file.csv", "rb" ) as src:
    rdr = csv.DictReader( src )
    for row in rdr:
        x= YourClass.objects.create( field=row['column'], field=row['column'], ... )
        x.save()
        print x

Something like that usually works better.

Churchman