當前位置：首頁 > 人文社科 > 生活经验 >内容正文

生活经验

使用OpenRowSet操作Excel Excel导入数据库

發布時間：2023/11/27 生活经验 33 豆豆

生活随笔收集整理的這篇文章主要介紹了使用OpenRowSet操作Excel Excel导入数据库小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

使用 OpenRowSet 和 OpenDataSource 訪問 Excel 97-2007

測試文件：D:\97-2003.xls和D:\2007.xlsx，兩個文件的內容是一模一樣的。

測試環境：SQL Server 2000 / 2005。

--------------------------------------------------------------------------------

接口類型

語法一覽

注冊表設置

單一數據類型列的類型解析

混合數據類型列的自然解析

混合數據類型列的強制解析——IMEX=1

如何解決 NULL 值問題

SQL Server 2000 中的列順序問題

如何訪問隱藏的 Sheet

如何訪問非常規命名的 Sheet

--------------------------------------------------------------------------------

接口類型

有兩種接口可供選擇：Microsoft.Jet.OLEDB.4.0（以下簡稱 Jet 引擎）和Microsoft.ACE.OLEDB.12.0（以下簡稱 ACE 引擎）。

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

ACE 引擎是隨 Office 2007 一起發布的數據庫連接組件，既可以訪問 Office 2007，也可以訪問 Office 97-2003。

另外：Microsoft.ACE.OLEDB.12.0 可以訪問正在打開的 Excel 文件，而 Microsoft.Jet.OLEDB.4.0 是不可以的。

Microsoft.ACE.OLEDB.12.0 安裝文件：

http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

--------------------------------------------------------------------------------

語法一覽

使用 Jet 引擎或 ACE 引擎訪問，在語法上沒有什么的區別。

view plaincopy to clipboardprint?

-->?Jet 引擎訪問Excel 97-2003

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet1$]')

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', [Sheet1$])

select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls')...[Sheet1$]

select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$]

-->?ACE 引擎訪問Excel 97-2003

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet1$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', [Sheet1$])

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls')...[Sheet1$]

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]

-->?ACE 引擎訪問Excel 2007

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx', 'select * from [Sheet1$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx', [Sheet1$])

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx')...[Sheet1$]

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]

Excel 2007 工作簿文件的擴展名是：xlsx

HDR=Yes/No

可選參數，指定 Excel 表的第一行是否列名，缺省為 Yes，可以在注冊表中修改缺省的行為。

IMEX=1

可選參數，將 Excel 表中混合 Intermixed 數據類型的列強制解析為文本。

--------------------------------------------------------------------------------

注冊表設置

Microsoft.Jet.OLEDB.4.0

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Microsoft.ACE.OLEDB.12.0

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

FirstRowHasNames

設置 HDR 參數的缺省行為，默認為 Yes。

ImportMixedTypes

設置混合列的強制解析類型，默認為文本 Text。

TypeGuessRows

設置用于解析數據類型的取樣行數，默認取樣前 8 行。如果設置為 0，將分析所有數據行，但不建議這樣做，會影響引擎的性能。

注意：Excel 表數據列是單一列數據類型還是混合列數據類型列，是由取樣行決定，而不是整列數據決定。

--------------------------------------------------------------------------------

單一數據類型列的類型解析

Sheet1 的內容如下圖所示，涵蓋了大部分 Excel 的數據類型，其中 longtext 分別有 256 個 A 和 B。

對于單一數據類型列的類型解析，ACE 引擎和 Jet 引擎是一樣的，下面測試 Jet 引擎的數據解析：

view plaincopy to clipboardprint?

use tempdb

select * into #type from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\97-2003.xls', 'select * from [Sheet1$]')

select

a.name,

date_type = b.name + case a.xusertype when 231 then '('+ltrim(a.length/2)+')' else '' end

from

syscolumns a inner join systypes b on a.xusertype = b.xusertype

where

a.id = object_id('#type')

drop table #type

use tempdb

select * into #type from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\97-2003.xls', 'select * from [Sheet1$]')

select

a.name,

date_type = b.name + case a.xusertype when 231 then '('+ltrim(a.length/2)+')' else '' end

from

syscolumns a inner join systypes b on a.xusertype = b.xusertype

where

a.id = object_id('#type')

drop table #type數據類型解析總結

文本：長度 <= 255，解析為 nvarchar(255)，長度 > 255，解析為 ntext。

數值：貨幣解析為 money，其它均解析為 float。

時間：datetime。

--------------------------------------------------------------------------------

混合數據類型列的自然解析

相對于使用 IMEX=1 的強制解析，不使用 IMEX=1，稱為自然解析。下圖是 Sheet2 的內容：

對于混合數據類型列的自然解析，ACE 引擎和 Jet 有細節上的區別，先看測試：

view plaincopy to clipboardprint?

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\97-2003.xls', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\97-2003.xls', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\2007.xlsx', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\97-2003.xls', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\97-2003.xls', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\2007.xlsx', 'select * from [Sheet2$]')

相同地方

取樣行里數值型多于文本型，解析為 float 數值。

取樣行里數值型少于文本型，解析為 nvarchar/ntext 文本。

當解析為 float 數值時，文本類型顯示為 NULL，這點毫無疑問。

相異地方

取樣行里數值型等于文本型，Jet 引擎解析為 float 數值，數值優先，ACE 引擎解析為 nvarchar/ntext 文本，文本優先。

當解析為 nvarchar/ntext 文本時，Jet 引擎將非文本數據顯示為 NULL，ACE 引擎正確顯示。

--------------------------------------------------------------------------------

混合數據類型列的強制解析——IMEX=1

使用 IMEX=1 選參之后，只要取樣數據里是混合數據類型的列，一律強制解析為 nvarchar/ntext 文本。當然，IMEX=1 對單一數據類型列的解析是不影響的。

view plaincopy to clipboardprint?

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:\2007.xlsx', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet2$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:\2007.xlsx', 'select * from [Sheet2$]')

最后一列（num<str），Jet 引擎的自然解析和強制解析都解析為 nvarchar(255)，但是自然解析將數值顯示為 NULL，強制解析卻能正確顯示，這是不一致的地方。

在數據解析的細節方面，ACE 引擎的表現優于 Jet 引擎。在前面提到的文本優先問題、非文本數據的 NULL 值問題，ACE 引擎的解析更合理。

--------------------------------------------------------------------------------

如何解決 NULL 值問題

前 8 行（取樣行）是混合數據類型的列，使用 IMEX=1 選參解決。

前 8 行是文本，行之外有非文本的數據，使用 ACE 引擎解決。

前 8 行是數值，行之外又非數值的數據：

將前 8 行其中一行的單元格式數字設置為文本（如果還不行，可能要手工重寫該單元格，以應用文本格式，不記得是 Office 97 還是 2000 存在這個問題了）；

修改注冊表中的 TypeGuessRows（注冊表設置），增加取樣行數，或設置為 0 全部解析。

目的只有一個，讓取樣行變成混合數據類型的列，然后使用 IMEX=1 選參解決。

--------------------------------------------------------------------------------

SQL Server 2000 中的列順序問題

這是 SQL Server 2000 行集函數 OpenRowSet 和 OpenDataSource 本身的問題，與訪問接口引擎無關，也與 Excel 版本無關。SQL Server 2005 的 OpenRowSet 和 OpenDataSource 不存在這個問題。

上圖是 Sheet3 的內容，連接到 SQL Server 2000 測試看看是什么問題：

view plaincopy to clipboardprint?

--> HDR=Yes

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:\97-2003.xls', [Sheet3$])

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\97-2003.xls')...[Sheet3$]

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes"')...[Sheet3$]

--> HDR=No

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:\97-2003.xls', [Sheet3$])

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\97-2003.xls')...[Sheet3$]

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\2007.xlsx;Extended Properties="Excel 12.0;HDR=No"')...[Sheet3$]

--> HDR=Yes

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:\97-2003.xls', [Sheet3$])

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\97-2003.xls')...[Sheet3$]

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes"')...[Sheet3$]

--> HDR=No

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:\97-2003.xls', [Sheet3$])

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\97-2003.xls')...[Sheet3$]

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\2007.xlsx;Extended Properties="Excel 12.0;HDR=No"')...[Sheet3$]

返回結果集的列順序，是按照列名排序，并不是 Excel 表的列順序。HDR=No 貌似正確，但仔細一看，仍然是按列名排序的。

OpenRowSet(query)

OpenRowSet(query) 可以解決這個列順序的問題，包括后面的訪問隱藏的 Sheet 或非常規命名的 Sheet，都可以用 OpenRowSet(query) 解決。

view plaincopy to clipboardprint?

--> HDR=Yes

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:\97-2003.xls', 'select * from [Sheet3$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\97-2003.xls', 'select * from [Sheet3$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\2007.xlsx', 'select * from [Sheet3$]')

--> HDR=No

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:\97-2003.xls', 'select * from [Sheet3$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\97-2003.xls', 'select * from [Sheet3$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\2007.xlsx', 'select * from [Sheet3$]')

--> HDR=Yes

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:\97-2003.xls', 'select * from [Sheet3$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\97-2003.xls', 'select * from [Sheet3$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\2007.xlsx', 'select * from [Sheet3$]')

--> HDR=No

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:\97-2003.xls', 'select * from [Sheet3$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\97-2003.xls', 'select * from [Sheet3$]')

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\2007.xlsx', 'select * from [Sheet3$]')

--------------------------------------------------------------------------------

如何訪問隱藏的 Sheet

隱藏 Sheet 的訪問情況比較復雜，就不寫測試過程了，歸納一下：

使用 OpenRowSet(query) 肯定可以訪問。

Excel 2007 任何寫法都可以訪問（Jet 引擎不能訪問 Excel 2007）。

打開的 Excel 文件任何寫法都可以訪問（Jet 引擎不能訪問打開的 Excel 文件）。

--------------------------------------------------------------------------------

如何訪問非常規命名的 Sheet

新建一個空白的 Sheet，重命名為 4 保存關閉：

使用 OpenRowSet(query) 可以正常訪問。

其它寫法，用單引號限定名稱 ['4$'] 可以訪問。

OpenRowSet(query) 也可以使用單引號限定訪問：'select * from [''4$'']'

引出最后一個問題，訪問 Excel 97-2003 空白的 Sheet，會返回一行 NULL 值，訪問 Excel 2007 空白的 Sheet，返回空結果集，數據類型均解析為 nvarchar(255)。

會出現的問題:

SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此服務器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用 'Ad Hoc Distributed Queries'。有關啟用 'Ad Hoc Distributed Queries' 的詳細信息

SQL Server 阻止了對組件 \'Ad Hoc Distributed Queries\' 的訪問

??? 在Sql Server中查詢一下Excel文件的時候出現問題：?
SELECT *? FROM OPENROWSET( 'MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=D:\a.xls',[sheet1$])?
??? 結果提示：?
??? SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此服務器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用 'Ad Hoc Distributed Queries'。有關啟用 'Ad Hoc Distributed Queries' 的詳細信息，請參閱 SQL Server 聯機叢書中的 "外圍應用配置器"。?
查詢相關資料，找到解決方法：

??? 啟用Ad Hoc Distributed Queries：新建查詢，輸入?
exec sp_configure 'show advanced options',1?
reconfigure?
exec sp_configure 'Ad Hoc Distributed Queries',1?
reconfigure

??? 執行

??? 使用完成后，關閉Ad Hoc Distributed Queries：?
exec sp_configure 'Ad Hoc Distributed Queries',0?
reconfigure?
exec sp_configure 'show advanced options',0?
reconfigure

SELECT?? *?
FROM????? OPENDATASOURCE(?
???????? 'SQLOLEDB',?
???????? 'Data Source=ServerName;User ID=sa;Password=sa'?
???????? ).DataBaseName.dbo.Table

引用:?http://www.cnblogs.com/lhuser/articles/1487947.html

轉載于:https://www.cnblogs.com/m0488/p/9410985.html

總結

以上是生活随笔為你收集整理的使用OpenRowSet操作Excel Excel导入数据库的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。