如何將數據從 Excel 導入到 SQL Server

本文循序漸進地演示如何用不同的方法將數據從 Microsoft Excel 工作表導入到 Microsoft SQL Server 數據庫。

技術說明
本文中的示例使用以下工具導入 Excel 數據：
SQL Server 數據傳輸服務 (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 示例）
本文的部分內容假定您熟悉下列主題：
數據傳輸服務
鏈接服務器和分布式查詢
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: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]

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

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C: estxltest.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: estxltest.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: estxltest.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: estxltest.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: estxltestt.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 數據庫。