sql读取Excel方法

Sql Server 读取excel方法：

select * from OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=存放Excel\Excel名.xls',[Sheet名$])

Example：

select * from OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;IMEX=1;Database=f:\SuccessBak\Student.xls',
[Class$])

--注意:

--1.excel处于关闭状态,即不能处于被打开状态

--2.excel文件所处路径及文件名、工作簿的名称尽量不要出现汉字，尽量以英文命名

--3.注意[Class$]',工作簿名后的$是必须的.

--4.Excle必须与Sql Server在同一电脑上。

在使用这个sql语句的过程中，我们可能会碰到下面的错误

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online

 sqlserver 阻止了使用'OpenRowset/OpenDatasource'的组件，所以解决办法如下：

我们使用sql语句来开启这个功能

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

如果遇到以下问题，请检查文章开始处提到的注意：

服务器: 消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。
OLE DB 错误跟踪［OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:  提供程序未给出有关错误的任何信息。］。

 

补充:

读取Excel时,可能会遇到以下问题:

  当列中存在数字行和字符串行时,会遇到有一种读出为null的情况,该如何处理呢?

1、在数字行的数据前加'；

2、在连接字符串中加入:IMEX=1；

但是第二种方法有时还不行.当前八行数据均为数字或字符串时,后面再有其他类型数据时,仍然读不出来 。

解释如下:

IMEX是用来告诉驱动程序使用Excel文件的模式，其值有0、1、2三种，分别代表导出、导入、混合模式。当我们设置IMEX＝1时将强制混合数据转换为文本，

但仅仅这种设置并不可靠，IMEX＝1只确保在某列前8行数据至少有一个是文本项的时候才起作用，它只是把查找前8行数据中数据类型占优选择的行为作了

略微的改变。例如某列前8行数据全为纯数字，那么它仍然以数字类型作为该列的数据类型，随后行里的含有文本的数据仍然变空。 

另一个改进的措施是IMEX＝1与注册表值TypeGuessRows配合使用，TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型，

默认为“8”。可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来更改采样行数。

但是这种改进还是没有根本上解决问题，即使我们把IMEX设为“1”， TypeGuessRows设得再大，例如1000，假设数据表有1001行，

某列前1000行全为纯数字，该列的第1001行又是一个文本，ISAM驱动的这种机制还是让这列的数据变成空。