问题描述
我正在尝试使用VBA将Excel中的数据表导入到SQL Server 2012中。 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 ..
（我把数字放在单元格中指定它们的位置，例如11 =行1，列1）
数据库表看起来像这样。 / p>
标题：id |年| row1 | row2 | row3 |等等..
ExampData：TableId 2012 11 21 31 ..
ExampData：TableId 2011 12 22 32 ..
（这不包括主键列，可以是id和year的组合，也可以是 NEWID（））
我知道如何导入特定列，例如我可以运行以下代码：
INSERT INTO example_table（id，year，row1，row2，row3）VALUES（'001'，'2012'，'11'，'21'，'31 '）
这对单个列非常有用，但是我将如何使其适用于整个表（多列和行）。
所以我设法让它工作。这是使用Excel 2010，SQL Sever 2012.
这假设excel中的行标题与sql中的列标题相同。
这也假定表的类似的布局如在问题的帖子中。
要将Excel中的数据导入SQL服务器，使用UDF，我们可以执行以下操作。
在excel中创建一个公共函数
public function import_data（TableID，vHeader As Variant，vRow As Variant，vData As Variant）
结束函数
并使用您喜欢的连接方法连接到数据库。
然后对于 INSERT 语句使用以下内容：
'声明变量
Dim vpush As String
Dim headerCount As Long
Dim rowTitles As String
Dim rowDatas As String
Dim i As Long
`计算列数
headerCount = Application.CountA（vHeader）
`创建插入统计ement
对于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（'&，和rowDatas和，& vHeader（i）&'）
Next i
不要忘记 .Execute（vpush）。 / p>
比起任何时候你想导入一个表，你将在excel工作表中执行以下操作：
- 在单元格类型
= import_data（并按CTRL+SHIFT+A - 选择TableId
- 选择标题列（选择列为所有行标题（这将是您的sql中的标题）
- 选择所有数据，表
应该这样做。
（PS如果这不是最有效的方式，或者你看到改进...随意编辑或添加）
I am trying to import a data table from excel into SQL Server 2012, using VBA.
Preferably with help of a UDF.
The excel table looks something like this.
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)
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())
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).
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.
To import data from Excel into the SQL server, using UDF we can do the following.
Create a Public Function in excel:
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 INSERT statement use the following:
'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).
Than anytime you want to import a table, you will do the following in an excel worksheet:
- 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
That should do it.
(P.S. If this is not the most efficient way, or you see improvements.. feel free to edit or add)
这篇关于VBA：将表从Excel导入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！