0

this code is SQL converter that import the excel file to SQL Server. But I can't insert the enpty cell to Null value in SQL Server.

import pandas as pd
import pyodbc
from tkinter import *
from tkinter import filedialog,ttk,messagebox
import os

window = Tk()
window.title("Tooling")
window.geometry('800x500')

def import_data_to_database():
    file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])

    if file_path:
        df = pd.read_excel(file_path)
        file_name = os.path.basename(file_path)
        connect = pyodbc.connect('Driver={SQL Server};'
                          'Server=PBG'
                          'Database=Name;'
                          'Trusted_Connection=yes;')
        cursor = connect.cursor()
        table_name = select_table.get()
        columns = ', '.join(df.columns)
        placeholders = ', '.join(['?']*len(df.columns))
    
        check = pd.read_sql(f"SELECT DISTINCT FileName FROM {table_name}",connect)
        check_list = check[check.columns[0]].values.tolist()
        for x in check_list:
            if file_name == x:
                messagebox.showerror('Error','This File is Imported')
            else:
                df['FileName'] = file_name
                print(df)
                query = f"INSERT INTO {table_name} ({columns}, FileName) VALUES ({placeholders},?)"
                cursor.executemany(query,df.values.tolist())
                connect.commit()
                cursor.close()
                connect.close()
                msg = f"Data has been imported to {table_name} from {file_path}."
                status_label.config(text=msg)

table = ["dbo.PartItem","dbo.Item", "dbo.Part","dbo.Orders","dbo.Material","dbo.PartMat","dbo.Vendor"]
label1 = Label(window, text='Select Table to Import to SQL Server')
label1.grid(row=0, column=0, padx=5)

select_table = ttk.Combobox(window, values= table)
select_table.grid(row=1, column=0, padx=5)

import_button1 = Button(window,text="Import Data",command=import_data_to_database)
import_button1.grid(row=3, column=0, padx=5)

bank1 = Label(window, text="")
bank1.grid(row=4, column=0, padx=5)

status_label = Label(window, text="", fg = "green")
status_label.grid(row=2, column=0, padx=5)

window.mainloop()

that have error :

cursor.executemany(query,df.values.tolist()) pyodbc.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 8 (""): 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)')

but I already set the table can Null

RF1991
  • 2,037
  • 4
  • 8
  • 17

0 Answers0