如何將 Excel 資料匯入 SQL Server(From MSDN)

如何將 Excel 資料匯入 SQL Server

適用於

本任務的內容

• 概要
• • 技術說明
  • 要求
  • 樣本
  • • 匯入與追加
    • 使用 DTS
    • 使用連結的伺服器
    • 使用分散式查詢
    • 使用 ADO 和 SQLOLEDB
    • 使用 ADO 和 Jet Provider
  • 疑難解答
• 參考

概要

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

返回頁首

技術說明

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

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

返回頁首

要求

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

• Microsoft SQL Server 7.0 或 Microsoft SQL Server 2000 的可用執行個體
• 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

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

返回頁首

使用連結的伺服器

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

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 資料庫。

返回頁首

疑難解答

• 記住附加貨幣符號 ($) 的 Excel 對象名稱代表工作表（例如：Sheet1$），而普通對象名稱代表 Excel 指定的範圍。
• 在某些環境中，特別是用表名稱取代 SELECT 查詢指派 EXCEL 來源資料時，目標 SQL Server 表中的列會按照字母順序重排。有關 Jet Provider 中存在的這一問題的其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

  299484 PRB：使用 ADOX 檢索 Access 表的列時，列按字母順序排列

• 當 Jet Provider 確定一個 Excel 列包含了混合文本和數值資料時，Jet Provider 會選擇“majority”資料類型並將不匹配的值以 NULL 形式返回。有關如何解決這個問題的其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

  194124 PRB：使用 DAO OpenRecordset 時 Excel 傳回值為 NULL

返回頁首 參考

有關如何將 Excel 用作資料來源的其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

257819 如何在 Visual Basic 或 VBA 中使用 ADO 來處理 Excel 資料

有關如何將資料轉送到 Excel 中的其他資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

295646 如何使用 ADO 將資料從 ADO 資料來源傳輸到 Excel

247412 INFO：將資料從 Visual Basic 傳輸到 Excel 的方法

246335 如何使用“自動化”功能將資料從 ADO 記錄集傳輸到 Excel

319951 如何通過 SQL Server Data Transmission Service向 Excel 傳送資料

306125 如何將資料從 Microsoft SQL Server 匯入 Microsoft Excel

返回頁首 這篇文章中的資訊適用於:

• Microsoft Excel 2000
• Microsoft SQL Server 2000 (all editions)
• Microsoft SQL Server 2000 64 bit (all editions)
• Microsoft SQL Server 7.0
• Microsoft Excel 2002
• Microsoft Excel 97 for Windows

最新動向:	2004-5-31 (2.1)
關鍵字:	kbHOWTOmaster kbJET KB321686 kbAudDeveloper