Python Forum
Inserting csv values into SQL server
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Inserting csv values into SQL server
#1
Hey all,

I am running into a small issue that I am unable to get around at the moment. Right now I am trying to insert values into a tablr in sql but I am hitting the below error. Being that I am new to python coding, I have tried using CAST and CONVERT in my sql script, but none work. Has anyone else hit this issue and been able to get around it? Any insight would be helpful. Thanks. My code and error message are showing below:

# Client Automation Script
import pyodbc
import csv
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=ec2-54-80-128-8.compute-1.amazonaws.com;DATABASE=GO_VIV_DMS;UID=UID;PWD=<Secret>')
with open('Poland_Test.csv') as csvfile:
        data = csv.reader(csvfile)
        cursor = cnxn.cursor()
        for row in data:
            cursor.execute('INSERT INTO GO_VIV_DMS.dbo.MasterClient(MasterClientCode,MasterClientDescription,IsActive,IncludeSMIClientPool,IncludeAgencyPool) values(?,?,?,?,?)', row)
            Print("Done")
            cursor.close()
            cnxn.commit()
Error:
Error message: Traceback (most recent call last): File "C:\Users\vwebster\Documents\MISC\Import Automation.py", line 9, in <module> cursor.execute('INSERT INTO GO_VIV_DMS.dbo.MasterClient(MasterClientCode,MasterClientDescription,IsActive,IncludeSMIClientPool,IncludeAgencyPool) values(?,?,?,?,?)', row) pyodbc.DataError: ('22018', "[22018] [Microsoft][ODBC SQL Server Driver][SQL Server] Conversion failed when converting the nvarchar value 'IsActive' to data type bit. (245) (SQLExecDirectW)")
Reply
#2
You should show the schema for GO_VIV_DMS.dbo.MasterClient
and at least a couple of rows from your csv file.
The error is telling you that there's a type mismatch on column IsActive which looks like it's a bit flag.
Reply
#3
The schema for IsActive,IncludeSMIPool,IncludeAgencyPool are set as bit. The issue is converting nvarchar to bit. I tried adding this last night, but you're not allowed to post immediately after submitting a new thread so this info was left out. I've tried cast and convert in the table insert but python doesn't like that so not sure where to do the conversion. I've even tried formatting the excel doc as text to see if it helps but still got the same error.
Reply
#4
since bit can only be true or false, what are the conditions for nvarchar?
You need to set the conditions for conversion,
nvarchar can hold chinese characters, or roman digits (in 16 bit utf form form)
in order to turn that to bit, you must set a condition
see: http://www.sqlservertutorial.net/sql-ser...erver-bit/
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to take the tar backup files form remote server to local server sivareddy 0 1,871 Jul-14-2021, 01:32 PM
Last Post: sivareddy
  Inserting values from multiple lists sqlite azulu 1 2,452 May-24-2020, 08:40 AM
Last Post: ibreeden
  error while inserting values into a table from form in flask in postgreSQL sahilsiddharth 3 7,093 Jun-05-2017, 07:49 PM
Last Post: sahilsiddharth
  Error when Inserting CSV file values into a postgreSQL using psycopg2 klllmmm 8 12,997 Oct-07-2016, 06:48 PM
Last Post: klllmmm

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020