VBA：将表从 Excel 导入 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15822562/
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
VBA: Import table from Excel into SQL
提问by Ben Z.
I am trying to import a data table from excel into SQL Server 2012, using VBA.
Preferably with help of a UDF.
我正在尝试使用 VBA 将 Excel 中的数据表导入 SQL Server 2012。
最好在 UDF 的帮助下。
The excel table looks something like this.
excel表看起来像这样。
TableID 2012 2011 2010 2009
row 1 11 12 13 14
row 2 21 22 23 24
row 3 31 32 33 34
etc..
(I placed the numbers in the cells to designated their position. For example 11 = row 1, column 1)
（我将数字放在单元格中以指定它们的位置。例如 11 = 第 1 行，第 1 列）
The database table looks something like this.
数据库表看起来像这样。
Header: id | year | row1 | row2 | row3 | etc..
ExampData: TableId 2012 11 21 31 ..
ExampData: TableId 2011 12 22 32 ..
(This doesn't include the Primary Key column, which can be either a combination of id and year, or a NEWID())
（这不包括主键列，它可以是 id 和 year 的组合，也可以是NEWID()）
I know how to import specific columns, for example I could run the following code:
我知道如何导入特定的列，例如我可以运行以下代码：
INSERT INTO example_table (id, year, row1, row2, row3) VALUES ('001', '2012', '11', '21', '31')
This works great for individual columns, but how would I go about making it work for the entire table (multiple columns and rows).
这对单个列很有用，但是我将如何使它适用于整个表（多列和多行）。
采纳答案by Ben Z.
So I managed to get it working. This is using Excel 2010, SQL Sever 2012.
This assumes the row titles in excel are the same as the column headings in the sql.
This also assumes a similair layout of tables as in the question post.
所以我设法让它工作。这是使用 Excel 2010，SQL Sever 2012。
这假设 excel 中的行标题与 sql 中的列标题相同。
这也假设表格的布局与问题帖子中的相似。
To import data from Excel into the SQL server, using UDF we can do the following.
Create a Public Functionin excel:
要将数据从 Excel 导入 SQL 服务器，我们可以使用 UDF 执行以下操作。在excel中
创建一个Public Function：
Public Function import_data(TableID, vHeader As Variant, vRow As Variant, vData As Variant)
End Function
And use your favorite connection method to connect to the database.
Then for the INSERTstatement use the following:
并使用您喜欢的连接方式连接到数据库。
然后对于INSERT语句使用以下内容：
'Declare the variables
Dim vpush As String
Dim headerCount As Long
Dim rowTitles As String
Dim rowDatas As String
Dim i As Long
`Count the amount of columns
headerCount = Application.CountA(vHeader)
`Create insert statement
For i = 1 To headerCount
rowTitles = VBA.Join(WorksheetFunction.Transpose(vRow), ", ")
rowDatas = VBA.Join(WorksheetFunction.Transpose(Application.Index(vData, , i)), "', '")
vpush = "INSERT INTO example_table (id, " & rowTitles & ", year) VALUES ('" & TableID & "', '" & rowDatas & "', '" & vHeader(i) & "')"
Next i
Don't forget to .Execute (vpush).
不要忘记.Execute (vpush)。
Than anytime you want to import a table, you will do the following in an excel worksheet:
比任何时候要导入表格，您都将在 Excel 工作表中执行以下操作：
- In a cell type
=import_data(and pressCTRL+SHIFT+A - Select TableId
- Select the heading row (assumed always to be years here)
- Select column with all row titles (which will be the heading titles in your sql)
- Select all data in table
- 在单元格类型中
=import_data(按CTRL+SHIFT+A - 选择表 ID
- 选择标题行（这里假设总是年份）
- 选择包含所有行标题的列（这将是您的 sql 中的标题）
- 选择表中的所有数据
That should do it.
(P.S. If this is not the most efficient way, or you see improvements.. feel free to edit or add)
那应该这样做。
（PS 如果这不是最有效的方法，或者您看到改进.. 随时编辑或添加）
回答by Martin Broadhurst
If you're using SQL Server 2008 or later, you can put multiple rows in your INSERT statement:
如果您使用的是 SQL Server 2008 或更高版本，则可以在 INSERT 语句中放置多行：
INSERT INTO example_table (id, year, row1, row2, row3)
VALUES ('001', '2012', '11', '21', '31'),
('002', '2012', '12', '22', '32'),
('003', '2012', '13', '23', '33')
Depending upon how large your spreadsheet is, you can do it in one large INSERT, or a few smaller ones.
根据电子表格的大小，您可以在一个大 INSERT 或几个较小的 INSERT 中完成。
