使用mssql查询excel文件

编者：李国帅

qq：9611153 微信lgs9611153

时间：2008-3-11

背景原因:

这里介绍了几种在MSSql中访问Excel的方法，内容转载于其人文档。

 

所需资源：

MSSql,Excel

 

访问方法:

使用 SQL-DMO 配置的链接服务器：

        

 SELECT * FROM XLTEST_DMO...Sheet1$

 

您还可以使用 OPENQUERY 以“passthrough”（传递）方式查询 Excel 链接服务器，如下所示：

     

    SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')

OPENQUERY 需要的第一个参数是链接服务器名称。工作表名称之间需要用分隔符分开，如上所示。

 

您还可以使用以下查询获得 Excel 链接服务器上所有可用表的列表：

        

EXECUTE SP_TABLES_EX 'XLTEST_DMO'

使用分布式查询来查询 Excel

 

您可以使用 SQL Server 分布式查询和 OPENDATASOURCE 或 OPENROWSET 函数临时查询很少访问的 Excel 数据源，如下所示：       

  SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

                   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$

 

注意，OPENROWSET 对第二个（“提供程序字符串”）参数使用一种非常规语法：        

 SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

            'Excel 8.0;Database=c:\book1.xls', Sheet1$)

 

sqlserver帮助：

最好的方法来自于正规的帮助，比如sqlserver自己的帮助。

--Read Excel Sheet using OpenDataSource
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="H:\tempNorthProducts";
    User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
--Read Excel Sheet using OpenRowSet
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=H:\tempNorthProducts', 'Select * from [Sheet1$]')

USE pubs
GO
--Read SQL Server table using OpenDataSource
--SELECT * FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=ETL;User ID=Weblogin;Password=Web').CM_Sales.dbo.users
SELECT a.* FROM OPENROWSET('SQLOLEDB','(local)';'sa';'123456','SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO

--Read SQL Server table using OPENROWSET
SELECT * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=123456', pubs.dbo.authors)
GO
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=123456',pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname

GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
   AS a

   GO
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;HDR=Yes;DATABASE=H:\','SELECT au_id,au_lname,au_fname,address FROM [DT#txt]')
(au_id,au_lname,au_fname,address)
SELECT au_id,au_lname,au_fname,address FROM  pubs.dbo.authors

select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;HDR=Yes;DATABASE=H:\',[DT#txt])