将数据从Excel导入到列数据相等的SQL数据库 [英] Import data from Excel to SQL database where column data are equal
问题描述
Dim ExcelConnection 作为 新 System.Data.OleDb.OleDbConnection ( Provider = Microsoft.ACE.OLEDB.12.0; Data Source =& myTextBox.Text& ; ;扩展属性=Excel 12.0 Xml; HDR =是)
Dim expr As String = SELECT * FROM [Sheet1 $]
Dim objCmdSelect As OleDbCommand = 新 OleDbCommand(expr,Excel连接)
Dim objDR As OleDbDataReader
Dim SQLconn As 新 SqlConnection()
ExcelConnection .Open()
SQLconn.ConnectionString = conString
SQLconn.Open()
使用 bulkCopy As SqlBulkCopy = 新 SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = myTable
尝试
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
objDR.Close()
SQLconn.Close()
MsgBox( 已上传。 ,MsgBoxStyle.Information)
Catch ex As 异常
MsgBox (ex.ToString)
结束 尝试
结束 使用
SQL数据库中的输出表将是:
EMP。没有。 / NAME / DEPT。
1234 / John Adams / NULL
4567 / Ryan King / NULL
自从在上传的表中DEPT。是NULL,我现在需要做的是导入一个只有2列的Excel文件(EMP。编号/ DEPT。),其中包含现有的EMP。没有。已定义(1234)和DEPT。将被输入(比如会计),它将被插入与雇员号码匹配的表格中。
EMP。没有。 / NAME / DEPT。
1234 / John Adams / Accounting
4567 / Ryan King / NULL
可以有人帮我修改我的代码吗?首先十分感谢! :)
Dim objCmdSelect As OleDbCommand = 新 OleDbCommand(expr,ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As 新 SqlConnection()
ExcelConnection.Open()
SQLconn.ConnectionString = conString
SQLconn.Open()
使用 bulkCopy 作为 SqlBulkCopy = 新 SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = myTable
尝试
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
objDR.Close()
SQLconn.Close()
MsgBox( 已上传。,MsgBoxStyle 。信息)
Catch ex As 例外
MsgBox(ex .ToString)
结束 尝试
结束 使用
输出SQL数据库中的表格将是:
EMP。没有。 / NAME / DEPT。
1234 / John Adams / NULL
4567 / Ryan King / NULL
自从在上传的表中DEPT。是NULL,我现在需要做的是导入一个只有2列的Excel文件(EMP。编号/ DEPT。),其中包含现有的EMP。没有。已定义(1234)和DEPT。将被输入(比如会计),它将被插入与雇员号码匹配的表格中。
EMP。没有。 / NAME / DEPT。
1234 / John Adams / Accounting
4567 / Ryan King / NULL
可以有人帮我修改我的代码吗?首先十分感谢! :)
要做的步骤:
1)从Excel获取数据到DataTable对象(SELECT * FROM [Sheet1
WHERE NOT [Dept.] IS NULL )。
2)更新数据(你有两种方式)
a)(不推荐)浏览DataTable.Rows
的集合并构建命令字符串以更新数据,例如:
UPDATE [YourTable] SET [Dept] = '& DataTable.Rows(i).Item( Dept。)。Value.ToString()& '& vbCr& _
WHERE [Emp No.] =& DataTable.Rows(i).Item( Emp No.)。Value.ToString
b)( 推荐 )使用部门创建存储过程以更新数据
和Emp No.
作为输入参数,例如:
CREATE PROCEDURE UpdateDept
@ DeptName NVARCHAR ( 30 ),
@ EmpNo < span class =code-keyword> INT
AS
UPDATE [YourTable] SET [部门] = @ DeptName
WHERE [Emp No.] = @ EmpNo
GO集团kquote>
Hi guys! Can somebody help me please because I currently have this code for importing Excel files to SQL Server database which work perfectly:
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myTextBox.Text & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""") Dim expr As String = "SELECT * FROM [Sheet1$]" Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection) Dim objDR As OleDbDataReader Dim SQLconn As New SqlConnection() ExcelConnection.Open() SQLconn.ConnectionString = conString SQLconn.Open() Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn) bulkCopy.DestinationTableName = "myTable" Try objDR = objCmdSelect.ExecuteReader bulkCopy.WriteToServer(objDR) objDR.Close() SQLconn.Close() MsgBox("Uploaded.", MsgBoxStyle.Information) Catch ex As Exception MsgBox(ex.ToString) End Try End Using
The output table in SQL database will be:
EMP. NO. / NAME / DEPT.
1234 / John Adams / NULL
4567 / Ryan King / NULL
Since in the uploaded table the DEPT. is NULL, what I need to do now is to import an Excel File with 2 columns only (EMP. NO. / DEPT.) where the existing EMP. NO. is defined already (1234), and the DEPT. will be inputted (say Accounting) which will be inserted in the table which MATCHES the employee numbers.
EMP. NO. / NAME / DEPT.
1234 / John Adams / Accounting
4567 / Ryan King / NULL
Can somebody help me how to revise my code for this? Thanks very much in advance! :)解决方案" Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection) Dim objDR As OleDbDataReader Dim SQLconn As New SqlConnection() ExcelConnection.Open() SQLconn.ConnectionString = conString SQLconn.Open() Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn) bulkCopy.DestinationTableName = "myTable" Try objDR = objCmdSelect.ExecuteReader bulkCopy.WriteToServer(objDR) objDR.Close() SQLconn.Close() MsgBox("Uploaded.", MsgBoxStyle.Information) Catch ex As Exception MsgBox(ex.ToString) End Try End Using
The output table in SQL database will be:
EMP. NO. / NAME / DEPT.
1234 / John Adams / NULL
4567 / Ryan King / NULL
Since in the uploaded table the DEPT. is NULL, what I need to do now is to import an Excel File with 2 columns only (EMP. NO. / DEPT.) where the existing EMP. NO. is defined already (1234), and the DEPT. will be inputted (say Accounting) which will be inserted in the table which MATCHES the employee numbers.
EMP. NO. / NAME / DEPT.
1234 / John Adams / Accounting
4567 / Ryan King / NULL
Can somebody help me how to revise my code for this? Thanks very much in advance! :)
Steps to do:
1) get the data from Excel into DataTable object (SELECT * FROM [Sheet1
WHERE NOT [Dept.] IS NULL).
2) update data (you''ve got 2 ways)
a) (NOT recommended) go through the collection ofDataTable.Rows
and build command-string to update your data, for example:
"UPDATE [YourTable] SET [Dept] = '" & DataTable.Rows(i).Item("Dept.").Value.ToString() & "'" & vbCr & _ "WHERE [Emp No.] = " & DataTable.Rows(i).Item("Emp No.").Value.ToString
b) (recomended) create a stored procedure to update data, usingDept.
andEmp No.
as an input parameters, for example:
CREATE PROCEDURE UpdateDept @DeptName NVARCHAR(30), @EmpNo INT AS UPDATE [YourTable] SET [Dept.] = @DeptName WHERE [Emp No.] = @EmpNo GO
这篇关于将数据从Excel导入到列数据相等的SQL数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!