文章編號: 321686 - 上次校閱: 2006年3月2日 - 版次: 3.3

如何從 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? (http://support.microsoft.com/kb/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.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 提供者

上一節的範例將 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.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 提供者支援的此語法來將 Excel 資料匯入到其他 Microsoft Access 資料庫、索引順序存取方法 (ISAM) (「桌面」) 資料庫或 ODBC 資料庫中。

回此頁最上方

疑難排解

  • 請記住，後面附加了錢幣符號 ($) 的 Excel 物件名稱代表工作表 (例如，Sheet1$)，而純物件名稱則代表 Excel 具名範圍。
  • 在某些情況下，尤其是當您使用表格名稱而不是 SELECT 查詢來指定 Excel 來源資料時，目的地 SQL Server 表格中的資料欄會依照字母順序來重新排列。如需有關 Jet 提供者的這個問題的詳細資訊，請按一下下列文件編號，檢視「Microsoft 知識庫」中的下列文件：
    299484? (http://support.microsoft.com/kb/299484/ ) PRB: Columns Are Sorted Alphabetically When You Use ADOX to Retrieve Columns of Access Table
  • 當 Jet 提供者確定 Excel 資料欄中包含混合文字及數值資料時，Jet 提供者會選取 majority 資料型別，並會將不符合的數值視為 NULL 傳回。如需有關此問題的替代解決方案的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
    194124? (http://support.microsoft.com/kb/194124/ ) PRB:Excel Values Returned as NULL Using DAO OpenRecordset
回此頁最上方

?考

如需有關如何使用 Excel 來作為資料來源的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
257819? (http://support.microsoft.com/kb/257819/ ) 如何從 Visual Basic 或 VBA 搭配使用 ADO 與 Excel 資料
如需有關如何將資料傳輸至 Excel 的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
295646? (http://support.microsoft.com/kb/295646/ ) How To Transfer Data from ADO Data Source to Excel with ADO
247412? (http://support.microsoft.com/kb/247412/ ) INFO：將資料從 Visual Basic 傳送至 Excel 的方法
246335? (http://support.microsoft.com/kb/246335/ ) 如何利用自動化將 ADO 資料錄集中的資料傳送至 Excel
319951? (http://support.microsoft.com/kb/319951/ ) HOW TO：使用 SQL Server 資料轉換服務將資料傳送到 Excel
306125? (http://support.microsoft.com/kb/306125/ ) How To Import Data from Microsoft SQL Server into Microsoft Excel
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft Excel 2000 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64 bit (all editions)
  • 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
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。