Dim fileName As String
fileName = EXCELFILENAME.Text
Dim sConnectionString As String = "Provider=Microsoft.Ace.OleDb.12.0;Data Source='" & fileName & "';Extended Properties=Excel 12.0;"
''Dim sConnectionString As String = "Provider=Microsoft.Ace.OleDb.16.0;Data Source='" & fileName & "';Extended Properties=Excel 16.0;"
''Dim sConnectionString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
Dim oleDbConnection As OleDbConnection = New OleDbConnection(sConnectionString)
oleDbConnection.Open()
'獲取excel表
Dim dataTable As DataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim tableName As String = dataTable.Rows(0)(2).ToString().Trim()
tableName = "[" & tableName.Replace(" ' ", " ") & "]"
' ''建立EXCEL連接，讀入資料
'Dim strConn As String = "Provider=Microsoft.Ace.OleDb.12.0;Data Source='" & fileName & "';Extended Properties=Excel 12.0;"
''
'利用SQL陳述句從Excel檔案里獲取資料
Dim query As String = "SELECT 請購單號,業務型別,日期,請購部門,請購人,款式編碼,款名,顏色,尺碼,數量,金額,需求日期,建議訂貨日期,供應商編號,制單人, 倉庫編碼 FROM " & tableName
Dim dataset As DataSet = New DataSet()
Dim oleAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, sConnectionString)
oleAdapter.Fill(dataset, "TY_INPUVOUCH")
'SQL資料庫連接
Dim sqlcon As SqlClient.SqlConnection = New SqlClient.SqlConnection(IMcn)
sqlcon.Open()
''先清除里面的資料
Dim CLEARDATA As DataTable = New DataTable()
Dim sqlDACLEAR As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("truncate table TY_INPUVOUCH ", sqlcon)
Dim sqlCBCLEAR As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDACLEAR)
sqlDACLEAR.Fill(CLEARDATA)
'從excel檔案獲得資料后，插入記錄到SQL Server的資料表
Dim dataTable1 As DataTable = New DataTable()
Dim sqlDA1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("SELECT 請購單號,業務型別,日期,請購部門,請購人 ,款式編碼,款名,顏色,尺碼,數量,金額,需求日期,建議訂貨日期,供應商編號,制單人, 倉庫編碼 from TY_INPUVOUCH WITH(TABLOCKX) ", sqlcon)
Dim sqlCB1 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA1)
sqlDA1.Fill(dataTable1)
Dim dataRow11 As DataRow
For Each dataRow11 In dataset.Tables("TY_INPUVOUCH").Rows
'sql里資料dataRow1
Dim dataRow1 As DataRow = dataTable1.NewRow()
dataRow1("請購單號") = dataRow11("請購單號")
dataRow1("業務型別") = dataRow11("業務型別")
dataRow1("日期") = dataRow11("日期")
dataRow1("請購部門") = dataRow11("請購部門")
dataRow1("請購人") = dataRow11("請購人")
dataRow1("款式編碼") = dataRow11("款式編碼")
dataRow1("款名") = dataRow11("款名")
dataRow1("顏色") = dataRow11("顏色")
dataRow1("尺碼") = dataRow11("尺碼")
dataRow1("數量") = dataRow11("數量")
dataRow1("金額") = dataRow11("金額")
dataRow1("需求日期") = dataRow11("需求日期")
dataRow1("建議訂貨日期") = dataRow11("建議訂貨日期")
dataRow1("供應商編號") = dataRow11("供應商編號")
dataRow1("制單人") = dataRow11("制單人")
dataRow1("倉庫編碼") = dataRow11("倉庫編碼")
dataTable1.Rows.Add(dataRow1)
Next
Try
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
sqlDA1.Update(dataTable1) '' 匯入EXCEL資料
uj5u.com熱心網友回復：
oleDbConnection.Open()
'改為
oleDbConnection.CommandTimeout=600 '10分鐘
oleDbConnection.Open()
uj5u.com熱心網友回復：
sql server的資料匯入匯出工具了解一下。不知道你的資料到底有多少，我這成天折騰七八百萬的資料沒有問題。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/45242.html