Excel 到 SQL 表与 VB.net
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23136139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel to SQL Table with VB.net
提问by CTTKDKing
I've read all the articles and posts from other users on this subject and I'm still stuck.
我已经阅读了其他用户关于这个主题的所有文章和帖子，但我仍然被困住了。
Essentially what I have is a VB.net program with a local SQL backend. I created a table called "consolDump" that I wish to import an Excel sheet into. I feel like I'm very close just from the help I've gotten from the other people with a similar problem. Just to clarify, I do not have the ability to add software to the machine I'm using (it's heavily restricted by IT), and do not have access to the SQL server import utility.
基本上我拥有的是一个带有本地 SQL 后端的 VB.net 程序。我创建了一个名为“consolDump”的表，我希望将 Excel 工作表导入其中。我觉得我非常接近我从其他有类似问题的人那里得到的帮助。澄清一下，我没有能力将软件添加到我正在使用的机器上（它受到 IT 的严重限制），也无权访问 SQL 服务器导入实用程序。
Here's the code I have. Any help would be appreciated.
这是我的代码。任何帮助，将不胜感激。
Imports System.Data.SqlClient
Public Class formImport
Private Sub buttonConsolImport_Click(sender As Object, e As EventArgs) Handles buttonConsolImport.Click
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim fBrowse As New OpenFileDialog
Dim fname As String
Try
With fBrowse
.Filter = "Excel files(*.xls)|*.xls|All files (*.*)|*.*"
.FilterIndex = 1
.Title = "Import data from Excel file"
End With
If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
fname = fBrowse.FileName
MyConnection = New System.Data.OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fname & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("A1, B1, C1, D1, E1, F1, G1, H1, I1, J1, k1, L1 from [consol_data$]", MyConnection)
MyCommand.TableMappings.Add("Table", "consolDump")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
For Each Drr As DataRow In DtSet.Tables(0).Rows
Execute_Local("INSERT INTO consolDump(PO_Number, Consol_ID, Status, Contractor, UTAS_Owner, Description, Start_Date, End_Date, Total_Spend, ) VALUES ('" & Drr(0).ToString & "','" & Drr(1).ToString & "','" & Drr(2).ToString & "'),'" & Drr(3).ToString & "','" & Drr(4).ToString & "','" & Drr(5).ToString & "','" & Drr(6).ToString & "','" & Drr(7).ToString & "','" & Drr(8).ToString & "','" & Drr(9).ToString & "','" & Drr(10).ToString & "','" & Drr(11).ToString & "'")
Next
MsgBox("Success")
MyConnection.Close()
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Ok so, to add to what I've asked, I'm trying to import a csv file from the excel sheet with this code, which works fine.
好的，为了添加到我所问的内容，我正在尝试使用此代码从 Excel 工作表导入一个 csv 文件，该代码工作正常。
'Converts consol data to csv file===========================
Dim excelApplication As New Excel.Application
Dim excelWrkBook As Excel.Workbook
excelApplication.Visible = False
excelApplication.DisplayAlerts = False
excelWrkBook = excelApplication.Workbooks.Open("R:\PECOE-WLOX\QuEST\Torres\sqlProjects\consol_data.xls")
excelWrkBook.SaveAs(Filename:="R:\PECOE-WLOX\QuEST\Torres\sqlProjects\consol_data.csv", FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
excelWrkBook.Close()
excelApplication.DisplayAlerts = True
excelApplication.Quit()
MessageBox.Show("Converted to CSV")
'============================================================
Now I'm trying to import the csv file into a new data table but I'm getting a strange error that says "system.data.sql is a namespace and cannot be used as an expression". Now this is probably my inexperience as I pulled the code from another site and several said they have it working. I've modified it to fit my data. Any help would be appreciated. The error appears as a syntax error on "Dim cmd As New SqlClient.SqlCommand(Sql, connection)". It's highlighting the Sql in the paranthesis as a namespace.
现在我试图将 csv 文件导入到一个新的数据表中，但我收到一个奇怪的错误，提示“system.data.sql 是一个命名空间，不能用作表达式”。现在这可能是我的经验不足，因为我从另一个站点提取了代码，并且有几个人说他们可以正常工作。我已经修改它以适合我的数据。任何帮助，将不胜感激。该错误显示为“Dim cmd As New SqlClient.SqlCommand(Sql, connection)”上的语法错误。它将括号中的 Sql 突出显示为命名空间。
Dim consolDump1 As New DataTable()
consolDump1.Columns.Add("PO_Number")
consolDump1.Columns.Add("Consol_ID")
consolDump1.Columns.Add("Status")
consolDump1.Columns.Add("Contractor")
consolDump1.Columns.Add("UTAS_Owner")
consolDump1.Columns.Add("Description")
consolDump1.Columns.Add("Start_Date")
consolDump1.Columns.Add("End_Date")
consolDump1.Columns.Add("Total_Spend")
consolDump1.Columns.Add("Job_Title")
consolDump1.Columns.Add("Location")
consolDump1.Columns.Add("Type")
Dim parser As New FileIO.TextFieldParser("R:\PECOE-WLOX\QuEST\Torres\sqlProjects\consol_data.csv")
parser.Delimiters = New String() {","} ' fields are separated by comma
parser.HasFieldsEnclosedInQuotes = True ' each of the values is enclosed with double quotes
parser.TrimWhiteSpace = True
parser.ReadLine()
Do Until parser.EndOfData = True
consolDump1.Rows.Add(parser.ReadFields())
Loop
Dim strSql As String = "INSERT INTO consolDump(PO_Number,Consol_ID,Status,Contractor,UTAS_Owner,Description,Start_Date,End_Date,Total_Spend,Job_Title,Location,Type) VALUES (@PO_Number,@Consol_ID,@Status,@Contractor,@UTAS_Owner,@Description,@Start_Date,@End_Date,@Total_Spend,@Job_Title,@Location,@Type)"
Dim SqlconnectionString As String = "Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\consolData.mdf;Integrated Security=True;Connect Timeout=30"
Using connection As New SqlClient.SqlConnection(SqlconnectionString)
Dim cmd As New SqlClient.SqlCommand(Sql, connection)
' create command objects and add parameters
With cmd.Parameters
.Add("@PO_Number", SqlDbType.VarChar, 15, "PO_Number")
.Add("@Consol_ID", SqlDbType.BigInt, "Consol_ID")
.Add("@Status", SqlDbType.Text, "Status")
.Add("@Contractor", SqlDbType.Text, "Contractor")
.Add("@UTAS_Owner", SqlDbType.Text, "UTAS_Owner")
.Add("@Description", SqlDbType.Text, "Description")
.Add("@Start_Date", SqlDbType.Date, "Start_Date")
.Add("@End_Date", SqlDbType.Date, "End_Date")
.Add("@Total_Spend", SqlDbType.Money, "Total_Spend")
.Add("@Job_Title", SqlDbType.Text, "Job_Title")
.Add("@Location", SqlDbType.Text, "Location")
.Add("@Type", SqlDbType.Text, "Type")
End With
Dim adapter As New SqlClient.SqlDataAdapter()
adapter.InsertCommand = cmd
'--Update the original SQL table from the datatable
Dim iRowsInserted As Int32 = adapter.Update(consolDump1)
End Using
End Sub
回答by Joel Coehoorn
There is an extra comma at the end of the column list in your sql query:
在您的 sql 查询中的列列表末尾有一个额外的逗号：
End_Date, Total_Spend, )
End_Date, Total_Spend, )
You need this:
你需要这个：
End_Date, Total_Spend )
End_Date, Total_Spend )
Also, the column list has 9 items, but your values list has 12. Are you maybe missing a few columns?
此外，列列表有 9 个项目，但您的值列表有 12 个。您是否可能缺少几列？
Finally for this section, it doesn't matter much with code for personal use, but what you're doing with string concatenation to put the excel data into your query is considered bad practice. If you do that in product code, you're creating a huge security hole. Instead, learn about parameterized queries.
最后，对于本节，个人使用的代码无关紧要，但是您使用字符串连接将 excel 数据放入查询中的做法被认为是不好的做法。如果您在产品代码中这样做，就会造成一个巨大的安全漏洞。相反，了解参数化查询。
In the later sample, you define your sql string this way:
在后面的示例中，您以这种方式定义 sql 字符串：
Dim strSql As String
But try to include in your command this way:
但是尝试以这种方式包含在您的命令中：
Dim cmd As New SqlClient.SqlCommand(Sql, ...
You should do this:
你应该做这个：
Dim cmd As New SqlClient.SqlCommand(strSql, ...
Also, based on the other code, if this is an access database, you should still be using the OleDbCommandand OleDbConnectionobjects, in the System.Data.OleDbnamespace.
此外，根据其他代码，如果这是一个访问数据库，您仍然应该在命名空间中使用OleDbCommand和OleDbConnection对象System.Data.OleDb。
What I would do is somewhere in between your first sample and your second:
我会做的是介于您的第一个样本和第二个样本之间：
Imports System.Data.SqlClient
Private Sub buttonConsolImport_Click(sender As Object, e As EventArgs) Handles buttonConsolImport.Click
Dim fBrowse As New OpenFileDialog
With fBrowse
.Filter = "Excel files(*.xls)|*.xls|All files (*.*)|*.*"
.FilterIndex = 1
.Title = "Import data from Excel file"
End With
If fBrowse.ShowDialog() <> Windows.Forms.DialogResult.OK Then Exit Sub
Dim fname As String = fBrowse.FileName
Dim sql As String = "INSERT INTO consolDump(PO_Number,Consol_ID,[Status],Contractor,UTAS_Owner,Description,Start_Date,End_Date,Total_Spend,Job_Title,Location,Type) VALUES (@PO_Number,@Consol_ID,@Status,@Contractor,@UTAS_Owner,@Description,@Start_Date,@End_Date,@Total_Spend,@Job_Title,@Location,@Type)"
Try
Dim parser As New FileIO.TextFieldParser(fname)
parser.Delimiters = New String() {","} ' fields are separated by comma
parser.HasFieldsEnclosedInQuotes = True ' each of the values is enclosed with double quotes
parser.TrimWhiteSpace = True
parser.ReadLine() 'skip column headers
Using cn As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\consolData.mdf;Integrated Security=True"),
cmd As New SqlCommand(sql, cn)
With cmd.Parameters
.Add("@PO_Number", SqlDbType.VarChar, 15)
.Add("@Consol_ID", SqlDbType.BigInt)
.Add("@Status", SqlDbType.Text) 'I doubt that "Text" is the right type for all of these
.Add("@Contractor", SqlDbType.Text) 'If it is, you may want to examine your table schema
.Add("@UTAS_Owner", SqlDbType.Text)
.Add("@Description", SqlDbType.Text)
.Add("@Start_Date", SqlDbType.Date)
.Add("@End_Date", SqlDbType.Date)
.Add("@Total_Spend", SqlDbType.Money)
.Add("@Job_Title", SqlDbType.Text)
.Add("@Location", SqlDbType.Text)
.Add("@Type", SqlDbType.Text)
End With
cn.Open()
Do Until parser.EndOfData = True
Dim fields() As String = parser.ReadFields()
For i As Integer = 0 To 11
cmd.Parameters(i).Value = fields(i)
Next
cmd.ExecuteNonQuery()
Loop
End Using
MsgBox("Success")
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
