如何將 Excel 資料匯入 SQL Server

匯入與追加

本文使用的樣本 SQL 陳述式示範了“建立表”查詢。該查詢通過使用 SELECT...INTO...FROM 文法將 Excel 資料匯入新的
SQL Server 表。如這些程式碼範例所示，在繼續引用來源物件和目標對象時，可以通過使用 INSERT
INTO...SELECT...FROM 文法將這些語句轉換成新增查詢。

使用 DTS

可以使用 SQL Server Data Transmission Service (DTS) 匯入嚮導將 Excel 資料匯入 SQL Server 表。在逐步執行嚮導並選擇
Excel 源表時，要記住附加貨幣符號 ($) 的 Excel 對象名稱代表工作表（例如，Sheet1$），而沒有貨幣符號的普通對象名稱代表
Excel 指定的範圍。

使用連結的伺服器

要簡化查詢，可以將 Excel 活頁簿配置為 SQL Server 中的連結的伺服器。
有關其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

306397 (http://support.microsoft.com/kb/306397/) 如何結合 SQL Server 連結的伺服器和分散式查詢使用 Excel

下列代碼將 Excel 連結的伺服器“EXCELLINK”上的 Customers 工作表資料匯入新的名為 XLImport1 的 SQL Server 表：

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]

還可以通過按照以下方式使用 OPENQUERY 以全通過方式對來源資料執行查詢：

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')

使用分散式查詢

如果不想將對 Excel 活頁簿的永久串連配置為連結的伺服器，可以通過使用 OPENDATASOURCE 或 OPENROWSET 函數為特定目的匯入資料。下列程式碼範例也能將 Excel Customers 工作表資料匯入新的 SQL Server 表：

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

使用 ADO 和 SQLOLEDB

當通過使用 Microsoft OLE DB for SQL Server (SQLOLEDB) 在 ADO 應用程式中串連到 SQL Server 時，可以使用與“使用分散式查詢” 一節中相同的“分散式查詢”文法將 Excel 資料匯入 SQL Server。

下列 Visual Basic 6.0 程式碼範例要求添加對 ActiveX 資料對象 (ADO) 的項目引用。此程式碼範例還示範了如何在 SQLOLEDB 串連上使用 OPENDATASOURCE 和 OPENROWSET。

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'Import by using OPENDATASOURCE.
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0')...[Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and object name.
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "[Customers$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'SELECT * FROM [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

使用 ADO 和 Jet Provider

上一節中的樣本使用 ADO 和 SQLOLEDB Provider 串連到從 Excel 到 SQL 匯入的目標。也可以使用 OLE DB Provider for Jet 4.0 來串連到 Excel 源。

Jet 資料引擎可以通過使用具有三種不同格式的特殊文法來在 SQL 陳述式中引用外部資料庫：

•	[Full path to Microsoft Access database].[Table Name]
•	[ISAM Name;ISAM Connection String].[Table Name]
•	[ODBC;ODBC Connection String].[Table Name]

本節使用第三種格式建立到目標 SQL Server 資料庫的 ODBC 串連。可以使用 ODBC 資料來源名稱 (DSN) 或者 DSN-less 連接字串：

DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]

下列 Visual Basic 6.0 程式碼範例要求添加對 ADO 的項目引用。此程式碼範例示範了如何使用 Jet 4.0 Provider 通過 ADO 連線將 Excel 資料匯入到 SQL Server。

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"

    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

也可以通過使用該文法（Jet Provider 支援）將 Excel 資料匯入其他 Microsoft Access 資料庫、索引順序存取方法 (ISAM)（“desktop”）資料庫或 ODBC 資料庫。