0

I am trying to insert data from my CSV file into SQL Server, but I am getting this error, which makes no sense to me..... because I am creating the table... shouldn't I be able to specify the data type? Also, even if I remove the CREATE table part of the code, the data is still not being INSERTED into the an already existing table.

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][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)')

import pandas as pd
import pyodbc

# Import CSV
data = pd.read_csv (r'C:\Users\Empyz\Desktop\Options_Data_Combined.csv')   
df = pd.DataFrame(data)
df2 = df.replace('', np.nan, inplace=True) 

# Connect to SQL Server
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=localhost;'
                      'Database=Stocks;'
                      'Trusted_Connection=yes;')
print('Connected Successfully to SQL Server')

cursor = conn.cursor()


   
# Insert DataFrame to Table
for row in df.itertuples():
    cursor.execute('''
                INSERT INTO OPTIONS_TEST2 (contractSymbol,  lastTradeDate, strike, lastPrice, bid, ask, change, percentChange, volume,  openInterest, impliedVolatility, inTheMoney,    contractSize, currency
)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                ''',
                row.contractSymbol,
                row.lastTradeDate,
                row.strike,
                row.lastPrice,
                row.bid,
                row.ask,
                row.change,
                row.percentChange,
                row.volume,
                row.openInterest,
                row.impliedVolatility,
                row.inTheMoney,
                row.contractSize,
                row.currency

                )
conn.commit()
Empyz
  • 59
  • 2
  • 11
  • 2
    `Parameter 12 (""): The supplied value is not a valid instance of data type float` The error message seems clear. An empty string is not a valid float – SOS Mar 17 '22 at 14:30
  • Do you mind telling me what I need to change? I don't really understand python that well – Empyz Mar 17 '22 at 14:31
  • 1
    Does this answer your question? ["Insert Into" statement causing errors due to "Parameter 7 (""): The supplied value is not a valid instance of data type float."](https://stackoverflow.com/questions/53986727/insert-into-statement-causing-errors-due-to-parameter-7-the-supplied-va) – SOS Mar 17 '22 at 14:32
  • 1
    Unrelated, but things like prices are better kept in a precise datatype like `decimal`, `float` is an approximation – HoneyBadger Mar 17 '22 at 14:36
  • Thanks I'll try changing all to decimal – Empyz Mar 17 '22 at 15:17
  • 1
    You need to check for empty columns and replace them with null or with a default value. – Ben Mar 17 '22 at 17:25
  • Thanks guys. I checked through the csv and there are definitely blanks in some of the columns, so that's probably why. I will try to fix it using that link. edit: the weird thing is that the error says parameter 12, but column 12 is not where the blanks are – Empyz Mar 19 '22 at 19:24
  • I updated my code to replace blanks with NaN and removed the create table function, because that seems to lock up my SQL, when I try to expand the tables in SSMS. When I run the code with only the insert data function, I now get the error: OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC SQL Server Driver]Login timeout expired (0) (SQLDriverConnect)') – Empyz Mar 19 '22 at 20:05
  • 1
    @Empyz sounds like that is a new problem that needs a new question. – HoneyBadger Mar 19 '22 at 22:16

0 Answers0