共用方式為

使用 SQL Server Integration Services 將數據從 Excel 匯入或將數據匯出至 Excel （SSIS）

本文說明如何使用 SQL Server Integration Services （SSIS） 將數據從 Excel 匯入或將數據匯出至 Excel。 本文也說明必要條件、限制和已知問題。

您可以藉由建立 SSIS 套件並使用 Excel 連接管理員和 Excel 來源或 Excel 目的地，從 Excel 匯入數據，或將數據導出至 Excel。 您也可以使用以 SSIS 為基礎的 SQL Server 匯入和匯出精靈。

本文包含三組資訊，能幫助您從 SSIS 中成功使用 Excel，並瞭解及解決常見問題。

  1. 您需要的檔案。
  2. 當您從 Excel 載入資料時，必須提供的資訊。
  3. 已知問題和限制。

取得連線至 Excel 所需的檔案

您必須先下載 Excel 的聯機組件，才能從 Excel 匯入數據或將數據匯出至 Excel。 預設不會安裝 Excel 的聯機組件。

在這裡下載最新版的Excel連結元件： Microsoft Access Database Engine 2016 可再分發套件。

最新版本的元件可以開啟舊版 Excel 所建立的檔案。

請確定您下載 Access Database Engine 2016 可再發行套件，而不是 Microsoft Access 2016 Runtime。

如果計算機已經有32位版本的Office，則必須安裝32位版本的元件。 您也必須確定以 32 位模式執行 SSIS 套件，或執行 32 位版本的匯入和導出精靈。

如果您有 Office 365 訂閱，當您執行安裝程式時，可能會看到錯誤訊息。 錯誤表示您無法將下載的檔案與已安裝的 Office 隨選即用元件同時安裝。 若要略過此錯誤訊息，請開啟命令提示字元視窗，並使用 /quiet 參數以安靜模式執行您下載的 .EXE 檔案來進行安裝。 例如：

C:\Users\<user name>\Downloads\AccessDatabaseEngine.exe /quiet

如果您在安裝 2016 可轉散發套件時遇到問題，請改為從這裡安裝 2010 可轉散發套件：Microsoft Access Database Engine 2010 Redistributable。 Excel 2013 沒有可轉散發套件。）

指定 Excel

第一個步驟是指出您想要連線到 Excel。

在 SSIS 中

在 SSIS 中，建立 Excel 連接管理員以連線到 Excel 來源或目的地檔案。 有數種方式可以建立連接管理員：

  • 在 [ 連接管理器 ] 區域中，以滑鼠右鍵按兩下並選取[ 新增連線]。 在 [ 新增 SSIS 連接管理器] 對話框中，選取 [EXCEL ]，然後選取 [新增]。

  • 在 [SSIS] 功能表上，選取 [ 新增連線]。 在 [ 新增 SSIS 連接管理器] 對話框中，選取 [EXCEL ]，然後選取 [新增]。

  • 在 Excel 來源編輯器或Excel 目的地編輯器的 連接管理器 頁面上，設定 Excel 來源 或 Excel 目的地 的同時，建立連接管理員。

在 SQL Server 匯入和導出精靈中

在 [匯入和匯出精靈] 的 [選擇數據源] 或 [選擇目的地] 頁面上，選取 [數據源] 清單中的 [Microsoft Excel]。

如果您在數據源清單中看不到 Excel，請確定您正在執行 32 位精靈。 Excel 聯機組件通常是 32 位檔案，在 64 位精靈中看不到。

Excel 檔案和檔案路徑

要提供的第一個資訊是 Excel 檔案的路徑和檔名。 您可以在 SSIS 套件的 Excel 連接管理器編輯器 中，或在 [匯入和匯出精靈] 的 [ 選擇數據源 ] 或 [ 選擇目的地 ] 頁面上提供這項資訊。

以下欄格式輸入路徑和檔案名稱：

  • 針對本機電腦上的檔案， C:\TestData.xlsx。

  • 針對網路共用上的檔案， \\Sales\Data\TestData.xlsx。

或者，按兩下 [ 瀏覽 ] 以使用 [ 開啟 ] 對話框尋找電子表格。

這很重要

您無法連線到受密碼保護的 Excel 檔案。

Excel 版本

要提供的第二個資訊是 Excel 檔案的版本。 您可以在 SSIS 套件的 Excel 連接管理器編輯器 中，或在 [匯入和匯出精靈] 的 [ 選擇數據源 ] 或 [ 選擇目的地 ] 頁面上提供這項資訊。

選取用來建立檔案或其他相容版本的 Microsoft Excel 版本。 例如，如果您在安裝 2016 聯機組件時遇到問題，您可以在此清單中安裝 2010 元件，然後選取 [Microsoft Excel 2007-2010 ]。

如果您只安裝舊版的聯機組件，您可能無法在清單中選取較新的 Excel 版本。 Excel 版本清單包含 SSIS 支援的所有 Excel 版本。 此清單中項目的存在並不表示已安裝這些必要的連接組件。 例如，即使您尚未安裝 2016 聯機組件，Microsoft Excel 2016 仍會出現在清單中。

第一列有欄位名稱

如果您要從 Excel 匯入數據，下一個步驟是指出數據的第一個數據列是否包含資料行名稱。 您可以在 SSIS 套件的 Excel 連接管理器編輯器 中，或在 [匯入和匯出精靈] 的 [ 選擇數據源 ] 頁面上提供這項資訊。

  • 如果您停用此選項，因為源數據不包含數據行名稱，精靈會使用 F1、F2 等等作為數據行標題。
  • 如果數據包含數據行名稱，但您停用此選項，精靈會將數據行名稱匯入為數據的第一個數據列。
  • 如果數據不包含數據行名稱，但您可以啟用此選項，精靈會使用源數據的第一個數據列作為數據行名稱。 在此情況下，源數據的第一個數據列不再包含在數據本身中。

如果您要從 Excel 匯出資料，而且啟用此選項，匯出數據的第一個數據列會包含資料行名稱。

工作表和範圍

有三種類型的Excel物件可用來做為數據的來源或目的地：工作表、具名範圍，或您使用其位址指定的未命名單元格範圍。

  • 工作表。 若要指定工作表，請將$字元附加至工作表名稱的結尾，並在字元串周圍新增分隔符 - 例如 [Sheet1$]。 或者，尋找以字元 $ 結尾的名稱，這些名稱存在於現有的數據表和檢視中。

  • 具名範圍。 若要指定具名範圍，請提供範圍名稱，例如 MyDataRange。 或者，在現有數據表和視圖列表中，尋找名稱不是以$字元結尾的項目。

  • 未命名的範圍。 若要指定您尚未命名的儲存格範圍，請將 $ 字元附加至工作表名稱的結尾、新增範圍規格，以及新增字串周圍的分隔符 - 例如 [Sheet1$A1：B4]。

若要選取或指定您想要作為資料來源或目的地的 Excel 物件類型，請執行下列其中一項動作：

在 SSIS 中

在 SSIS 中，在 Excel 來源編輯器或 Excel 目的地編輯器的 [連接管理員] 頁面上，執行下列其中一項動作：

  • 若要使用 工作表 或 具名範圍，請選取 [數據表] 或 [檢視 ] 作為 [數據存取模式]。 然後，在 [Excel 工作表的名稱 ] 清單中，選取工作表或具名範圍。

  • 若要使用您使用其位址指定的 未命名範圍 ，請選取 [SQL] 命令 作為 [資料存取模式]。 然後，在 [SQL 命令文字 ] 字段中，輸入類似下列範例的查詢：

    SELECT * FROM [Sheet1$A1:B5]

在 SQL Server 匯入和導出精靈中

在 [匯入和匯出精靈] 中，執行下列其中一項動作：

  • 當您從 Excel 匯入 時，請執行下列其中一項動作：

    • 若要使用 工作表 或 具名範圍，請在 [ 指定數據表複製或查詢 ] 頁面上，選取 [從一或多個數據表或檢視表複製數據]。 然後，在 [ 選取源數據表和檢視] 頁面上的 [ 來源 ] 數據行中，選取源工作表和具名範圍。

    • 若要使用您指定其位址 的未命名範圍 ，請在 [ 指定數據表複製或查詢 ] 頁面上，選取 [ 寫入查詢] 以指定要傳輸的數據。 然後，在 [ 提供來源查詢] 頁面上，提供類似下列範例的查詢：

      SELECT * FROM [Sheet1$A1:B5]

  • 當您 匯出 至 Excel 時，請執行下列其中一項動作：

    • 若要使用 工作表 或 具名範圍，請在 [ 選取源數據表和檢視 ] 頁面上的 [ 目的地 ] 數據行中，選取目的地工作表和具名範圍。

    • 若要使用以位址指定 之未命名的範圍 ，請在 [ 選取源數據表和檢視 ] 頁面上的 [ 目的地 ] 資料行中，以下列格式輸入範圍，而不使用分隔符： Sheet1$A1:B5。 精靈會新增分隔符。

選取或輸入要匯入或匯出的 Excel 物件之後，您也可以在精靈的 [ 選取源數據表和檢視 ] 頁面上執行下列動作：

  • 選取 編輯對應，以檢閱來源與目的地之間的欄位映射。

  • 請選取 預覽 來預覽範例數據，以確認是否符合預期。

數據類型的問題

數據類型

Excel 驅動程式只會辨識一組有限的數據類型。 例如，所有數值欄都會解譯為雙精度浮點數（DT_R8），而所有字串欄（非備忘錄欄）都會解譯為 255 個字元的 Unicode 字串（DT_WSTR）。 SSIS 會對應 Excel 數據類型，如下所示：

  • 數值 - 雙精確度浮點數 （DT_R8）

  • 貨幣 - 貨幣（DT_CY）

  • 布林值 - 布林值 （DT_BOOL）

  • 日期/時間 - 日期時間 （DT_DATE）

  • 字串 - Unicode 字串，長度 255 （DT_WSTR）

  • 備忘 - Unicode 文字串流 （DT_NTEXT）

數據類型和長度轉換

SSIS 不會隱含轉換數據類型。 因此，您可能必須先使用衍生數據行或數據轉換轉換，在將數據載入 Excel 以外的目的地之前明確地轉換 Excel 數據，或先從 Excel 以外的來源轉換數據，再將它載入 Excel 目的地。

以下是一些可能需要的轉換範例：

  • Unicode Excel 字串數據行與具有特定代碼頁的非 Unicode 字串資料行之間的轉換。

  • 在 255 個字元的 Excel 字串資料行與不同長度的字串數據行之間進行轉換。

  • 雙精確度 Excel 數值數據行與其他類型的數值數據行之間的轉換。

小提示

如果您使用匯入和匯出精靈，而且您的數據需要其中一些轉換，精靈會為您設定必要的轉換。 因此，即使您想要使用 SSIS 套件，使用匯入和匯出精靈建立初始套件可能很有用。 讓精靈為您建立及設定連接管理員、來源、轉換和目的地。

匯入的問題

空白數據列

當您將工作表或具名範圍指定為來源時，驅動程式會讀取連續的 儲存格區塊， 從工作表或範圍左上角的第一個非空白單元格開始。 因此，您的數據不需要在數據列 1 中啟動，但源數據中不能有空的數據列。 例如，在數據列標頭和數據行之間不能有空白列，或工作表頂端的標題後面不能接著空白列。

如果您的資料上方有空的數據列，您就無法將數據查詢為工作表。 在 Excel 中，您必須選取數據範圍，並將名稱指派給範圍，然後查詢具名範圍，而不是工作表。

遺漏值

Excel 驅動程式會讀取指定來源中的特定數據列數（預設為八個數據列），以猜測每個數據行的數據類型。 當數據行似乎包含混合數據類型，特別是與文字數據混合的數值數據時，驅動程式會決定偏向多數數據類型，並針對包含其他類型數據的單元格傳回 Null 值。 （在平局中，數值類型會獲勝。Excel 工作表中的大部分儲存格格式設定選項似乎不會影響此資料類型的判斷。

您可以藉由指定匯入模式將所有值匯入為文字，來修改 Excel 驅動程式的這個行為。 若要指定匯入模式，請在 [屬性] 視窗中，於 Excel 連接管理員的連接字串中，新增 IMEX=1 至 [擴充屬性 ] 的值。

截斷的文字

當驅動程序判斷 Excel 資料行包含文字數據時，驅動程式會根據所取樣的最長值來選取數據類型（字串或備忘）。 如果驅動程式在所取樣的數據列中未發現超過 255 個字元的值，則會將數據行視為 255 個字元的字串數據行，而不是備忘數據行。 因此，可能會截斷超過 255 個字元的值。

若要在不截斷的情況下從備忘數據行匯入數據，您有兩個選項：

  • 務必確認至少其中一個被抽樣的資料列中的備忘欄包含字元長度超過 255 的值

  • 增加驅動程序取樣的數據列數目，以包含這類數據列。 您可以透過在下列登錄機碼下增加 TypeGuessRows 的值來增加取樣的列數：

可重分發元件版本 登錄鍵
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

匯出的問題

建立新的目的地檔案

在 SSIS 中

使用您要建立之新 Excel 檔案的路徑和檔名，建立 Excel 連接管理員。 然後，在 Excel 目的地編輯器中，針對 [Excel] 工作表的名稱，選取 [ 新增 ] 以建立目的地工作表。 此時，SSIS 會使用指定的工作表建立新的 Excel 檔案。

在 SQL Server 匯入和導出精靈中

在 [ 選擇目的地] 頁面上，選取 [ 瀏覽]。 在 [ 開啟 ] 對話框中，流覽至您要建立新 Excel 檔案的資料夾、提供新檔案的名稱，然後選取 [ 開啟]。

匯出至足夠大的範圍

當您將範圍指定為目的地時，如果範圍的數據 行少於源 數據，就會發生錯誤。 不過，如果您指定的 範圍的數據列 少於源數據列，精靈會繼續寫入數據列，而不會發生錯誤，並擴充範圍定義以符合新的數據列數目。

匯出長文字值

在您成功將超過 255 個字元的字串儲存至 Excel 資料行之前，驅動程式必須將目的地數據行的數據類型辨識為 備忘 ，而不是 字串。

  • 如果現有的目的地數據表已經包含數據列，則驅動程序取樣的前幾個數據列必須至少包含備忘數據行中超過 255 個字元的值實例。

  • 如果在封裝設計期間或在運行時間或匯入和匯出精靈建立新的目的地數據表，則 CREATE TABLE 語句必須使用LONGTEXT （或其同義字之一）作為目的地備忘數據行的數據類型。 在精靈中檢查CREATE TABLE語句，並視需要加以修改。 然後，在 [資料行對應] 頁面上，單擊 [建立目的地資料表] 選項旁的 [編輯 SQL]。

相關內容

如需本文中所述之元件和程序的詳細資訊，請參閱下列文章：

關於 SSIS

Excel 連接管理員
Excel 來源
Excel 輸出位置
使用 Foreach 迴圈容器來執行 Excel 檔案和資料表迴圈
使用腳本任務處理 Excel 檔案

關於 SQL Server 匯入和導出精靈

連接至 Excel 數據源
透過匯入和匯出精靈的簡單範例開始使用

將 Excel 中的資料匯入 SQL Server 或 Azure SQL Database

  • Last updated on