CODEWITHSUNDEEP
sql-servercsvpyodbc
MoChen
MoChen

Reputation: 317

SQL Server Update using PYODBC

I have a CSV file which contains '\N' in some cells under a column for which the header is defined as int in SQL Server.

I am using pyodbc to update the SQL server data each day from the CSV supplied. The problem is whenever I have '\N' in the CSV file then the SQL server update results in an error and I have to delete the rows with '\N' to have them updated.

Is there any way I can update '\N' in int type column in SQL Server?

Below is the code

with open (Result_File, 'r') as h:
    reader = csv.reader(h)
    columns = next(reader)
    query = "INSERT into dbo.test({0}) values ({1})"
    query = query.format(','.join(columns), ','.join('?' * len(columns)))
    for data_line in reader:
        cur.execute(query,data_line)

Upvotes: 1

Views: 223

Answers (1)

Bryan
Bryan

Reputation: 17703

Use a list comprehension to iterate through the lists returned from the csv.reader object, replacing elements with \n value with the default value you specified (0).

...
for data_line in reader:
    # substitute 0 if the column value is '\n'
    cur.execute(query, [0 if value == '\n' else value for value in data_line])

Note that is workaround is highly specific, and would be difficult to maintain if other cases of value substitution crop up. If possible, I'd fix the process that creates the input file so your processing code can be generalized and more readable.

Upvotes: 1

Related Questions