« Rebuilding Drupal caches | Main | Reordering MySQL columns »

January 28, 2011

Script to form CSV for database insertion

My technique will almost assuredly become more refined as time passes but, for now, here is a simple script that takes in a CSV file and outputs an SQL statement.

#! /usr/bin/python

#execfile("/dir/format_csv.py")

import csv, sys

try:
inDir ='/home/data/Desktop/'
inFile = 'test_datafile.csv'
db = "umbs"
table = "temptable"

#Open CSV into a list
in1 = list(csv.reader( open('{0}{1}'.format(inDir, inFile), 'r') , delimiter=','))
out = open('{0}test_output.csv'.format(inDir), 'w') # output Document
#See http://docs.python.org/tutorial/inputoutput.html for discussion on .format

#Write INSERT, db and table, column names and begin VALUES
out.write("INSERT INTO {0}.{1} ".format(db, table) +"\n")
out.write("(" + str(in1[0]).strip('[]') + ")\n")#First row must match table fields
out.write("VALUES\n")

#Write the data (i.e., rows 2:End
for row in in1[1:]:
if row != in1[-1]:
out.write("(" + str(row).strip('[]') + "),\n")
else:
out.write("(" + str(row).strip('[]') + ");\n")

#Close the output file
out.close()

except IOError:

print 'Can\'t open file for reading.'

sys.exit(0)

Posted by kkwaiser at January 28, 2011 03:39 PM

Comments

Login to leave a comment. Create a new account.