OPENROWSET from excel 2010 and 2007


I have the below versions in my stystem:

Database Version:  SQL Developer Edition (64-bit)
Windows Version: Windows 7 Enterprise (64-Bit)
Excle:  Micorsoft Excel 2010 or 2007(64-Bit)


Since all the versions are 64 Bit. So we need to install  Microsoft.ACE.OLEDB.12.0 from “AccessDatabaseEngine_x64”. instead of 'Microsoft.Jet.OLEDB.4.0'. We can download this from the below link


After downloading and running the setup. Run the below queries step by step.

sp_configure 'show advanced options',1
 GO
 RECONFIGURE
 GO
 sp_configure 'Ad Hoc Distributed Queries',1
 GO
 RECONFIGURE;
 -----Run the below query after running above.
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
SELECT * FROM
OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Users\rmamillapalli\Desktop\Ramesh\Shanthi.xls;HDR=YES;IMEX=1','SELECT * FROM [WrongThirdPartySupplier$]')
Above given path is my local path and Header columns in the excel should be in the first

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server