1

The situation is I am pulling a table through an API into a python dataframe. This dataframe is being pulled directly into a SQL server. I'm running into an issue where I encounter an empty string or and empty cell that something expects to be of data type float. Even though I have the table prebuilt in SSMS with not a single float-type column, I'm still getting this error.

I pulled the same data into a CSV to troubleshoot and we can see that the 78th row of data has an empty cell in the 9th column. My python code successfully pushes 77 rows into SQL and then fails. If I simply give the column the type nvarchar then it shouldn't complain when an empty string comes along.

Can anyone tell me why I'm getting an error with the 12th parameter(When I only have 9 columns) and its desire to be a float, when it should be content with being a string?

Here as an image showing all the pieces come together, but not quite come together. A lot going on

Here is the code that may be applicable or helpful. I have commented out all the other columns for troubleshooting and did not copy them here.

for i in range(0, 5):
    now = datetime.now()
    print ('loading stage', i+1, 'of', number_of_locations, 'location:', all_unique_locations[i], ' current time:' , now.strftime ("%H:%M:%S"))
    current_stage = stageSummary.get_stage_summary_by_location_text(access_token, all_unique_locations[i], False )
    df = convert_string_to_dataframe(current_stage)
    all_stage_summary_df = all_stage_summary_df.append(df)

connStr = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=<redacted>;DATABASE=AAV_DEVELOPMENT;Trusted_Connection=yes')
cursor = connStr.cursor()

for index,row in all_stage_summary_df.iterrows():
    print("Adding row ");
    cursor.execute("INSERT INTO dbo.StageSummaries([Primary_Location]\
                                                  ,[JobNo]\
                                                  ,[Stage]\
                                                  ,[Top_Depth_m]\
                                                  ,[Elapsed_Time_hh_mm_ss]\
                                                  ,[Start_Time]\
                                                  ,[End_Time]\
                                                  ,[Pumping_Time_hh_mm_ss]\
                                                  ,[Mainline_Pressure_Min_MPa]\
                                                  )values (?,?,?,?,?,?,?,?,?)"
                                                  ,row['Primary Location']
                                                  ,row['JobNo']
                                                  ,row['Stage #']
                                                  ,row['Top Depth (m)']
                                                  ,row['Elapsed Time (hh:mm:ss)']
                                                  ,row['Start Time']
                                                  ,row['End Time']
                                                  ,row['Pumping Time (hh:mm:ss)']
                                                  ,row['Mainline Pressure Min (MPa)']
                                                    ) 
    connStr.commit()
cursor.close()
connStr.close()

I have seen a couple of questions with the same error but they don't have alot of detail or solutions that I have found.

The error

('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 12 (""): 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)'\)
KickingAustin
  • 47
  • 1
  • 8
  • 1
    "why I'm getting an error with the 12th parameter(When I only have 9 columns)" - A pyodbc `.execute()` statement is translated by the ODBC driver into an [sp_prepexec](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-prepexec-transact-sql?view=sql-server-ver15) call and `sp_prepexec` takes three (3) additional parameters (`handle`, `params`, and `stmt`) before the beginning of the bound parameters. – Gord Thompson Feb 05 '21 at 19:47
  • @GordThompson Thanks Gord, that makes a lot of sense. I thought it might be something like that but didn't find anything on it. – KickingAustin Feb 05 '21 at 22:17

1 Answers1

4

Take a look at the fillowing link: "Insert Into" statement causing errors due to "Parameter 7 (""): The supplied value is not a valid instance of data type float."

You could try to replace an empty field by 0 in your dataframe.

El Profesor
  • 56
  • 3
  • I couldn't tell you why I didn't come across this post in my onw searching but it seems to have done the trick. I instead replaced every field with "" and seemed to work fine, The supposed empty string that was found, was actually NaN so it didn't fit in any category. This allows me to leave zeros out of any column that is actually a string. Thanks El Profesor! – KickingAustin Feb 05 '21 at 23:09
  • I've had a pretty similar issue a while ago, fillna(0) also did the trick for me. Glad I could help! – El Profesor Feb 06 '21 at 06:37
  • I experienced this issue on an `nvarchar()` column set to allow `NULL`S. Having `np.nan` in that column generated the *mysterious non-existent parameter error* seen above. Replacing the `NULL`/`np.nan` value in that column with some text got rid of the bug. Before using El Profesor's solution, I verified that my data was properly formatted by writing an `INSERT INTO` SQL statement (using a SQL dialect) for the row which was generating the aforementioned error -- It worked fine, even when the 'problem value' was `NULL` rather than some text. Cheers! – Kelley Brady Aug 31 '21 at 23:09
  • Why is everyone accepting this as a answer? Inserting a 0 is not the same as inserting Nulls. – MiguelL Aug 23 '22 at 16:44