Skip the first line of CSV file

Question

I'm having an issue with my python csv parser and I don't know where the mistake happens :

So this is my python :

# -*-coding:Utf-8 -*                                                                                                                                                                                        

import sqlite3;
from datetime import datetime, date;

conn = sqlite3.connect('info_max.sqlite3')
conn.text_factory = str
c = conn.cursor()
c.execute('drop table if exists info_max')
c.execute('create table info_max(id_huiles text, name_fr text, name_ln text, img text, mode_obt text, bien_vertus text, bio text)')

def mysplit(string):
    quote = False
    retval = []
    current = ""
    for char in string:
        if char == '"':
            quote = not quote
        elif char == ',' and not quote:
            retval.append(current)
            current = ""
        else:
            current += char
    retval.append(current)
    return retval

# Lit de ligne en ligne, en sautant la première ligne                                                                                                                                                       
rawdata = open("info_max_try.csv", "r").read()
data = rawdata.split("\n", 1)[1:]
data = re.split('"END"', rawdata)
print(data)
for entry in data:
    # Parse les valeurs                                                                                                                                                                                     
    vals = mysplit(entry.strip())
    # Convertit les strings format heure au format standard sqlite3                                                                                                                                         
    # Insert la ligne                                                                                                                                                                                       
    vals[0] = int(vals[0])
    print "Inserting %s..." %(vals[0])
    print "The enrty is : %s" %entry
    sql = "insert into info_max values(?, ?, ?, ?, ?, ?, ?)"
    c.execute(sql, vals)

# Done !                                                                                                                                                                                                    
conn.commit()

My .csv looks like this :

"id_huiles","name_fr",    "name_ln",  "img",         "mode_obt",  "bien_vertus","bio"
"77",        "Basilic", "Basilium", "Basilic.png", "some_text", "some_text",    "0"
...

And I got this error after 3 entries :

File "parseAromaHuile.py", line 39, in <module>
    c.execute(sql, vals)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 7, and there are 6 supplied.

EDIT

My lines had \nin them .. I modifed my code but I don't want my parser to read my first line

The offending line looks like this:

"6","Bergamote", "Citrus bergamis L.", "bergamote.png", "some_text", "some_text\n other_text\n more_text","0"

Thanks for your help !

Answer 1

I think the problem is in your mysplit function, somewhere (this if/elif seems suspect) it returns a list of 6 elements instead of 7.

Try this:

import sqlite3;
import csv
from datetime import datetime, date;

conn = sqlite3.connect('info_max.sqlite3')
conn.text_factory = str
c = conn.cursor()
c.execute('drop table if exists info_max')
c.execute('create table info_max(id_huiles text, name_fr text, name_ln text, img text, mode_obt text, bien_vertus text, bio text)')


with open('info_max.csv','rb') as source:
    #I use csv reader instead of writing my own
    data = csv.reader(source, delimiter=';')
    header = data.next()
    for vals in data:
        for val in vals:
            #I use replace function to get rid of qoutes
            val.replace('"', '')
        #adds the string "missing data" if some column is missing in your source 
        if len(vals) < 6:
            vals += ['missing data' for i in range(0,6-len(vals))]                                                                                                                                                                                       
        vals[0] = int(vals[0])
        print "Inserting %s..." %(vals[0])
        print "The enrty is : %s" %vals
        sql = "insert into info_max values(?, ?, ?, ?, ?, ?, ?)"
        c.execute(sql, vals)

conn.commit()

Answer 2

Your peculiar problem may lay in the readline function, which would split a longer text containing newlines into two entries/lines/data-sets instead of one! ;-)

The canonical way to parse csv would be to use the csvreader-module as pointed out by Hrabal.