1 Replies - 1965 Views - Last Post: 15 May 2014 - 04:07 AM

#1 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Accessing Excel through SQL Server

Post icon  Posted 25 February 2014 - 06:56 AM

Hi
Most of the time people want to access Excel through Ms sql server, to work with that data present in the excel

There are some configurations you need to set on the server side
1) Enable the Adhoc Query exection by using the following query
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO


2) if you are using the access 97 or 2000 or 2003 then use the following query
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', N'Excel 8.0;DATABASE=D:\Mywork\Entity List.xls', 
'Select * from [Sheet1$]')
GO



3) if you are using the access 97 or 2007 then you need to register the excel 2007 driver (ie Microsoft.ACE.OLEDB.12.0 use ) the following query will done this
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Extended Properties="IMEX=1;HDR=YES;";DATABASE=D:\Mywork\Entity List.xlsx', 
'Select * from [Sheet1$]')



now some of the people say that this query will not work for me,
pro bably the reasons are
1) May be the path is wrong
Soluction: Verify the path is correct

2) Sql server don't have the rights to access the file
Solution: Give full access to the file

3) The Oledb driver you mentioned will not installed
Solution: download and install the required

4) The Os may be in Latest windows and 64 bit (what? Why?)
What? Yes, it happens for most of my colleague PC
Why? your SQL server installed as 64 bit edition and Oledb is registered in your sever is 32 bit
you need to do a little work around for that,
Solution: Download a 64 bit oledb driver form here, and then us the following query
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



Now you can access the excel by the follwoing queries

-- For 2007
SELECT * FROM   
OPENROWSET
(
	'Microsoft.ACE.OLEDB.12.0', 
	'EXCEL 12.0;Extended Properties="IMEX=1;HDR=YES;";DATABASE=D:\Customers\Gilead\2014\FEB\Role Profile1.xlsx', 
	'Select * from [Sheet1$]'
)
GO
SELECT * FROM   
OPENDATASOURCE
(
	'Microsoft.ACE.OLEDB.12.0', 
	'Data Source=D:\Mywork\Entity List.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1;"'
)...[Sheet1$];
GO
-- for 97 - 2003
SELECT * FROM   
OPENROWSET
(
	'Microsoft.ACE.OLEDB.12.0', 
	'EXCEL 12.0;Extended Properties="IMEX=1;HDR=YES;";DATABASE=D:\Mywork\Entity List.xls', 
	'Select * from [Sheet1$]'
)
GO
SELECT * FROM   
OPENDATASOURCE
(
	'Microsoft.ACE.OLEDB.12.0', 
	'Data Source=D:\Mywork\Entity List.xls;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1;"'
)...[Sheet1$];
GO


hope you got the answers

Is This A Good Question/Topic? 1
  • +

Replies To: Accessing Excel through SQL Server

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Accessing Excel through SQL Server

Posted 15 May 2014 - 04:07 AM

the following one is for CSV, you may Argue Bulk copy will more handy, This is just for a knowledge sharing
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\From\RamITM\;Extended Properties="text;HDR=Yes;FMT=Delimited"','SELECT * FROM vgusers.csv');

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source = D:\From\RamITM;Extended Properties = "Text;HDR=YES;"')...vgusers#csv


This post has been edited by thava: 15 May 2014 - 04:08 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1