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

發行項
05/04/2023

有數種方式可以將 Excel 檔案中的資料匯入到 SQL Server 或 Azure SQL Database。某些方法可讓您只執行一個步驟，便能直接從 Excel 檔案匯入資料；其他方法會要求先將 Excel 資料匯出成文字 (CSV 檔案) 才可匯入這些文字。

本文摘要說明常用的方法，並提供連結，為您提供更詳細的資訊。針對複雜工具與服務 (例如 SSIS 或 Azure Data Factory) 的完整描述不在此文章的涵蓋範圍內。若要深入了解您感興趣的解決方案，請遵循提供的連結。

方法清單

有數種方式可從 Excel 匯入資料。您可能需要安裝 SQL Server Management Studio (SSMS)，才能使用其中一些工具。

您可以使用下列工具來從 Excel 匯入資料：

先匯出成文字 (SQL Server 和 SQL Database)	直接從 Excel 匯入 (僅限 SQL Server 內部部署)
匯入一般檔案精靈	SQL Server 匯入和匯出精靈
BULK INSERT 陳述式	SQL Server Integration Services (SSIS)
BCP	OPENROWSET 函數
複製精靈 (Azure Data Factory)
Azure Data Factory

如果想要從 Excel 活頁簿匯入多個工作表，則通常必須針對每個工作表執行一次這些工具的任何一個。

重要

若要深入了解，請參閱將資料載入到 Excel 檔案或從 Excel 檔案載入資料的限制與已知問題 (部分機器翻譯)。

匯入和匯出精靈

使用 [SQL Server 匯入和匯出精靈] 從 Excel 檔案直接匯入資料。您也可以將設定儲存為 SQL Server Integration Services (SSIS) 封裝，以便日後自訂及重複使用。

在 SQL Server Management Studio 中，連線到 SQL Server 資料庫引擎的執行個體。
展開 [資料庫] 。
以滑鼠右鍵按一下資料庫。
選取 [工作]。
選擇 [匯入資料] 或 [匯出資料]：

這會啟動精靈：

Connect to an Excel data source

若要深入了解，請檢閱：

Integration Services (SSIS)

如果您熟悉 SQL Server Integration Services (SSIS) 且不想執行 SQL Server 匯入和匯出精靈，請建立在資料流程中使用 Excel 來源與 SQL Server 目的地的 SSIS 封裝。

若要深入了解，請檢閱：

若要開始學習如何建置 SSIS 套件，請參閱如何建立 ETL 套件的教學課程。

Components in the data flow

OPENROWSET 和連結的伺服器

重要

在 Azure SQL Database 中，您無法直接從 Excel 匯入。您必須先將資料匯出成文字 (CSV) 檔案 (部分機器翻譯)。

注意

連接至 Excel 資料來源的 ACE 提供者 (先前稱為 Jet 提供者) 是供互動式用戶端使用。如果您在 SQL 伺服器上使用 ACE 提供者 (特別是在自動化程序或平行執行的程序中)，可能會看到非預期的結果。

分散式查詢

使用 Transact-SQL OPENROWSET 或 OPENDATASOURCE 函式，直接從 Excel 將資料匯入至 SQL Server。此使用方式稱為「分散式查詢」。

重要

在 Azure SQL Database 中，您無法直接從 Excel 匯入。您必須先將資料匯出成文字 (CSV) 檔案 (部分機器翻譯)。

在您可以執行分散式查詢之前，必須啟用 ad hoc distributed queries 伺服器設定選項，如下列範例所示。如需詳細資訊，請參閱特定分散式查詢伺服器設定選項。

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'ad hoc distributed queries', 1;
RECONFIGURE;
GO

下列程式碼範例使用 Sheet1，將資料從 Excel OPENROWSET 工作表匯入至新的資料庫資料表。

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]);
GO

以下是使用 OPENDATASOURCE 的相同範例。

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];
GO

若要將匯入的資料「附加」到「現有」的資料表，而不建立新的資料表，請使用 INSERT INTO ... SELECT ... FROM ... 語法，而不是上述範例中使用的 SELECT ... INTO ... FROM ... 語法。

若要查詢 Excel 資料但不進行匯入，請直接使用標準 SELECT ... FROM ... 語法。

如需分散式查詢的詳細資訊，請參閱下列文章：

分散式查詢 (SQL Server 2019 (15.x) 仍支援分散式查詢，但適用於此功能的文件並未更新。)
OPENROWSET
OPENDATASOURCE

連結的伺服器

您也可以將 SQL Server 到 Excel 檔案的持續連線設定為「連結的伺服器」。下列範例會將資料從現有 Excel 連結伺服器 EXCELLINK 的 Data 工作表匯入至名為 Data_ls 的新 SQL Server 資料庫資料表。

USE ImportFromExcel;
GO
SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
GO

您可以從 SQL Server Management Studio (SSMS)，或是執行系統預存程序 sp_addlinkedserver (如下列範例所示) 來建立連結的伺服器。

DECLARE @RC INT;
DECLARE @server NVARCHAR(128);
DECLARE @srvproduct NVARCHAR(128);
DECLARE @provider NVARCHAR(128);
DECLARE @datasrc NVARCHAR(4000);
DECLARE @location NVARCHAR(4000);
DECLARE @provstr NVARCHAR(4000);
DECLARE @catalog NVARCHAR(128);

-- Set parameter values
SET @server = 'EXCELLINK';
SET @srvproduct = 'Excel';
SET @provider = 'Microsoft.ACE.OLEDB.12.0';
SET @datasrc = 'C:\Temp\Data.xlsx';
SET @provstr = 'Excel 12.0';

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server,
    @srvproduct,
    @provider,
    @datasrc,
    @location,
    @provstr,
    @catalog;

如需連結的伺服器的詳細資訊，請參閱下列文章：

如需連結的伺服器與分散式查詢的更多範例與詳細資訊，請參閱下列文章：

如何搭配使用 Excel 以及 SQL Server 連結伺服器和分散式查詢

必要條件：將 Excel 資料儲存為文字

若要使用此頁面所描述的剩餘方法 (BULK INSERT 陳述式、BCP 工具或 Azure Data Factory)，您必須先將 Excel 資料匯出為文字檔。

在 Excel 中，選取 [檔案] | [另存新檔]，然後選取 [文字檔 (Tab 字元分隔) (*.txt)] 或 [CSV (逗號分隔) (*.csv)] 作為目的地檔案類型。

如果您想要從活頁簿匯出多個工作表，請選取每個工作表，然後重複此程序。 另存新檔命令只會匯出使用中工作表。

提示

若要取得資料匯入工具的最佳結果，請儲存僅包含資料行標題和包含資料之資料列的工作表。如果儲存的資料包含頁面標題、空白行、註解和其他內容，您稍後在匯入資料時可能會看到非預期的結果。

匯入一般檔案精靈

逐步設定「匯入一檔案精靈」的每個頁面，匯入儲存為文字檔的資料。

如先前必要條件一節中所述，您必須將 Excel 資料匯出成文字，才能使用「匯入一般檔案精靈」將它匯入。

如需「匯入一般檔案精靈」的詳細資訊，請參閱將一般檔案匯入 SQL 精靈。

BULK INSERT 命令

BULK INSERT 是您可從 SQL Server Management Studio 執行的 Transact-SQL 命令。下列範例會將來自 Data.csv 逗號分隔檔案的資料載入至現有資料庫資料表。

如先前必要條件一節中所述，您必須將 Excel 資料匯出成文字，才能使用 BULK INSERT 將它匯入。 BULK INSERT 無法直接讀取 Excel 檔案。您可以使用 BULK INSERT 命令匯入儲存在本機或 Azure Blob 儲存體中的 CSV 檔案。

USE ImportFromExcel;
GO
BULK INSERT Data_bi FROM 'C:\Temp\data.csv'
   WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO

如需 SQL Server 和 SQL Database 的詳細資訊與範例，請參閱下列文章：

BCP 工具

BCP 是您從命令提示字元執行的程式。下列範例會將來自 Data.csv 逗號分隔檔案的資料載入至現有 Data_bcp 資料庫資料表。

如先前必要條件一節中所述，您必須將 Excel 資料匯出成文字，才能使用 BCP 將它匯入。 BCP 無法直接讀取 Excel 檔案。用於從儲存在本機儲存體的測試 (CSV) 檔案匯入至 SQL Server 或 SQL Database。

重要

針對儲存在 Azure Blob 儲存體中的文字 (CSV) 檔案，請使用 BULK INSERT 或 OPENROWSET。如需範例，請參閱範例。

bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,

如需 BCP 的詳細資訊，請參閱下列文章：

複製精靈 (ADF)

逐步設定 Azure Data Factory (ADF) 複製精靈的每個頁面，匯入儲存為文字檔的資料。

如先前必要條件一節中所述，您必須將 Excel 資料匯出成文字，才能使用 Azure Data Factory 將它匯入。 Data Factory 無法直接讀取 Excel 檔案。

如需複製精靈的詳細資訊，請參閱下列文章：

Azure Data Factory

如果您熟悉 Azure Data Factory，且不想執行 [複製精靈]，請建立具有 [複製] 活動的管線，以從文字檔複製至 SQL Server 或 Azure SQL Database。

如先前必要條件一節中所述，您必須將 Excel 資料匯出成文字，才能使用 Azure Data Factory 將它匯入。 Data Factory 無法直接讀取 Excel 檔案。

如需使用這些 Data Factory 來源與接收的詳細資訊，請參閱下列文章：

若要開始學習如何使用 Azure Data Factory 來複製資料，請參閱下列文章：

常見錯誤

Microsoft.ACE.OLEDB.12.0 尚未註冊

此錯誤的發生原因為未安裝 OLEDB 提供者。從 Microsoft Access 資料庫引擎 2016 可轉散發套件安裝。如果 Windows 和 SQL Server 都是 64 位元，請務必安裝 64 位元版本。

完整錯誤如下：

Msg 7403, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

無法建立連結伺服器 "(null)" OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的執行個體

這表示 Microsoft OLEDB 並未正確設定。執行下列 Transact-SQL 程式碼來解決此問題：

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;

完整錯誤如下：

Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

64 位元 SQL Server 無法以同處理序方式載入 32 位元 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0"。

使用 64 位元 SQL Server 安裝 32 位元版本的 OLD DB 提供者時，即會發生此錯誤。若要解決此問題，請解除安裝 32 位元版本，並改為安裝 64 位元版本的 OLE DB 提供者。

完整錯誤如下：

Msg 7438, Level 16, State 1, Line 3
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 報告了錯誤。

無法初始化連結伺服器 "(null)" OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的資料來源物件

這兩個錯誤通常表示在 SQL Server 處理序和檔案之間發生權限問題。請確定執行 SQL Server 服務的帳戶具有檔案的完整存取權限。我們建議您不要嘗試從桌面匯入檔案。

完整錯誤如下：

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Share via

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

方法清單

匯入和匯出精靈

Integration Services (SSIS)

OPENROWSET 和連結的伺服器

分散式查詢

連結的伺服器

必要條件：將 Excel 資料儲存為文字

匯入一般檔案精靈

BULK INSERT 命令

BCP 工具

複製精靈 (ADF)

Azure Data Factory

常見錯誤

Microsoft.ACE.OLEDB.12.0 尚未註冊

無法建立連結伺服器 "(null)" OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的執行個體

64 位元 SQL Server 無法以同處理序方式載入 32 位元 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0"。

連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 報告了錯誤。

無法初始化連結伺服器 "(null)" OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的資料來源物件

下一步

意見反應

意見反應

其他資源