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

結論
本文將逐步告訴您,如何使用各種方法來將資料從 Microsoft Excel 工作表匯入到 Microsoft SQL Server 資料庫中。

回到頁首

技術說明

本文中的範例使用以下項目匯入 Excel 資料:
  • SQL Server 資料轉換服務 (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • SQL Server 連結伺服器
  • SQL Server 分散式查詢
  • ActiveX Data Objects (ADO) 與 SQL Server 的 Microsoft OLE DB Provider
  • ADO 與 Jet 4.0 的 Microsoft OLE DB Provider
回到頁首

需求

下列清單列出建議使用的硬體、軟體、網路基礎結構及必要的 Service Pack:
  • Microsoft SQL Server 7.0、Microsoft SQL Server 2000 或 Microsoft SQL Server 2005 的可用執行個體
  • 使用 Visual Basic 的 ADO 範例的 Microsoft Visual Basic 6.0
本文假設您已熟悉下列主題:
  • 資料轉換服務
  • 連結伺服器與分散式查詢
  • Visual Basic 中的 ADO 開發
回到頁首

範例

匯入與附加

本文中使用的 SQL 陳述式範例將示範「建立表格」查詢,它使用了 SELECT...INTO...FROM 語法來將 Excel 資料匯入到新的 SQL Server 表格中。您可以使用 INSERT INTO...SELECT...FROM 語法來將這些陳述式轉換為「附加」查詢,同時您還可以繼續參照如這些程式碼範例中所示的來源及目的地物件。

回到頁首

使用 DTS 或 SSIS

您可以使用「SQL Server 資料轉換服務 (DTS) 匯入精靈」或「SQL Server 匯入及匯出精靈」,將 Excel 資料匯入到 SQL Server 資料表中。當您使用此精靈,並選擇 Excel 來源表格時,請記住後面附加了錢幣符號 ($) 的 Excel 物件名稱代表工作表 (例如,Sheet1$),而後面沒有錢幣符號的純物件名稱則代表 Excel 具名範圍。

回到頁首

使用連結伺服器

要簡化查詢,您可以在 SQL Server 中將 Excel 活頁簿設定為連結伺服器。如需詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
306397 如何搭配使用 Excel 以及 SQL Server 連結伺服器和分散式查詢
以下程式碼會將資料從 Excel 連結伺服器 EXCELLINK 上的「客戶」工作表匯入到名為 XLImport1 的新 SQL Server 表格中:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]				
您也可以使用 OPENQUERY 來以 Passthrough 方式對來源執行查詢,方法如下:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,'SELECT * FROM [Customers$]')				
回到頁首

使用分散式查詢

如果您不想將對於 Excel 活頁簿的持續連線設定為連線伺服器,您可以使用 OPENDATASOURCE 或 OPENROWSET 函式來以特定目的匯入資料。下列程式碼範例也會將資料從 Excel 客戶工作表匯入新的 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

當您使用 SQL Server 的 Microsoft OLE DB (SQLOLEDB) 連線到 ADO 應用程式中的 SQL Server 時,您可以使用<使用分散式查訊>一節中的相同「分散式查詢」語法來將 Excel 資料匯入 SQL Server。

以下 Visual Basic 6.0 程式碼範例需要您將專案參照新增到 ActiveX Data Objects (ADO) 中。此程式碼範例也會示範如何透過 SQLOLEDB 連線來使用 OPENDATASOURCE 與 OPENROWSET。
Dim cn As ADODB.ConnectionDim strSQL As StringDim lngRecsAff As LongSet cn = New ADODB.Connectioncn.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 strSQLcn.Execute strSQL, lngRecsAff, adExecuteNoRecordsDebug.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 strSQLcn.Execute strSQL, lngRecsAff, adExecuteNoRecordsDebug.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 strSQLcn.Execute strSQL, lngRecsAff, adExecuteNoRecordsDebug.Print "Records affected:"& lngRecsAffcn.CloseSet cn = Nothing				
回到頁首

使用 ADO 與 Jet 提供者

上一節的範例將 ADO 搭配 SQLOLEDB Provider 一起使用來連線至您「Excel 對 SQL」匯入的目的地。您也可以使用 Jet 4.0 的 OLE DB Provider 來連線至 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 資料庫進行 ODBC 連線。您可以使用 ODBC 資料來源名稱 (DSN) 或無 DSN 的連線字串:
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 提供者來透過 ADO 連線將 Excel 資料匯入到 SQL Server 中。
Dim cn As ADODB.ConnectionDim strSQL As StringDim lngRecsAff As LongSet cn = New ADODB.Connectioncn.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 strSQLcn.Execute strSQL, lngRecsAff, adExecuteNoRecordsDebug.Print "Records affected:"& lngRecsAff        cn.CloseSet cn = Nothing				
您也可以使用 Jet 提供者支援的此語法來將 Excel 資料匯入到其他 Microsoft Access 資料庫、索引順序存取方法 (ISAM) (「桌面」) 資料庫或 ODBC 資料庫中。

回到頁首

疑難排解

  • 請記住,後面附加了錢幣符號 ($) 的 Excel 物件名稱代表工作表 (例如,Sheet1$),而純物件名稱則代表 Excel 具名範圍。
  • 在某些情況下,尤其是當您使用表格名稱而不是 SELECT 查詢來指定 Excel 來源資料時,目的地 SQL Server 表格中的資料欄會依照字母順序來重新排列。如需有關 Jet 提供者的這個問題的詳細資訊,請按一下下列文件編號,檢視「Microsoft 知識庫」中的下列文件:
    299484 PRB:當您使用 ADOX 擷取 Access 資料表的資料欄時,資料欄會依照字母順序排序
    (機器翻譯)
  • 當 Jet 提供者確定 Excel 資料欄中包含混合文字及數值資料時,Jet 提供者會選取 majority 資料型別,並會將不符合的數值視為 NULL 傳回。如需有關此問題的替代解決方案的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
    194124 PRB:使用 DAO OpenRecordset 時傳回為 NULL 的 Excel 值
    (機器翻譯)
回到頁首
参考
如需有關如何使用 Excel 來作為資料來源的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
257819 如何從 Visual Basic 或 VBA 搭配使用 ADO 與 Excel 資料
如需有關如何將資料傳輸至 Excel 的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
295646 如何使用 ADO 從 ADO 資料來源傳輸資料到 Excel
(機器翻譯)
247412 INFO:將資料從 Visual Basic 傳送至 Excel 的方法
246335 如何利用自動化將 ADO 資料錄集中的資料傳送至 Excel
319951 HOW TO:使用 SQL Server 資料轉換服務將資料傳送到 Excel
306125 如何將資料從 SQL Server 匯入到 Microsoft Excel
(機器翻譯) 回到頁首
excel sql ado dts sqloledb jet
注意 :本文屬於「快速發佈」文章,係由 Microsoft 技術支援或組織內部直接建立。 本文所包含的資訊是為了回應新問題而依現況提供。 因此為了迅速對外發佈,文章內容可能含有印刷錯誤,而且可能會在不另行通知的情況下進行修改。 如需其他考量事項,請參閱使用規定
內容

文章識別碼:321686 - 最後檢閱時間:12/06/2013 14:10:00 - 修訂: 2.0

Microsoft Excel 2000 Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft Excel 2002 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbhowtomaster kbjet KB321686
意見反應