SQL Server 讀取 Excel/CSV/TXT 檔案完整指南：OPENROWSET、OPENDATASOURCE 與外部表方案

前置環境設定

1. 啟用 Ad Hoc Distributed Queries：

   EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
   EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

2. 安裝 ACE 驅動 (64 位元 SQL Server 必裝 64 位元驅動)：
   • Microsoft Access Database Engine 2016 Redistributable
   • 安裝參數：AccessDatabaseEngine_X64.exe /passive (避免與 32 位元 Office 衝突)
3. 設定提供者屬性 (SSMS 物件總管 → Server Objects → Linked Servers → Providers → Microsoft.ACE.OLEDB.12.0 → 勾選 Dynamic Parameters、Allow Inprocess、DisallowAdhocAccess=0)

方案一：OPENROWSET 單次查詢 (適合臨時分析)

讀取 Excel (.xlsx/.xls)

-- .xlsx (Excel 12.0 Xml)
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\Data\Sales.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"',
    'SELECT * FROM [Sheet1$]');

-- .xls (Excel 8.0)
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\Data\Sales.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1"',
    'SELECT * FROM [Sheet1$]');

-- 指定範圍
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\Data\Sales.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"',
    'SELECT * FROM [Sheet1$A1:D100]');

讀取 CSV/TXT 文字檔

-- 使用 Jet/ACE Text 驅動
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Text;HDR=YES;FORMAT=CSVDelimited;Database=D:\Data\',
    'SELECT * FROM [Sales.csv]');

-- 或指定分隔符 (Tab 等)
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Text;HDR=YES;FORMAT=TabDelimited;Database=D:\Data\',
    'SELECT * FROM [Sales.txt]');

方案二：OPENDATASOURCE 臨時連接 (語法較簡潔)

SELECT * INTO #TempSales
FROM OPENDATASOURCE(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\Data\Sales.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"'
)...[Sheet1$];

方案三：建立連結伺服器 (適合重複存取)

EXEC sp_addlinkedserver
    @server = 'LINK_EXCEL_SALES',
    @srvproduct = '',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'D:\Data\Sales.xlsx',
    @provstr = 'Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"';

-- 查詢
SELECT * FROM LINK_EXCEL_SALES...[Sheet1$];

-- 移除
EXEC sp_dropserver 'LINK_EXCEL_SALES', 'droplogins';

方案四：外部表 - PolyBase (SQL Server 2019+ 推薦，效能最佳)

建立外部資料源

CREATE EXTERNAL DATA SOURCE ExcelSales
WITH (
    LOCATION = 'D:\Data\',
    PUSH_DOWN = OFF
);

建立外部檔案格式

CREATE EXTERNAL FILE FORMAT CSVFormat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2,  -- 跳過標題
        USE_TYPE_DEFAULT = TRUE
    )
);

建立外部表

CREATE EXTERNAL TABLE dbo.Sales_External (
    OrderID INT,
    CustomerName NVARCHAR(100),
    Amount DECIMAL(18,2),
    OrderDate DATE
)
WITH (
    DATA_SOURCE = ExcelSales,
    FILE_FORMAT = CSVFormat,
    LOCATION = 'Sales.csv',
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 100
);

查詢外部表 (支援 predicate pushdown、統計資訊)

SELECT CustomerName, SUM(Amount) AS TotalAmount
FROM dbo.Sales_External
WHERE OrderDate >= '2024-01-01'
GROUP BY CustomerName
ORDER BY TotalAmount DESC;

Extended Properties 關鍵參數詳解

Schema.ini 精確控制欄位型別 (文字檔專用)

在文字檔同目錄建立 Schema.ini：

[Sales.csv]
ColNameHeader=True
Format=CSVDelimited
CharacterSet=65001
Col1=OrderID Long
Col2=CustomerName Text Width 100
Col3=Amount Currency
Col4=OrderDate DateTime

驅動將依此定義強制轉換，解決型別推斷錯誤。

常見錯誤排查

效能與最佳實踐比較

自動化匯入範本：參數化預存程序

CREATE PROCEDURE usp_ImportFile
    @FilePath NVARCHAR(500),
    @FileType NVARCHAR(20) = 'EXCEL',  -- EXCEL, CSV, TXT
    @SheetName NVARCHAR(100) = 'Sheet1',
    @TargetTable NVARCHAR(200),
    @TruncateTarget BIT = 1
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Provider NVARCHAR(100) = 'Microsoft.ACE.OLEDB.12.0';
    DECLARE @ExtProps NVARCHAR(500);
    DECLARE @SQL NVARCHAR(MAX);

    IF @FileType = 'EXCEL' BEGIN
        IF RIGHT(@FilePath, 5) = '.xlsx'
            SET @ExtProps = 'Excel 12.0 Xml;HDR=YES;IMEX=1';
        ELSE
            SET @ExtProps = 'Excel 8.0;HDR=YES;IMEX=1';
    END ELSE IF @FileType = 'CSV' BEGIN
        SET @ExtProps = 'Text;HDR=YES;FORMAT=CSVDelimited;IMEX=1;Database=' + LEFT(@FilePath, LEN(@FilePath) - CHARINDEX('\', REVERSE(@FilePath)));
    END

    IF @TruncateTarget = 1
        SET @SQL = 'TRUNCATE TABLE ' + QUOTENAME(@TargetTable) + '; ';

    SET @SQL += 'INSERT INTO ' + QUOTENAME(@TargetTable) + ' SELECT * FROM OPENROWSET(''' 
        + @Provider + ''', ''' + @ExtProps + ''', ''SELECT * FROM [' + @SheetName + '$]'')';

    EXEC sp_executesql @SQL;
END;