I am using Pandas read_excel to get an excel file into a dataframe, and then using the to_sql function to insert this into a table in a SQL Server database.

Setup information:

  • There are columns in the Excel sheet that are numbers I want imported as text due to leading zeroes, the datatype in Excel is specified to be text and all the cells give me the warning “Number Stored as Text” as expected.
  • Every field in my SQL Server table is nvarchar of various lengths.
  • When reading in the Excel file I am specifying the dtypes to all be object. I have also tried the astype(str) function.
  • When inserting into SQL Server I am specifying the dtypes to be nvarchar of the same length as the SQL Server table.

The error does not always trigger on the same column as I change data in my excel sheet.

This code inserts into multiple tables, and the first 4 tables work as expected so I believe the basic structure of the code should be fine.

Here is the specific error with the digits of the postal code redacted (X represents a letter, # a number):

Error: (pypyodbc.DataError) (‘22018’, “[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the nvarchar value ‘X#X #X#’ to data type int.”)

I have double checked the datatype in SQL Server is nvarchar(50) for this column, the dtype in my dataframe is object, and the dtype in the to_sql function is nvarchar(50).

Here is the code I am using with all the setup variables omitted for brevity/privacy:

import pandas as pd
import pypyodbc as odbc
from sqlalchemy import create_engine
from sqlalchemy.engine import URL
from sqlalchemy import text
import sqlalchemy

connection_string = URL.create('mssql+pyodbc', query={'odbc_connect':f'DRIVER={driver};SERVER={server_name};DATABASE={database}'})
engine = create_engine(connection_string, module=odbc)

for excel_file in excel_files_to_import:

    sheet_dtypes = excel_file_dtypes.get(excel_file, None)
    sql_dtypes = to_sql_dtypes.get(excel_file, None)
    df_excel = pd.read_excel(folder_path + excel_file, sheet_name=None, dtype=sheet_dtypes)

    for sheet_name, df in df_excel.items():
        table_name = table_name_mapping.get(sheet_name, sheet_name)
        df.to_sql(table_name, con=engine, if_exists="append", index=False, dtype=sql_dtypes)

Any help would be appreciated, I have no idea where this int problem is coming from!

New contributor

Nolan Driessen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.