Pyodbc.dataerror: ('22018',"[22018] [microsoft][odbc Sql Server Driver][sql Server]conversion Failed ] Error

I have written the following snippet to import a CSV file into an MS SQL Server database but it gives me an error. It is based on code written for Sqlite for Python and changed for

Solution 1:

The error message

Conversion failed when converting the varchar value 'a' to data type int.

reveals that your code can be "fooled" into thinking that a column is integer when it is really text, presumably because it only looks at the first row of data. Testing reveals that both

ID,txt1,txt2,int1
1,foo,123,3
2,bar,abc,4

and

"ID","txt1","txt2","int1"
1,"foo","123",3
2,"bar","abc",4

result in your code producing the CREATE TABLE statement:

CREATETABLE ads (ID INTEGER,txt1 TEXT,txt2 INTEGER,int1 INTEGER)

which is wrong because the [txt2] column is not really INTEGER.

You could investigate tweaking your code to look at more than the first data row. (Microsoft's own import routines often default to the first eight rows when attempting to auto-detect data types.) You could also just import all columns as text and then convert them later in SQL server.

However, given that there must be hundreds – if not thousands – of examples out there for importing CSV data to SQL Server you should also consider doing a more exhaustive search for existing (debugged) code before you continue investing time and effort into "rolling your own solution".