Gilles Ganault wrote:
>
> It's probably trivial but I'm having a difficult time using Python
> to import a tab-delimited file into SQLite because some columns might
> be empty, so the INSERT string should contain NULL instead of "NULL".
>
One thing you can do is let the import utility insert the string "NULL"
for the null records and then change them to real nulls after the fact
using an update statement.
update table
set column = null
where column = 'NULL';
> Does someone have working code handy that can parse through each line,
> check each column, and if found empty, build the right INSERT?
>
> Here's my newbie, yucckie, non-working code:
>
> ======
> import csv
>
> cr = csv.reader(open("test.tsv","rb"),dialect='excel-tab')
>
> for row in cr:
> #INSERT INTO mytable (col1,col2) VALUES (NULL,"My string")
>
> sql = "INSERT INTO mytable (col1,col2) VALUES ("
> for col in row:
> if col=="":
> sql = sql + "NULL"
> else:
> sql = sql + col
> if not last col:
> sql = sql + ","
> else:
> #remove trailing comma
> ======
>
You probably need to add quotes around the col value here to make it a
literal string. I seem to remember that the csv reader strips the quotes
form the strings.
if col=="":
sql = sql + "NULL"
else:
sql = sql + "'" + col + "'"
I've always found it easier to skip the first comma instead of trying to
remove the last one.
fisrt = True
for col in row:
if not first:
sql = sql + ","
if first:
first = False
if col=="":
sql = sql + "NULL"
else:
sql = sql + "'" + col + "'"
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
