iA


Quick CSV to JSON parser in python

by Andy Boyle.

I did something in python today. It wasn’t that hard, but I thought I should write up something quickly about it.

We’ve got some people entering in some data in a Google Doc spreadsheet for a project. After fiddling around with attempting to make Google Docs API spit out not-ugly JSON, I said screw it and came up with a new plan: I was going to write a cron job that downloads the CSV from the spreadsheet and then parses it into JSON.

And of course, as a guy who likes Django, I turned to python for the parsing.

First, you will need to remove the first line in the CSV if it had any field names. Put it into a folder somewhere, perhaps. Then make an empty file and name it parsed.json and place it in the same file.

If you’re on a Mac/Unix-y thing, use your terminal and cd into the folder your parsed.json file is. Enter

pwd

in the terminal to see the path to your directory. If you’re on a Windows box, then it’s probably C:\wherever\your\stuff\is\

Now create a new file called parser.py. Enter in the code below, changing /path/to/filename part on line 5 to the bit that was spit out after you typed pwd (or where you inherently found it if you’re in Windows). On line 7, change fieldname1 and whatnot to the name you want each row to have in your JSON file.

import csv
import json

# Open the CSV
f = open( '/path/to/filename.csv', 'rU' )
# Change each fieldname to the appropriate field name. I know, so difficult.
reader = csv.DictReader( f, fieldnames = ( "fieldname0","fieldname1","fieldname2","fieldname3" ))
# Parse the CSV into JSON
out = json.dumps( [ row for row in reader ] )
print "JSON parsed!"
# Save the JSON
f = open( '/path/to/parsed.json', 'w')
f.write(out)
print "JSON saved!"

In your terminal, while you’re in the proper directory, type:

 python parser.py 

BOOM! It should spit out “JSON parsed!” and “JSON saved!” If you wanted to spit out the JSON in the terminal, you could add a line at the bottom:

 print out 

Voila. Now you can turn your CSV files into JSON.