SQL 2008 查询 EXCEL 2010

1，SQL  Server 2008 SP3 select @@version 得到以下信息：

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86) Sep 22 2011 00:28:06 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

2，操作系统是 windows 7 32位 sp1

3，OFFICE  2010 PRO plus vol 32位 

4，AccessDatabaseEngine.exe 2010 32位

5，AccessDatabaseEngine.exe 2007 32位

6，Access runtime 2010 32位 已安装

7，ODBC 中可以查看到 EXCEL Driver (*.xls,*xlsx.....)

8，SQL 高级设置及外围都已经开启

--开启高级设置

exec sp_configure'show advanced options',1reconfigure

--开启外围设置
exec sp_configure'Ad Hoc Distributed Queries',1reconfigure 


9，执行以下代码
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 GO


win 7 64 bit 安装2010后有问题，安装 AccessDatabaseEngine2010 64 位，还是有问题。 
安装AccessDatabaseEngine2007 32位 后正常


今天又在虚拟机（win 7 32 bit）实验了一下：

1，安装SQL Server 2008 32位

2，安装AccessDatabaseEngine2007 32位

后运行 

select * from  OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
 'Excel 12.0;DATABASE=c:\work\test.xlsx'
 , 'Select * from [Sheet1$]') 

提示 没有开启外围…………

3，运行

--开启高级设置

exec sp_configure'show advanced options',1reconfigure

--开启外围设置
exec sp_configure'Ad Hoc Distributed Queries',1reconfigure 

提供无法提取行，但是有列标题。

4，运行 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 GO

 

后正常。

 

 又一次在虚拟机中实验

 

1，安装SQL Server 2008 32

2，安装 Office 2010 pro plus 32（只安装了 excel word powerpint）

3,

--开启高级设置

exec sp_configure'show advanced options',1reconfigure

--开启外围设置
exec sp_configure'Ad Hoc Distributed Queries',1reconfigure 


 

select *
from  OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
 'Excel 12.0;DATABASE=c:\work\test.xlsx'
 , 'Select * from [Sheet1$]') 
 

正常

转：

 

Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server
To, Import/Export data To/From Excel (.xls) need Microsoft.Jet.OLEDB.4.0 and for Excel 2007 (.xlsx)

need 2007 Office System Driver: Data Connectivity Components. You can download from here.

Export Data to Excel file
Create an Excel file named testing having the headers same as that of SQLServerTable columns
Here is Query:
Excel 2003 (.Xls) file:

insertintoOPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:testing.xls;', 'SELECT * FROM [Sheet1$]') select*from SQLServerTable
　　Excel 2007 (.Xlsx) file:

insertintoOPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;', 'SELECT * FROM [Sheet1$]') select*from SQLServerTable
　　Import data from Excel to new SQL Server table
Excel 2003 (.Xls) file:

select*into SQLServerTable FROMOPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
　Excel 2007 (.Xlsx) file:

Select*into SQLServerTable FROMOPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
Import data from Excel to existing SQL Server table
Excel 2003 (.Xls) file:

Insertinto SQLServerTable Select*FROMOPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
　Excel 2007 (.Xlsx) file:

INSERTINTO SQLServerTable select*FROMOPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
　　Import data from Excel to new SQL Server table with dynamic table name (ex. SQLTABLE_200110413)


DECLARE@tablevarchar(500)
DECLARE@Qvarchar(2000 )
SET@table='SQLTABLE_'+(CONVERT(VARCHAR(8),GETDATE(),112))
SET@Q='select * into '+@table+' FROM OPENROWSET("Microsoft.ACE.OLEDB.12.0", "Excel 12.0;Database=D:testing.xlsx;HDR=YES", "SELECT * FROM [Sheet1$]")'
Exec(@Q)
　　May be you find error like below while Import/Export data To/From Excel
Error 1:

Msg 7399, Level16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Here is solution might work for you..

USE[master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Error 2:

Msg 15281, Level16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource'of component 'Ad Hoc Distributed Queries'because this component is turned offas part of the security configuration for this server. A system administrator can enable the useof'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.
here is solution might work for you

EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'Ad Hoc Distributed Queries', 1RECONFIGURE
If above Query showing following error…

Configuration option'show advanced options' changed from0to1. Run the RECONFIGURE statement to install.
Msg 5808, Level16, State 1, Line 2
Ad hoc updateto system catalogs isnot supported.
then user RECONFIGURE WITH OVERRIDE instead of RECONFIGURE..

EXEC sp_configure 'show advanced options', 1
RECONFIGUREWITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGUREWITH OVERRIDE