如何將資料從 Excel 匯入到 SQL Server

本文循序漸進地示範如何用不同的方法將資料從 Microsoft Excel 工作表匯入到 Microsoft SQL Server 資料庫。 技術說明

本文中的樣本使用以下工具匯入 Excel 資料：

• SQL Server Data Transmission Service (DTS)
• Microsoft SQL Server 2005 Integration Services (SSIS)
• SQL Server 連結的伺服器
• SQL Server 分散式查詢
• ActiveX 資料對象 (ADO) 和 Microsoft OLE DB Provider for SQL Server
• ADO 和 Microsoft OLE DB Provider for Jet 4.0

要求

下面的列表列出了推薦使用的硬體、軟體、網路架構以及所需的 Service Pack：

• Microsoft SQL Server 7.0、Microsoft SQL Server 2000 或 Microsoft SQL Server 2005 的可用執行個體
• Microsoft Visual Basic 6.0（針對使用 Visual Basic 的 ADO 樣本）

本文的部分內容假定您熟悉下列主題：

• Data Transmission Service
• 連結的伺服器和分散式查詢
• Visual Basic 中的 ADO 開發

樣本

匯入與追加

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

使用 DTS 或 SSIS

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

使用連結的伺服器

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

306397  (http://support.microsoft.com/kb/306397/EN-US/ ) 如何：結合 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 資料庫。