使EXCEL中使用宏生成T-SQL实现数据导入

     有时我们需要从EXCEL文档中把数据导入到数据库，这时我们可以使用Excel的宏功能。假设如下图这样的DEMO数据:

    然后通过“视图”找到如下选项：

  接着我们创建一个宏，在编辑器中输入(Vbscript):

Sub CreateInsertScript()

 Dim Row As Integer

 Dim Col As Integer

 'To store all the columns available in the current active sheet

 Dim ColNames(100) As String

 Col = 1

 Row = 1

 Dim ColCount As Integer

 ColCount = 0

 'Get Columns from the sheet

 Do Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.

 ColNames(ColCount) = "[" + ActiveSheet.Cells(Row, Col) + "]"

 ColCount = ColCount + 1

 Col = Col + 1

 Loop

 ColCount = ColCount - 1

 'Inputs for the starting and ending point for the rows

 Row = InputBox("Give the starting Row No.")

 Dim MaxRow As Integer

 MaxRow = InputBox("Give the Maximum Row No.")

 'File to save the generated insert statements

 File = "c:\\InsertCode.txt"

 fHandle = FreeFile()

 Open File For Output As fHandle

 Dim CellColCount As Integer

 Dim StringStore As String 'Temporary variable to store partial statement

 Do While Row <= MaxRow

 StringStore = ""

 CellColCount = 0

 'ActiveSheet.Name will give the current active sheet name

 'this can be treated as table name in the database

 StringStore = StringStore + "insert into [" + ActiveSheet.Name + "] ( "

 Do While CellColCount <= ColCount

 StringStore = StringStore + ColNames(CellColCount)

 'To avoid "," after last column

 If CellColCount <> ColCount Then

 StringStore = StringStore + " , "

 End If

 CellColCount = CellColCount + 1

 Loop

 'Here it will print "insert into [TableName] ( [Col1] , [Col2] , ..."

 Print #fHandle, StringStore + " ) "

 'For printing the values for the above columns

 StringStore = " values( "

 CellColCount = 0

 Do While CellColCount <= ColCount

 StringStore = StringStore + " '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"

 If CellColCount <> ColCount Then

 StringStore = StringStore + ", "

 End If

 CellColCount = CellColCount + 1

 Loop

 'Here it will print "values( 'value1', 'value2', ..."

 Print #fHandle, StringStore + ");"

 Print #fHandle, " "

 Row = Row + 1

 Loop

 Close #fHandle

 MsgBox ("Successfully Done")

 End Sub

接着点击运行，好了弹出两个对话框，输入起始行2，结束行5,确定后在生成一个文本文件，这些参数你可以修改的 c:\\InsertCode.txt

内容是我们最终想要的T-SQL:

insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] )
 values(  'Peter',  '23',  '2009-01-01',  '3003.5');

insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] )
 values(  'Lucy',  '21',  '2003-10-01',  '2087.65');

insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] )
 values(  'Max',  '29',  '2011-01-01',  '1989.11');

insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] )
 values(  'Eric',  '35',  '1999-05-01',  '5043.2');

很简单，您可以自己动手试一下。

希望对您开发有帮助。

作者：Petter Liu
出处：http://www.cnblogs.com/wintersun/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
该文章也同时发布在我的独立博客中-Petter Liu Blog。