Title: Pandas To_SQL is giving the DataError: (pyodbc.DataError) ('22018', "[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]
Tags: python;pandas
Question: I am trying to load the excel file in SQL server using sqlalchemy, to_sql. When i run the code it gives me below error.
```quoted = urllib.parse.quote_plus(&quot;Driver={SQL Server};&quot;
                                 &quot;Server=XYZ;&quot;
                                 &quot;Database=SNOW;&quot;
                                 &quot;Trusted_Connection=yes;&quot;)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))

data = pd.read_excel(r'I:\ESS_ITRS_STATS_DATA.xlsx')
# rename columns
data = data.rename(columns={'Gateway': 'Gateway',
                            'Severity': 'Severity',
                            'Country': 'Country',
                            'CSIID': 'CSI_ID',
                            'NetProbe': 'NetProbe',
                            'Entity': 'Entity',
                            'Sampler': 'Sampler',
                            'Variable': 'Variable',
                            'Hostname': 'Hostname',
                            'Absolute_path': 'Absolute_path',
                            'Date': 'Date',
                            'Description': 'Description',
                            'InsertedDateTime': 'InsertedDateTime'})
df = pd.DataFrame(data, columns=['Gateway','Severity','Country','CSI_ID','NetProbe','Entity','Sampler','Variable','Hostname','Absolute_path','Date','Description','InsertedDateTime'])
df['InsertedDateTime'] = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
df = df.fillna(value=0)
df['CSI_ID'] = df['CSI_ID'].astype(str)
table_name='ITRSALERTS_TEMP'
df.to_sql(table_name,engine,index=False,if_exists=&quot;append&quot;,schema=&quot;dbo&quot;, chunksize=25,dtype={col_name: NVARCHAR for col_name in df})
```
i am getting the below error
DataError: (pyodbc.DataError) ('22018', &quot;[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the nvarchar value '95.89' to data type int. (245) (SQLExecDirectW)&quot;)
below is the content from my excel
Gateway Severity    Country CSIID   NetProbe    Entity  Sampler Variable    Hostname    Absolute_path   Date    Description InsertedDateTime
SST_ISSUER01_NAM_PROD   CRITICAL    UNITED STATES   0   XYZ-1   XYZ-H-1 CPU _Total.%processorTime   XYZ-H-1 0   2020-09-20T15:12:35 95.89   2020-09-21 18:12:57



Here is another answer: Apparently data type in database and in dataframe do not match: database expects value '95.89' to be numerical and it is sent as a string. You should cast it to appropriate type, maybe round it (depending on database table definition) in the same way you've already done for CSI_ID column:
```df['Description'] = df['Description'].astype(float)
```


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2020:00:09/21/20', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<python><pandas>', 'Title': 'Pandas To_SQL is giving the DataError: (pyodbc.DataError) (\'22018\', "[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]', 'ViewCount': 861, 'id': 63992135, 'user_AboutMe': None, 'user_AccountId': 19516464.0, 'user_DisplayName': 'Sunny', 'user_Reputation': 11.0, 'user_Views': 4.0, 'user_id': 14279206.0}}