Error while trying to insert Python dataframe into SQL table

Question

I am trying to insert a .csv file's contents into a SQL Server database. I am able insert columns which has no nulls in my csv files. There are few columns which has few blanks due to which I am unable to insert those specific columns along with others. I have created a table like this;

CREATE TABLE [main].[table1](
    [Record_ID] [int] IDENTITY(1,1) NOT NULL,
    [account_id] [nvarchar](max) NULL,
    [business_name] [nvarchar] (max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

As stated above, I have few blanks in account_id and business_name columns. I could able to insert other columns with no errors. When I try to insert these two columns I get an error like this,

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 (""): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. (8023) (SQLExecDirectW)')

Not sure why I am getting this every time I try to insert csv file contents. I have already took care of that in my insert command where null will be allowed.

Insert command:

cursor.execute("INSERT INTO main.table1(account_id,business_name) values(?,?)", row.account_id, row.business_name)

Can anyone tell me how to fix this?

Any help would be appreciated.

account_id [nvarchar](max) Really? That smells funny to me. Similarly, [business_name] [nvarchar] (max) — Mitch Wheat
– Mitch Wheat, Commented Feb 20, 2022 at 5:27
Aside... SQL Server 2008 is no longer supported by Microsoft and has not been for a few years now. You should migrate your databases to newer supported versions of SQL Server. — AlwaysLearning
– AlwaysLearning, Commented Feb 20, 2022 at 5:54
Please Edit your question to include more information. Based on the ProgrammingError in the error message it seems like you're trying to insert data from a Python script, so what is that code trying to do when the error gets thrown? — AlwaysLearning
– AlwaysLearning, Commented Feb 20, 2022 at 5:57
The supplied value is not a valid instance of data type float The questions is: why does your script think that a data type in the target table is float (when there is no float in the table you posted) — Nick.Mc
– Nick.Mc, Commented Feb 20, 2022 at 23:12
...... someone has almost always had the same error as you. stackoverflow.com/questions/66068980/… Basically you'll need to replace the blanks or nulls with something otherwise it thinks it's a float — Nick.Mc
– Nick.Mc, Commented Feb 21, 2022 at 6:55

adey27 · Accepted Answer · 2022-02-21 23:17:43Z

1

I simply included the function to replace NaN with 0 and it now loads all rows successfully using the following line of code right after I create the dataframe I want to load (df)

df = df.fillna(value=0)

answered Feb 21, 2022 at 23:17

adey27

4696 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Error while trying to insert Python dataframe into SQL table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related