#! /usr/bin/env python

# (C) Copyright 2002 Rob Blackwell and Active Web Solutions Ltd
#   All rights reserved.

# Converts a CSV file to an SQL file with a series of INSERT statements
# allowing the data to easily be imported into a database

# Example: csv2sql.py customers.csv customers "use mydatabase" > sql.txt

# Released under the GNU General Public License

import string, sys

def escapestring( s):
    return string.replace(s,"'","''")

def listFieldValues (a):
    buffer = ""

    for field in a:
        if field == '':
            buffer = buffer + "NULL,"
        else:
            buffer = buffer + "'" + escapestring(field) + "'" + ","
    return buffer[:-1]

def listFieldNames (a):
    buffer = ""

    for field in a:
        buffer = buffer + field + ","
    return buffer[:-1]

def writeSql(filename, table, preamble):
    firstTime = True
    print preamble

    file = open(filename,"r")
    while 1:
        line = file.readline()
        if not line:
            break
    
        buffer = line.split('\n')
        buffer = buffer[0].split(',')
       
        if firstTime:
            fieldNames = listFieldNames(buffer)
            firstTime = False
        else:
            print "INSERT INTO %s (%s) VALUES (%s)" % (table, fieldNames ,listFieldValues (buffer))


if __name__ == '__main__':
    if (len(sys.argv) < 3):
        sys.stderr.write("Usage:\n")
        sys.stderr.write("csv2sql.py  <csvfile> <tablename> [<preable>] > <sqlfile>\n")
        sys.exit(-1)

    filename = sys.argv[1]
    table = sys.argv[2]

    if (len(sys.argv) >= 3):
        preamble = sys.argv[3]
    else:
        preamble = ""

    writeSql(filename, table, preamble)