學海無邊，書囊無底

  使用T-SQL查詢excel檔案中的資料是最省時省力的，執行前須確定SQL Server是否允許使用 OPENROWSET 和 OPENDATASOURCE 進行特定分散式查詢(參考註1)。由於需投過SQL Server讀取excel檔案中的資料，因此SQL Server執行帳號需要擁有存取該檔案的權限，可參考(註2)說明調整SQL Server啟動帳號。

建立excel檔案，在檔案內容建立以下資料。接著使用不同的

我的執行環境如下
作業系統：Windows 7 (64位元)
執行環境 ：SQL Server 2008 R2 (64位元)+Office (64位元)

以下使用 ACE 引擎查詢 Excel 97-2003 檔案內容(註1)，由於執行語法的那台SQL Server環境中已安裝了OFFICE 64位元，因此在下圖可看到Microsoft.ACE.OLEDB.12.0連接。ACE 引擎是随 Office 2007 一起發布的資料庫連接组件，可同時查詢舊有的Office 97-2003與Office 2007。值得一提的是，Microsoft.ACE.OLEDB.12.0 可以查詢正在開啟的 Excel 文件，而 Microsoft.Jet.OLEDB.4.0 則不行。

使用OpenRowSet的語法如下。
當HDR=Yes，表示 Excel 表的第一行為資料行名稱，應忽略第一行資料。
IMEX=1 表示欄位中數字與文字混合時，強制解譯為本文。(參考這裡)

--使用OpenRowSet
SELECT * FROM OpenRowSet('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=Yes;IMEX=1;Database=c:\temp\Employee.xls',
'SELECT * FROM [工作表1$]')

SELECT * FROM OpenRowSet('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=Yes;IMEX=2;Database=c:\temp\Employee.xls',[工作表1$])

使用OpenDataSource基本語法：OPENDATASOURCE ( provider_name, init_string )
OPENDATASOURCE 可做為四部分名稱的第一部分使用，來參考 SELECT、INSERT、UPDATE 或 DELETE 陳述式中的資料表或檢視名稱，或參考 EXECUTE 陳述式中的遠端預存程序。

--使用OpenDataSource
SELECT * FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;HDR=Yes;IMEX=1;Database=c:\temp\Employee.xls')...[工作表1$]

SELECT * FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0', 
'Data Source=c:\temp\Employee.xls;
Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[工作表1$]

查 尋結果如下

--使用OpenRowSet
SELECT * FROM OpenRowSet('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;HDR=Yes;IMEX=1;Database=c:\temp\Employee.xlsx', 'select * from [工作表1$]')   
SELECT * FROM OpenRowSet('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;HDR=Yes;IMEX=1;Database=c:\temp\Employee.xlsx', [工作表1$])   

--使用OpenDataSource
SELECT * FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;HDR=Yes;IMEX=1;Database=c:\temp\Employee.xlsx')...[工作表1$]   
SELECT * FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=c:\temp\Employee.xlsx;
Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[工作表1$]  

環境 : SQL Server 2008 R2 (32位元)
作業系統 XP (32位元)

Jet 引擎大家都很熟悉，可以查詢 Office 97-2003，但不能查詢 Office 2007。

--使用OpenRowSet
SELECT * FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;HDR=Yes;IMEX=1;Database=c:\temp\Employee.xls', 
'SELECT * FROM  [工作表1$]')  

SELECT * FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;HDR=Yes;IMEX=1;Database=c:\temp\Employee.xls', [工作表1$])   

--使用OpenDataSource
SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;HDR=Yes;IMEX=1;Database=c:\temp\Employee.xls')...[工作表1$]   

SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 
'Data Source=c:\temp\Employee.xls;Extended Properties="Excel 8.0
;HDR=Yes;IMEX=1"')...[工作表1$] 

當伺服器環境是32位元，也可使用Microsoft.Jet.OLEDB.4.0連結Excle檔案存取資料。若是你的環境是SQL Server 2008 R2 x64 版本，則會有以下錯誤訊息。這是因為目前Microsoft.Jet.OLEDB.4.0 沒有 x64 位元版本，產生的錯誤：訊息 7308，層級 16，狀態 1，行 1
OLE DB 提供者 'Microsoft.Jet.OLEDB.4.0' 不能用來散佈查詢，因為提供者是設定成以單一執行緒 Apartment 模式執行。

補充                                                                                      

(註1)SQL Server預設不允許使用 OPENROWSET 和 OPENDATASOURCE 進行特定分散式查詢。使用 OpenRowSet前，需使用sp_configure 來啟用 'Ad Hoc Distributed Queries'

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

訊息 15281，層級 16，狀態 1，行 1
SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取，因為此元件已經由此伺服器的安全性組態關閉。系統管理員可以使用 sp_configure 來啟用 'Ad Hoc Distributed Queries' 的使用。如需有關啟用 'Ad Hoc Distributed Queries' 的詳細資訊，請參閱《SQL Server 線上叢書》中的＜介面區組態＞(Surface Area Configuration)。

(註2)記得確認 SQL Server伺服器的啟動帳號帳號，擁有開啟excel檔案的權限

以筆者的SQL Server Configuration Manager 為例 ，SQL Server啟動帳號為  .\Sndy 

若要能夠讓t-sql 指令碼直接讀取c:\temp 路徑內的 檔案，請開啟資料夾的內容並確認安全性是否有受與讀取、寫入..等權限

否則執行時會出現以下錯誤訊息

訊息 7399，層級 16，狀態 1，行 1
連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 報告了錯誤。拒絕存取。
訊息 7350，層級 16，狀態 2，行 1
無法從連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 取得資料行資訊。

(註3)若執行的環境沒有安裝OFFICE 軟體，自然就不會有Microsoft.ACE.OLEDB.12.0連線物件。展開物件總館中LinkServers資料夾底下的Providers也可觀察此SQL Server是否提供ACE連接物件。如下圖所示的SQL Server環境中執行。

SELECT * FROM OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=c:\temp\Employee.xls', 'SELECT * FROM [工作表1$]')

執行結果將出現以下錯誤訊息。
Msg 7403, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

因此需要自行下載Microsoft Access Database Engine 2010 可轉散發套件。可依需求選擇安裝32位元或是64位元。

由於Microsoft.Jet.OLEDB.4.0 目前沒有 x64 位元版本。因此使用OPENROWSET 與 OPENDATASOURCE 函數查詢Excle資料時，可改為Microsoft.ACE.OLEDB.12.0。若執行的伺服器沒有安裝Office 64位元版，可自行下載安裝 64位元版本的 Microsoft Access Database Engine 2010

經由Microsoft.ACE.OLEDB.12.0連接可取得以下檔案類型
          Excel：*.xls、*.xlsx。
          Access：*.mdb、*.accdb。

參考:sharedderrick
使用.net程式取得excle資料可參考這裡

http://sanchen.blogspot.tw/2007/08/imex1-oledb-excel-null.html