CODEWITHSUNDEEP
pythoncsvpython-3.x
user2106896
user2106896

Reputation: 11

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

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

Upvotes: 1

Views: 808

Answers (2)

cfi
cfi

Reputation: 11298

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.

Upvotes: 2

Jack Shedd
Jack Shedd

Reputation: 3531

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

Upvotes: 2

Related Questions