将数据从Excel插入SQL DB [英] INSERT data from Excel into SQL DB

问题描述

我已经创建了一个Excel Sheet，它可以进行一些查找，以格式化需要插入另一个表的数据。这个Excel工作簿需要给予一些可以自由添加一些新行的用户，然后需要能够点击插入数据库按钮，并将记录转换并作为新记录插入到SQL表中。我正在使用Excel 2010和SQL Server 2008.我有一个与DB的连接，因为我正在使用它来提取一些数据，以验证要添加的新行，但我不知道如何插入数据回来

I have created an Excel Sheet that does some lookups to format data that needs to be inserted into another table. This Excel Workbook needs to be given to some users that are free to add some new rows, and then need to be able to hit an "Insert Into Database" button and have the records transformed and inserted as new records into a SQL Table. I am using Excel 2010 and SQL Server 2008. I have a connection to the DB as I am using it to pull some data back in order to verify the new rows being added, but I'm not sure how to then insert the data back.

推荐答案

您可以使用ADO做很多事：

You can do a lot with ADO:

Dim cn As New ADODB.Connection

''You should probably change Activeworkbook.Fullname to the
''name of your workbook
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& ActiveWorkbook.FullName _
 & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

cn.Open strCon

s = "INSERT INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable ( Col1, Col2, Col3, Col4 ) " _
& "SELECT  a.Col1, a.Col2, a.Col3, a.Col4 " _
& "FROM [Sheet2$] a " _
& "LEFT JOIN [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable b ON a.Col1 = b.Col1 " _
& "WHERE b.Col1 Is Null"
cn.Execute s

您还可以使用ACE连接： http://www.connectionstrings.com/ 或OPENROWSET和SQL Server连接。在所有情况下，根据您的注册表设置，您可能在列中混合数据类型有问题（ http://forum.lessthandot.com/viewtopic.php?f=17&t=12043&p=59669&hilit=excel#p59669 ）

You can also use the ACE connection: http://www.connectionstrings.com/ or OPENROWSET and an SQL Server connection. In all cases, you may have problems with mixed data types in columns, depending on your registry settings (http://forum.lessthandot.com/viewtopic.php?f=17&t=12043&p=59669&hilit=excel#p59669)

这篇关于将数据从Excel插入SQL DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！