Monday, August 23, 2010

Convert CSV or TAB delimited text to SQL Insert with Python

Basic task: convert your coma separated or tab delimited txt file into an SQL insert script.
Done in a functional manner. Define your fields in the input file and in the destination table:  excel_fieldssql_fields 
Define your mapping: define a mapping between the two field list: mapping
Also you can assign a function to each element of this mapping. This function will be applied on the value of the input field to get the sanitized or derived db filed values in your insert SQL: map_func
The code that does the actual work is quite simple:
def print_insert(splittedline):
    print ''.join([insert_start, ','.join(map(lambda x : map_func[x](get(mapping[x],splittedline)), sql_fields)), insert_end])
f = open(sys.argv[1]'r')
map(print_insert, filter(filter_lines, map(lambda x : x.split(field_delimiter), f.readlines())))