从SQL查询分析器中读取EXCEL中的内容

时间:2024-10-17 14:35:26

很早以前就用sql查询分析器来操作过EXCEL文件了。

由于对于excel公式并不是很了解,所以很多时候处理excel中的内容,常常是用sql语句来处理的。[什么样的人有什么样的办法吧 :)]

今又要用sql读取excel中的内容,所以把常用到得一些记录一下。

常用的方法有2种

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\a.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1"')...sheet1$ SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\a.xls;HDR=YES;IMEX=1', sheet1$ )

  

如果有密码还可以:

SELECT *  FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\a.xls";
User ID=;Password=;Extended properties="Excel 8.0;HDR=YES;IMEX=1"')...sheet1$

  

有的时候,我们会遇到如下错误:

SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。

我们可以这样处理:

启用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

  

在读取excel中某个单元格中的内容时,如里面放置的内容超过255个的时候,我们可能会得到null值。

(也可以说成,sql查询excel的字段内容超过255个的时候得到null值)

这时,我们需要把一列,设置为文本格式的字符,就可以了。

设置如下:

选择一列(或者一个单元格),菜单栏上的“数据”-->“分列”

在新打开的页面上选择“固定分隔符”,

点击下步-->“分割符号”的复选框全部取消。

点击下步-->“列数据格式”选择文本。

点击完成。

这时候,你会看到我们设置的excel 这一列中的内容都变成了"#"号,这个不用管,到sql查询分析器中,我们可以正常得到数据了。