SQL Server Management Studio中访问EXCEL 2007（XLSX）文件的方法

因为要批量导入EXCEL中的数据，而系统装的是OFFICE 2010，遇到好多问题，这个问题折腾了几天，今天终于调通了，记录下相关信息，供以后参考。

 

一、调试环境：

1、Win7、Office 2010、SQL 2008 R2 EXPRESS。

2、测试软件：SQL Server Management Studio 2008

二、测试过程

1、开启OPENDATASOURCE、OPENROWSET功能

方法一：

SQL Server Management Studio 2008中运行：

--开启服务器端相关配置(SQL Server 2000默认已开启，SQL Server 2005和SQL Server 2008中未开启)
-- wonsoft.cn
--
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

 

方法二：

SQL Server Management Studio 2008（2005配置方法请百度）中左视图树的根（即“服务器名称”）右键 -->  选择“方面” -->  选择：方面 =  外围应用配置器  --> 设置：AdHocRemoteQueriesEnabled = TRUE

 

2、设置服务的启用模式：

    WINDOWS管理工具/服务中将SQL Server（MSSQLSERVER或EXPRESS）和SQL Full-Filter Deamon Launcher（MSSQLSERVER）两个服务的登录身份改为LocalSystem、允许系统与桌面相互访问。

 

3、SQL语句示例：

示例一：

-- wonsoft.cn
--
SELECT   *
FROM      OPENDATASOURCE(
         'Microsoft.Ace.OleDb.12.0',
         'Extended Properties="Excel 12.0;HDR=YES;IMEX=1";Data Source="D:\abc4.xlsx"'
         )...[sheet1$]

示例二：

-- wonsoft.cn
--
SELECT   * 
FROM      OPENROWSET(
         'Microsoft.Ace.OleDb.12.0',
         'Excel 12.0;HDR=YES;IMEX=1;Database=D:\abc4.xlsx'
         ,[sheet1$])

或：

-- wonsoft.cn
--
SELECT   *
FROM      OPENROWSET(
         'Microsoft.Ace.OleDb.12.0',
         'Excel 12.0;HDR=YES;IMEX=1;Database=D:\abc4.xlsx'
         ,'select * from [sheet1$]')

 示例三：

-- wonsoft.cn
--
SELECT * FROM OpenDataSource(
      'Microsoft.Jet.OLEDB.4.0', 
      'Data Source="d:\test2.xls";Extended properties=Excel 5.0'
      )...Sheet1$

 

三、常见故障定位：（示例一为例）1、链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Ace.OleDb.12.0" 返回了消息 "找不到可安装的 ISAM。"。

     例一：

-- wonsoft.cn
--
SELECT   *
FROM      OPENDATASOURCE(
         'Microsoft.Ace.OleDb.12.0',
         'Extended Properties="Excel 12.0;HDR=YES;IMEX=1";DataSource="D:\abc4.xlsx"'
         )...[sheet1$]

仔细检查，会发现“DataSource”有错，应当是“Data Source”

     例二：

-- wonsoft.cn
-- 
SELECT   *
FROM      OPENDATASOURCE(
         'Microsoft.Ace.OleDb.12.0',
         'ExtendedProperties="Excel 12.0;HDR=YES;IMEX=1";Data Source="D:\abc4.xlsx"'
         )...[sheet1$]

仔细检查，会发现“ExtendedProperties”有错，应当是“Extended Properties”

    注：以上关键字输入错误，也会导致该出错现象。

    例三：很容易出错

-- wonsoft.cn
-- 
SELECT   *
FROM      OPENDATASOURCE(
         'Microsoft.Ace.OleDb.12.0',
         'Extended Properties=Excel 12.0;HDR=YES;IMEX=1;Data Source="D:\abc4.xlsx"'
         )...[sheet1$]

注意扩展属性：Extended Properties=”Excel 12.0;HDR=YES;IMEX=1"，如果把双引号去掉，也会导致该错误。