使用 vb.net 将 Excel 工作表导入 MySQL 表时,有没有办法检查重复项
我的网络应用程序上有一个上传 excel 文件。当文件上传时,应该打开它,然后将数据导入到 mysql 表中。它工作得很好,因为它很好地添加了信息。然而,有一个问题,我无法让重复检查正常工作。部分原因是因为将使用的 Excel 工作表没有任何可以用作唯一标识符的列。在Excel工作表中,有几次整行的信息完全相同,只有一两列存在差异......我想我可以这样做: Select * FROM table_name WHERE table_col_1 = variable and table_col_2 = variable 2 等等...
我想通过这样做,我可以将行中每一列的值与导入值进行比较,如果相同,则会跳过它。然而我无法完全理解它......也许我正在深入思考它,并且有一个简单的方法可以做到这一点。以下是我正在使用的功能:
Private Function PerFormUpdate(ByVal customer As String, ByVal bill_to As String, ByVal Contact As String, ByVal Company As String, ByVal firstName As String, ByVal mi As String, ByVal lastname As String, ByVal phone As String, ByVal altPhone As String, ByVal fax As String)
Dim _db As New schoolEntities
Dim command As MySqlCommand = _dbconn.CreateCommand()
command.Parameters.AddWithValue("@Customer", customer)
command.Parameters.AddWithValue("@Bill_to", bill_to)
command.Parameters.AddWithValue("@Contact", Contact)
command.Parameters.AddWithValue("@Company", Company)
command.Parameters.AddWithValue("@First_Name", firstName)
command.Parameters.AddWithValue("@M_I", mi)
command.Parameters.AddWithValue("@Last_Name", lastname)
command.Parameters.AddWithValue("@Phone", phone)
command.Parameters.AddWithValue("@Alt_Phone", altPhone)
command.Parameters.AddWithValue("@Fax", fax)
command.CommandText = "SELECT * FROM quickbooks_imports WHERE Customer=@Customer, Bill_to=@Bill_to, Contact=@Contact, Company =@Company, First_Name=@First_Name, M_I=@M_I, Last_Name=@Last_Name, Phone =@Phone, Alt_Phone=@Alt_Phone, Fax=@Fax"
_dbconn.Open()
Dim _mysqlReader As MySqlDataReader = command.ExecuteReader()
_dbconn.Close()
If Not _mysqlReader.HasRows Then
Dim _UpdateItem As New quickbooks_imports
Dim updateCommand As MySqlCommand = _dbconn.CreateCommand()
_UpdateItem.Customer = customer
_UpdateItem.Bill_to = bill_to
_UpdateItem.Contact = Contact
_UpdateItem.Company = Company
_UpdateItem.First_Name = firstName
_UpdateItem.M_I = mi
_UpdateItem.Last_Name = lastname
_UpdateItem.Phone = phone
_UpdateItem.Alt_Phone = altPhone
_UpdateItem.Fax = fax
updateCommand.CommandText = "INSERT INTO quickbooks_imports (Customer,Bill_to,Contact,Company,First_Name,M_I,Last_Name,Phone,Alt_Phone,Fax) VALUES (@Customer, @Bill_to, @Contact, @Company, @First_Name, @M_I, @Last_Name, @Phone, @Alt_Phone, @Fax)"
updateCommand.Parameters.AddWithValue("@Customer", _UpdateItem.Customer)
updateCommand.Parameters.AddWithValue("@Bill_to", _UpdateItem.Bill_to)
updateCommand.Parameters.AddWithValue("@Contact", _UpdateItem.Contact)
updateCommand.Parameters.AddWithValue("@Company", _UpdateItem.Company)
updateCommand.Parameters.AddWithValue("@First_Name", _UpdateItem.First_Name)
updateCommand.Parameters.AddWithValue("@M_I", _UpdateItem.M_I)
updateCommand.Parameters.AddWithValue("@Last_Name", _UpdateItem.Last_Name)
updateCommand.Parameters.AddWithValue("@Phone", _UpdateItem.Phone)
updateCommand.Parameters.AddWithValue("@Alt_Phone", _UpdateItem.Alt_Phone)
updateCommand.Parameters.AddWithValue("@Fax", _UpdateItem.Fax)
'updateCommand.CommandText = "INSERT INTO EXCEL (id,Customer,Bill_to,Contact,Company,First_Name,M_I,Last_Name,Phone,Alt_Phone,Fax) VALUES ('" & _UpdateItem.id & "','" & _UpdateItem.Customer & "','" & _UpdateItem.Bill_to & "','" & _UpdateItem.Contact & "','" & _UpdateItem.Company & "','" & _UpdateItem.First_Name & "','" & _UpdateItem.M_I & "','" & _UpdateItem.Last_Name & "','" & _UpdateItem.Phone & "','" & _UpdateItem.Alt_Phone & "','" & _UpdateItem.Fax & "') ON DUPLICATE KEY UPDATE Customer= '" & _UpdateItem.Customer & "' Bill_to= '" & _UpdateItem.Bill_to & "' Contact= '" & _UpdateItem.Contact & "' Company= '" & _UpdateItem.Company & "' First_Name= '" & _UpdateItem.First_Name & "' M_I= '" & _UpdateItem.M_I & "' Last_Name= '" & _UpdateItem.Last_Name & "' Phone= '" & _UpdateItem.Phone & "' Alt_Phone= '" & _UpdateItem.Alt_Phone & "' Fax= '" & _UpdateItem.Fax & "'"
'updateCommand.CommandText = "INSERT INTO quickbooks_imports (Customer,Bill_to,Contact,Company,First_Name,M_I,Last_Name,Phone,Alt_Phone,Fax) VALUES ('" & _UpdateItem.Customer & "','" & _UpdateItem.Bill_to & "','" & _UpdateItem.Contact & "','" & _UpdateItem.Company & "','" & _UpdateItem.First_Name & "','" & _UpdateItem.M_I & "','" & _UpdateItem.Last_Name & "','" & _UpdateItem.Phone & "','" & _UpdateItem.Alt_Phone & "','" & _UpdateItem.Fax & "') "
_dbconn.Open()
Try
updateCommand.ExecuteNonQuery()
Catch ex As Exception
Dim _error As String = Nothing
MsgBox(ex.Message)
End Try
_db.SaveChanges()
Else
Dim _NewItem As New quickbooks_imports
_NewItem.Customer = customer
_NewItem.Bill_to = bill_to
_NewItem.Contact = Contact
_NewItem.Company = Company
_NewItem.First_Name = firstName
_NewItem.M_I = mi
_NewItem.Last_Name = lastname
_NewItem.Phone = phone
_NewItem.Alt_Phone = altPhone
_NewItem.Fax = fax
_db.quickbooks_imports.AddObject(_NewItem)
_db.SaveChanges()
End If
_dbconn.Close()
Return View()
End Function
任何帮助将不胜感激......
I have a upload excel file on my web application. When the file is uploaded it is supposed to be openned and then import the data into a mysql table. It works fine in that it adds the information just fine. However there is a problem the code I cannot get the duplicate checking to work correctly.. Part of this is because the excel sheet that will be used does not have any column that I can use as a unique identifier. Several times in the excel sheet the information is completely identical across the entire row with only differences in one or two columns... I was thinking I could do something like:
Select * FROM table_name WHERE table_col_1 = variable and table_col_2 = variable 2 and etc...
I am thinking by doing this I could compare the values of every column in the row to the import value and if its the same it would skip it... However I cant quite get my head around it... Maybe I am thinking into it to deep and there is a simple way to do this. Below is the function that i am using:
Private Function PerFormUpdate(ByVal customer As String, ByVal bill_to As String, ByVal Contact As String, ByVal Company As String, ByVal firstName As String, ByVal mi As String, ByVal lastname As String, ByVal phone As String, ByVal altPhone As String, ByVal fax As String)
Dim _db As New schoolEntities
Dim command As MySqlCommand = _dbconn.CreateCommand()
command.Parameters.AddWithValue("@Customer", customer)
command.Parameters.AddWithValue("@Bill_to", bill_to)
command.Parameters.AddWithValue("@Contact", Contact)
command.Parameters.AddWithValue("@Company", Company)
command.Parameters.AddWithValue("@First_Name", firstName)
command.Parameters.AddWithValue("@M_I", mi)
command.Parameters.AddWithValue("@Last_Name", lastname)
command.Parameters.AddWithValue("@Phone", phone)
command.Parameters.AddWithValue("@Alt_Phone", altPhone)
command.Parameters.AddWithValue("@Fax", fax)
command.CommandText = "SELECT * FROM quickbooks_imports WHERE Customer=@Customer, Bill_to=@Bill_to, Contact=@Contact, Company =@Company, First_Name=@First_Name, M_I=@M_I, Last_Name=@Last_Name, Phone =@Phone, Alt_Phone=@Alt_Phone, Fax=@Fax"
_dbconn.Open()
Dim _mysqlReader As MySqlDataReader = command.ExecuteReader()
_dbconn.Close()
If Not _mysqlReader.HasRows Then
Dim _UpdateItem As New quickbooks_imports
Dim updateCommand As MySqlCommand = _dbconn.CreateCommand()
_UpdateItem.Customer = customer
_UpdateItem.Bill_to = bill_to
_UpdateItem.Contact = Contact
_UpdateItem.Company = Company
_UpdateItem.First_Name = firstName
_UpdateItem.M_I = mi
_UpdateItem.Last_Name = lastname
_UpdateItem.Phone = phone
_UpdateItem.Alt_Phone = altPhone
_UpdateItem.Fax = fax
updateCommand.CommandText = "INSERT INTO quickbooks_imports (Customer,Bill_to,Contact,Company,First_Name,M_I,Last_Name,Phone,Alt_Phone,Fax) VALUES (@Customer, @Bill_to, @Contact, @Company, @First_Name, @M_I, @Last_Name, @Phone, @Alt_Phone, @Fax)"
updateCommand.Parameters.AddWithValue("@Customer", _UpdateItem.Customer)
updateCommand.Parameters.AddWithValue("@Bill_to", _UpdateItem.Bill_to)
updateCommand.Parameters.AddWithValue("@Contact", _UpdateItem.Contact)
updateCommand.Parameters.AddWithValue("@Company", _UpdateItem.Company)
updateCommand.Parameters.AddWithValue("@First_Name", _UpdateItem.First_Name)
updateCommand.Parameters.AddWithValue("@M_I", _UpdateItem.M_I)
updateCommand.Parameters.AddWithValue("@Last_Name", _UpdateItem.Last_Name)
updateCommand.Parameters.AddWithValue("@Phone", _UpdateItem.Phone)
updateCommand.Parameters.AddWithValue("@Alt_Phone", _UpdateItem.Alt_Phone)
updateCommand.Parameters.AddWithValue("@Fax", _UpdateItem.Fax)
'updateCommand.CommandText = "INSERT INTO EXCEL (id,Customer,Bill_to,Contact,Company,First_Name,M_I,Last_Name,Phone,Alt_Phone,Fax) VALUES ('" & _UpdateItem.id & "','" & _UpdateItem.Customer & "','" & _UpdateItem.Bill_to & "','" & _UpdateItem.Contact & "','" & _UpdateItem.Company & "','" & _UpdateItem.First_Name & "','" & _UpdateItem.M_I & "','" & _UpdateItem.Last_Name & "','" & _UpdateItem.Phone & "','" & _UpdateItem.Alt_Phone & "','" & _UpdateItem.Fax & "') ON DUPLICATE KEY UPDATE Customer= '" & _UpdateItem.Customer & "' Bill_to= '" & _UpdateItem.Bill_to & "' Contact= '" & _UpdateItem.Contact & "' Company= '" & _UpdateItem.Company & "' First_Name= '" & _UpdateItem.First_Name & "' M_I= '" & _UpdateItem.M_I & "' Last_Name= '" & _UpdateItem.Last_Name & "' Phone= '" & _UpdateItem.Phone & "' Alt_Phone= '" & _UpdateItem.Alt_Phone & "' Fax= '" & _UpdateItem.Fax & "'"
'updateCommand.CommandText = "INSERT INTO quickbooks_imports (Customer,Bill_to,Contact,Company,First_Name,M_I,Last_Name,Phone,Alt_Phone,Fax) VALUES ('" & _UpdateItem.Customer & "','" & _UpdateItem.Bill_to & "','" & _UpdateItem.Contact & "','" & _UpdateItem.Company & "','" & _UpdateItem.First_Name & "','" & _UpdateItem.M_I & "','" & _UpdateItem.Last_Name & "','" & _UpdateItem.Phone & "','" & _UpdateItem.Alt_Phone & "','" & _UpdateItem.Fax & "') "
_dbconn.Open()
Try
updateCommand.ExecuteNonQuery()
Catch ex As Exception
Dim _error As String = Nothing
MsgBox(ex.Message)
End Try
_db.SaveChanges()
Else
Dim _NewItem As New quickbooks_imports
_NewItem.Customer = customer
_NewItem.Bill_to = bill_to
_NewItem.Contact = Contact
_NewItem.Company = Company
_NewItem.First_Name = firstName
_NewItem.M_I = mi
_NewItem.Last_Name = lastname
_NewItem.Phone = phone
_NewItem.Alt_Phone = altPhone
_NewItem.Fax = fax
_db.quickbooks_imports.AddObject(_NewItem)
_db.SaveChanges()
End If
_dbconn.Close()
Return View()
End Function
Any help would be greatly appreciated...
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
尝试将 Excel 中的数据插入另一个表(可能是临时表),然后将所需的数据从附加表复制到目标表,例如 -
Try to insert data from the excel into another (maybe temporary table), then copy data you need from additional table to the target table, e.g. -
上面的代码是针对mssql的..有些语法可能是错误的..但这就是我的想法..
但这可能会给ua提示..祝你好运!
The above code is for mssql.. and some syntax might be wrong.. but that's what I think of..
But this might give u a hint.. good luck!!