1

i'm using Python 3.9 and extracting data from salesforce. i want to insert the data into an SQL Server table. all works fine until one of the fields i want to insert is coming as None (empty). the SQL Server table has a column with type Float and some records has data for that column and some doesn't (hence, are equal to NULL).

when executing the INSERT statement with no data for "ExpectedRevenue" column ("None") and where the corresponding column is Float andexpecting to get a Float data type (not "None") i get an error such as:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type varchar to float. (8114) (SQLExecDirectW)')

the INSERT statement is similar to:

cursor.execute('''INSERT INTO [SOME DB].[dbo].[SOME TABLE] (Id, IsDeleted, Name, ExpectedRevenue) VALUES(?,?,?,?) ''',(Id, IsDeleted, Name,ExpectedRevenue) )

at the above example the last column is Float at the table, the field names are the same as the variable names.

please assist

Improve this question

2 Answers 2

Reset to default
4

The error is because of NaN values in your data. The sql server Null equivalent in python is None object, so you must change all NaNs with None, if you want to insert Nulls into the sql server: df.where((pd.notnull(df)), None)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

never mind... my bad but it worked just fine with the "None" insertion

Improve this answer

1 Comment

Since it worked with None, you may want to mark @M.RezaSharifian response as an Answer. It will help other readers of your post.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.