Monday, July 29, 2013

Python script to convert JSON file into CSV file for easy uploading on MySQL database

This tutorial show how to use a python script that converts a JSON file data into a CSV file. And how to export CSV file data into Sqlite database using a python script.

The JSON file data is stored in the following format:

{"URL": "", "headerName": null, "Domain": "", "headerValue": null},
{"URL": "", "headerName": null, "Domain": "", "headerValue": null},

Python Script File to convert a JSON file data into a CSV file: (

import fileinput
import json
import csv
import sys

l = []
for line in fileinput.input():
myjson = json.loads(''.join(l))
keys = {}
for i in myjson:
    for k in i.keys():
        keys[k] = 1
mycsv = csv.DictWriter(sys.stdout, fieldnames=keys.keys(),
for row in myjson:

$ python  fx23.json > out.csv

After executing above command, the output in the CSV file will be as follows:,,,,,,

Now Suppose, You want to append a string at the end of each line. In other words, You want to add one more column to the CSV file, then following awk command can be used:

$ awk -F"," 'BEGIN { OFS = "," } {$4="Fx23,"; print}' out.csv  >  output.csv

It will produce output as follows:,,,Fx23,,,,Fx23,

Python Script file to convert CSV file data into Sqlite Database: (

import csv, sqlite3
conn = sqlite3.connect("mydbrecord.sqlite")
curs = conn.cursor()
curs.execute("CREATE TABLE PCFC ( id INTEGER PRIMARY KEY, url TEXT, headerName TEXT, domain TEXT, headerValue TEXT, userAgent Text);")
counter = 1
reader = csv.reader(open('output.csv', 'r'), delimiter=',')
for row in reader:
    to_db = [counter, unicode(row[0], "utf8"), unicode(row[1], "utf8"), unicode(row[2], "utf8"), unicode(row[3], "utf8")]
    curs.execute("INSERT INTO PCFC (id, url, headerName, domain, headerValue) VALUES (?, ?, ?, ?, ?);", to_db)
    counter += 1

Save above file and run following command to create a database.

$ python

It will create a Sqlite database file with name mydbrecord.sqlite in the current working directory. 


  1. I'm having issues with non ascii characters, any ideas on how I can handle?

    UnicodeEncodeError: 'ascii' codec can't encode characters

  2. Hello - Is there a way to CSV to MySQL?