T-SQL——關於SQL讀取Excel文件

• 2021 年 10 月 1 日
• 筆記
• SQL

0. 背景說明

• 某系統上線，需要大量的數據初始化，用戶提供的而是Excel文件。
  期望直接插入到SQL Server數據庫的表中，所以可以按照以下步驟使用MSSM讀取Excel表格中數據，實現Excel到SQL Server的數據批量導入

1. 安裝Access Database Engine

• 首先安裝Access Database Engine 即需要安裝Micsoft.ACE.OLEDB安裝包

  • 微軟官方下載地址：//www.microsoft.com/zh-cn/download/details.aspx?id=13255
  • 注意安裝的SQLServer若是64位則該引擎一定也要64位的，若是安裝32位則報錯無法使用

• 因為我本機已經安裝了Office2007（32位）

  • 在該種情形下，安裝64位的Micsoft.ACE.OLEDB則會報錯：

    裝64(32)為office Access驅動的時候無法安裝64(32)位版本的Office因為在您的PC上找到了以下32(64)位程序
    

  • 而此時我並不想卸載我的32位Office，或者服務器不允許我卸載32位的程序

  • 上述情形可以使用以下安裝包安裝對應位數的版本即可

  • 百度雲鏈接: 2351144/2018rupg/未在本地計算機上註冊「microsoft.ACE.oledb.12

1. SQL腳本

說明：Excel表格是第一行默認是讀取結果集的列名

--開啟啟用 Ad Hoc Distributed Queries 高級選項，
--在SQL Server中，該選項默認是Disable的，需要顯式啟用（Enable）；
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

--允許在進程中使用ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',
                                    N'AllowInProcess',
                                    1;
--允許動態參數
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',
                                    N'DynamicParameters',
                                    1;


--連接Excel表格的兩種方式
--注意使用OpenDataSouce函數，後使用三個點後連接需要獲取的工作簿名稱
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=E:\1.xlsx')...[Sheet1$];
--注意OPENROWSET第二個參數是Excel中的工作簿名稱
SELECT *
FROM
    OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\1.xlsx;hdr=yes;imex=1', Sheet1$);

--關閉第一開啟的配置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

3. .net項目中通過Micsoft.ACE.oledb讀取Excel文件

見:.net程序讀取Excel文件