Saturday, April 15, 2017

SQL Server OPENROWSET Excel using Microsoft.ACE.OLEDB

This on SQL Server 2016:

Download file AccessDatabaseEngine_X64.exe from Microsoft Access Database Engine 2010 Redistributable

Open an Administrator Command Window and install with the following command:

C:\Users\kaushalsinha\Downloads\AccessDatabaseEngine_X64.exe /passive


/passive switch is the key.

Once installed, the query in SQL Server Management Studio:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1;
GO

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;Database=F:/2011-12 CRDC/SCH/data for Schools/Pt 1-Enrollment/Overall Enrollment.xlsx','SELECT * FROM [Suppressed Data$]');

... and the result:



 

No comments: