0

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.

adey27
  • 439
  • 3
  • 19
  • 2
    account_id [nvarchar](max) Really? That smells funny to me. Similarly, [business_name] [nvarchar] (max) – Mitch Wheat Feb 20 '22 at 05:27
  • 1
    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 Feb 20 '22 at 05:54
  • 1
    Please [Edit](https://stackoverflow.com/posts/71191581/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 Feb 20 '22 at 05:57
  • @MitchWheat, "account-Id" column contains alphanumeric data . Any suggestions? – adey27 Feb 20 '22 at 22:15
  • Please share with us the sql statement that does the insert. (Also share the specific failing value for "account_id".) – donPablo Feb 20 '22 at 23:06
  • 1
    _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 Feb 20 '22 at 23:12
  • @Nick.McDermaid I have no idea why is that. I am confused too. – adey27 Feb 21 '22 at 05:56
  • @donPablo i have updated my question with sql insert command – adey27 Feb 21 '22 at 06:00
  • It's also mentioning `Parameter 4` - any idea where that is? – Nick.Mc Feb 21 '22 at 06:53
  • 2
    ...... someone has almost always had the same error as you. https://stackoverflow.com/questions/66068980/the-supplied-value-is-not-a-valid-instance-of-float-but-im-not-asking-for-a-fl Basically you'll need to replace the blanks or nulls with _something_ otherwise it thinks it's a float – Nick.Mc Feb 21 '22 at 06:55
  • @Nick.McDermaid. yes it worked like a treat. not sure why I didn't come across this post in my own searching. – adey27 Feb 21 '22 at 23:10

1 Answers1

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)
adey27
  • 439
  • 3
  • 19