0

I am importing csv file to SQL server, my csv file consists of columns with null values.. here is my snippet,

@event.listens_for(engine, 'before_cursor_execute')
def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
    if executemany:
        cursor.fast_executemany = True

for filename in os.listdir(directory):
        df = pd.read_csv(fl_path,header=0, dtype=str)
        df.fillna("", inplace=True)
        df.to_sql(table_name, con=engine, if_exists='append', index=False)

I got the following error,

sqlalchemy.exc.DataError: (pyodbc.DataError) ('22018', '[22018] [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification (0) (SQLExecute)')

when I try to convert null into blank, I'm getting error and I want to insert null values as blank.. and when i insert null as null into database I'm not facing any errors.. please help me where I'm going wrong

jarlh
  • 42,561
  • 8
  • 45
  • 63
user
  • 3
  • 4
  • Can you identify a specific column in the target table that is triggering the error? What type is it? (datetime, int, decimal, … ?) – Gord Thompson Jul 31 '22 at 14:42
  • column type : nvarchar – user Jul 31 '22 at 14:50
  • I am unable to reproduce your issue. [This code](https://gist.github.com/gordthompson/90aba52277fa3e748c431a22f7ffe100) works fine for me. What versions of Python, SQLAlchemy, pyodbc, and pandas are you running? – Gord Thompson Jul 31 '22 at 15:18
  • possibly related: https://stackoverflow.com/a/61206056/2144390 – Gord Thompson Jul 31 '22 at 15:27
  • python : 3.10.0 , SQLAlchemy : 1.4.39 , pyodbc : 4.0.34 , pandas : 1.4.3 – user Jul 31 '22 at 15:42
  • Okay, I'm running Python 3.10.2, but otherwise we're the same. I'm using Windows; are you? – Gord Thompson Jul 31 '22 at 16:16
  • BTW, [1] Current versions of SQLAlchemy don't need `@event.listens_for`, we can just use `create_engine(connection_url, fast_executemany=True)`, [2] Does my [example code](https://gist.github.com/gordthompson/90aba52277fa3e748c431a22f7ffe100) work for you? – Gord Thompson Jul 31 '22 at 16:21
  • 1
    _Invalid character value for cast specification_ is usually related to non-character columns like `date`, `datetime` and `time`. Are you sure the target table only contains `nvarchar` columns? – AlwaysLearning Jul 31 '22 at 21:58
  • target table also consists datetime fields – user Aug 01 '22 at 05:30

0 Answers0