Excel数据批量导入到SqlServer的方法

1，以Excel为数据源建立连接导入。

关键点在于Excel的数据要有表头，表头要和数据库表的列名一样。连接字符串中HDR=YES不能省略，也就是第一行是表头的意思。IMEX=1;是把数据都当作字符串读取。

Sub test()
     
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Dim Headers As Boolean
    Dim strConn As String
    Dim path As String
    
    On Error GoTo test_Error
    
    Headers = True
    path = "c:\20131212.xls"
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & path & ";" & _
              "Extended Properties=""Excel 8.0; IMEX=1;HDR=YES"""
                  
    Debug.Print strConn
    Set cn = New ADODB.Connection
    cn.Open strConn
     
     'Import by using Jet Provider.
    strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
             "Server=192.168.6.111;Database=answer;" & _
             "UID=sa;PWD=password].test1 " & _
             "Select * FROM [Sheet1$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff
    Debug.Print "Records affected: " & lngRecsAff
     
    cn.Close
    Set cn = Nothing
     
    On Error GoTo 0
    Exit Sub
     
test_Error:
     
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of VBA Document ThisWorkbook"
     
End Sub

 2，还有一种方案，是以sqlserver为数据源，写法大致如下

 "INSERT INTO [档案1] SELECT * FROM [Excel 8.0;Database=" & ThisWorkbook.FullName & ";HDR=YES].[sheet1$" & addr & "];"