2

I'm trying to use Pandas to_sql to insert data from .csv files into a mssql db. No matter how I seem do it I run into this error:

pyodbc.DataError: ('String data, right truncation: length 8 buffer 4294967294', '22001')

The code I'm running looks like this:

import pandas as pd
from sqlalchemy import create_engine

df = pd.read_csv('foo.csv')
engine = create_engine("mssql+pyodbc://:@Test")
with engine.connect() as conn, conn.begin():
    df.to_sql(name='test', con=conn, schema='foo', if_exists='append', index=False)

Any help would be appreciated!

P.S I'm still fairly new to python and mssql.

Drew Huslig
  • 41
  • 5
  • I don't know Pandas but it reads as though data is going to be truncated if you attempt to insert it. Have you compared your table schema (field definitions) to the data in your CSV file and verified that your data isn't longer than allowed by the fields? – alroc Mar 14 '18 at 01:55
  • Yes. I've tried creating a table myself with ample room and also let pandas create the table with all columns at varchar(max) so I'm not sure why anything would be truncated. – Drew Huslig Mar 14 '18 at 02:00

1 Answers1

2

Okay so I didn't have my DSN configured correctly. The driver I was using was SQL Server and I needed to change it to ODBC Driver 13 for SQL Server. That fixed all my problems.

Drew Huslig
  • 41
  • 5