使用SQL将Excel导入到sqlserver所遇问题总结

情景先叙：由于业务需要，需要使用SQL语句将Excel中数据导入到sqlserver中，该Excel的第一行需要删除，第二行数据作为导入表的字段名。

接下来，就说说我前前后后所遇到的问题……

 

1.使用度娘搜索得知，可以使用openrowset或opendatasourse来实现Excel的导入，然后我就测试了一下

1   select *
2   from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=D:\单病种excel\QualityReport4400000121.xls',AMI$)

View Code

这是一个简单的查询，可以看到这个查询是可行的。存在的问题就是没有删除第一行，而是把第一行当作表头了(AMI是Excel里左下角的工作表名，默认Sheet1)

要想不把第一行数据当作表头只需要改动openrowset()里的HDR=NO即可。此处直接使用select into 导入到新表，然后借助模糊查询来删除第一行数据

1  select * into tmpTable
2    from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=NO;DATABASE=D:\QualityReport4400000121.xls',AMI$)
3 
4    delete tmpTable where F1 like '单病种质量控制指标%'
5    
6    select * from tmpTable

 

如此便实现了第一步导入数据的目的了。

2. 更改字段名，需要将现在的第一行数据作为数据表的字段名。最容易想到的方法就是将数据写回或导出到Excel表中，再从Excel导入到数据库，采用此方法简单方便不需要过多的处理，其缺点是多次访问数据库外部接口导致性能下降。这只是我的想法，具体写回没测试过，给个网上的参考代码吧

1 insert into Table select * from OpenDataSource
2 ('Microsoft.Jet.OLEDB.4.0','Data Source="D:/BJData.mdb";')...SheetName

 

下面是我用SQL命令写的将第一行数据转为字段名

 1 declare @FID int ,@ColumCount int
 2 declare @SQL varchar(max),@ColumValue varchar(max),@tmpSQL nvarchar(max)
 3 set @FID =1
 4 set @SQL=''
 5 set @tmpSQL=''
 6 --查询源表中的列数
 7 select @ColumCount =count(1) from syscolumns where id = object_id('tmpTable');
 8 --构造动态SQL将第一行数据作为字段名
 9 while @FID<=@ColumCount 
10     begin 
11     --获取第一行各字段的值
12     set @tmpSQL='select @colValue=F'+convert(varchar(50),@FID )+' from tmpTable where F1=''序号'''
13     exec sp_executesql @tmpSQL,N'@colValue varchar(max) output',@ColumValue output 
14     --设置对应字段名
15     set @SQL+=',F'+convert(varchar(50),@FID )+' as '''+@ColumValue+''''
16     set @FID =@FId +1
17     end
18 --构造完整的SQL
19 set @SQL='select '+SUBSTRING(@SQL,2,len(@SQL))+' into NewTable from tmpTable where F1!=''序号'''
20 exec(@SQL)

效果如下

到此为止已经实现了我想要的功能了，在本机上运行完全通过，环境是win7x86,office 2010x86,sqlserver2008x86

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

下面说的是在服务器上遇到的问题。

服务器的环境一般是winserver2008 x64,sqlserver 2008x64，没错都是64位的

1. 组件服务开启

消息 15281，级别 16，状态 1，第 2 行
SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

 解决办法：执行存储过程开启服务，之后这个问题解决了

1 --开启导入功能
2 exec sp_configure 'show advanced options',1
3 reconfigure
4 exec sp_configure 'Ad Hoc Distributed Queries',1
5 reconfigure

还有相关的设置，或许会用到的，也贴出来了

1 --允许在进程中使用ACE.OLEDB.12
2 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
3 --允许动态参数
4 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

 

2. 不再报上面的错误了，接着出现的是64位数据库引擎不兼容32位的访问接口

消息 7308，级别 16，状态 1，第 2 行
因为 OLE DB 访问接口 'Microsoft.Jet.OLEDB.4.0' 配置为在单线程单元模式下运行，所以该访问接口无法用于分布式查询。

解决方法：下载64位插件安装，给出下载地址

http://www.microsoft.com/zh-CN/download/details.aspx?id=13255

然后更改openrowset里的字符串'MICROSOFT.JET.OLEDB.4.0'为'MICROSOFT.ACE.OLEDB.12.0'

这里要注意了，如果服务器数据库和office都是64位的，那么安装这个64位的插件就能解决问题了；

但如果不幸office是32位的，那同喜，要卸了office重新安装个64位的office再安装插件，因为32位的office是不能安装64位的插件。

 

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

另外，openrowset是不允许使用变量的，但是在实际运用中我们的文件路径习惯采用的方法就是使用变量，不然我们不能一次一次地来改字符串吧。

那么如果你需要使用变量的话，只能采用动态构造SQL语句的方法在openrowset里使用变量传参，下面仅作示例：

declare @filepath varchar(max),@SQL varchar(max)
set @filepath='D:\QualityReport4400000121.xls'
set @SQL=
    'select * into tmpTable
     from OPENROWSET(''MICROSOFT.ACE.OLEDB.12.0'',''Excel 5.0;HDR=NO;DATABASE='+@filepath+''',AMI$)'
exec(@SQL)    

唉，同样作为一入门菜鸟，提醒一下吧。被 '  ' 包括的 ' 需要加多一个 ' 才能表示为单引号

 

另附接口服务关闭方法，以便仅使用一次导入的朋友关闭

1 --注意这里，要先关闭外围的设置，然后再关闭高级选项
2 exec sp_configure'Ad Hoc Distributed Queries',0
3 reconfigure
4 exec sp_configure'show advanced options',0
5 reconfigure
6 --关闭ACE.OLEDB.12的选项
7 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
8 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0

 

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

以上纯属个人工作中所遇到的问题，如有失误之处望不吝赐教

 

转载于:https://www.cnblogs.com/VinJack/p/openrowset.html