« 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