Excel and SQL Server Integration using OLEDB

Source



Configure the Microsoft.ACE.OLEDB.12.0 to insert or update data in an Excel spreadsheet from SQL Server by using the OPENROWSET command in Transact-SQL to provide an elegant and easy to use interface. There are a couple of easy steps to perform to be able to use this OLEDb interface technology which are provided below.

First you have to set a couple Registry parameters which can be managed with these two commands:




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


You also have to enable the Distributed Queries in the Surface Area Configuration using this set of commands


SP_CONFIGURE 'show advanced options', 1
RECONFIGURE
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
RECONFIGURE


Of course you also have to tell the data provider that you are dealing with an Excel file and not an Access database which is the default using the Extended Properties clause:

Select ProductCategory, ProductName,
sum(case when TransactionType = ‘Sales’ then Amount else 0 end) as Sales,
sum(case when TransactionType = ‘Budget’ then Amount else 0 end) as Budget
from OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,
‘Excel 12.0;Database=C:\GizmoSales.xlsx;Extended Properties=”Excel 12.0 Xml;HDR=YES”;’, ‘Select * from [SalesData$]‘)
group by  ProductCategory, ProductName

When referencing the Excel sheet T-SQL you have to use this syntax if not you will get an error stating that the sheet doesn't exist:

If In Excel the sheet name is : Temp (as an example)

then in T-SQL you need to reference it as [Temp$]

Other than that you can use openquery, openrowset or opendatasource as you would in previous versions of Excel and SQL Server.


More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working