parse an input csv file with python to get an output sql file

Question

Basically what I'm looking to do in Python 3.2 is read the input csv file which contain 3 columns then create an sql output file, where the 3 data in each line from the input file, will be as parameters in an insert query :

My code looks like this :

import os  
import csv  

InFileName = r'path\test.csv'  
OutFileName = r'path\test.sql'  
NumCommas = 0  

File = open(InFileName)  
for line in File:  
    if line.count(',') > NumCommas:  
        NumCommas = line.count(',')  
File.seek(0)  

reader = csv.reader(File)  
OutFile = open(OutFileName, 'w')  
for rows in reader:  
    OutFile.write("insert into table_name values(",rows[0],", 2, to_date(",   rows[1],",'YYYY-MM-DD'), 1, 1, -1, 0, ",rows[2],", ",rows[2],", 0, 0, 0, sysdate, 0);" +   '\n')  

OutFile.close()  
File.close()

I got the error:

list index out of range

Just a few python style pointers: 1. TitleCase should only be used for class names: variables should be snake_case. 2. Use "comma-space" to separate arguments to functions. — Matthew Schinckel
– Matthew Schinckel, Commented Feb 25, 2013 at 11:18
Oh, and another thing: be very, very careful about constructing SQL queries from data that may have come from users. That may not be the case in this instance, but not really a good habit to get into. — Matthew Schinckel
– Matthew Schinckel, Commented Feb 25, 2013 at 11:19

cfi · Accepted Answer · 2013-02-25 13:47:43Z

With your code

NumCommas = 0  

File = open(InFileName)  
for line in File:  
    if line.count(',') > NumCommas:  
        NumCommas = line.count(',')

you determine and remember the maximum number of commas in one line of all lines of your input file. Afterwards you're not even using that information to validate your input.

Jack already made the point: Validate your input:

for (lineno, row) in enumerate(reader):
    if len(row) >= 3:
        OutFile.write("insert into table_name values(",row[0],", 2, to_date(",   row[1],",'YYYY-MM-DD'), 1, 1, -1, 0, ",row[2],", ",row[2],", 0, 0, 0, sysdate, 0);" +   '\n')
    else:
        print("Line {0} does not contain at least three columns: {1}".format(lineno, row))

You don't really need the first loop to count commas. Generally speaking, file I/O is a performance limitation to any computing application. Don't do it twice if you don't have to.

Also, generally speaking, always post full error messages. I'm sure Python gave you line numbers and lines of code which makes it easier for folks here to help.

Jack Shedd · Accepted Answer · 2013-02-25 11:00:45Z

2

Log rows within your rows in reader block. You might just have a blank line at the end of the file (or beginning) of the csv.

This would mean that the rows array for that row would be empty, can the rows[0], or rows[2] bits would be trying to access a column which doesn't exist for that row:

for rows in reader:
    print rows # check yourself before you wreck yourself

answered Feb 25, 2013 at 11:00

Jack Shedd

3,53123 silver badges27 bronze badges

Collectives™ on Stack Overflow

parse an input csv file with python to get an output sql file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related