pandas to_sql gives conversion errors even after specifiying dtype

I’m trying to make a program which imports data from an Excel to SQL tables. I need to do this for several hundred files, so I need to have a general approach.

I’m using SQLAlchemy to import my data frame to SQL Server, but the column ProductCode causes an issue. The first 1000 or so rows are integers, so SQL Alchemy identifies the data type as a integer. There are however some nvarchar values which causes an conversion error Conversion failed when converting the nvarchar value 'AOE1' to data type int when creating the table in SQL Server.

What I have tried is to specify that the column ProductCode should be nvarchar, but the error still persists. I can’t write a dtype argument for all columns as I don’t know how the structure of the Excel files before importing them to a data frame.

import sqlalchemy as sa
import pandas as pd

sqlcon = sa.create_engine('mssql+pyodbc://@' + serverName + "https://stackoverflow.com/" + databaseName + '?trusted_connection=yes&driver=SQL+Server')

xl2 = pd.read_excel(fullPath, sheet_name=sheetName, header=None)
xl2.to_sql(tableName,schema="dbo",con=sqlcon, index=False, if_exists="replace", dtype={'ProductCode': sa.types.NVARCHAR})

I’ve tried to go into Excel, and copy the name of the column there in case there was blank spaces or something in the column name, but it wasn’t.

I still get the same error Conversion failed when converting the nvarchar value 'AOE1' to data type int though. Can I specify the dtype of a single column (leaving the others to be identified by SqlAlchemy?) or can I force SqlAlchemy to base its data type identifier by many more values (so I can capture the nvarchar values)?

EDIT

I’ve gone into the Excel file and sorted the table by the ProductCode column to have it start with the nvarchar values. It did not help.

I’ve tried to “restart variables” in Jupyter notebook in case the data type values has been cached. It did not work.