Read Excel using SQL

Excel reading is possible only if system is installed with AccessDatabaseEngine(32 or 64bit), and below configuration is done in SQL;

Step 1 : Re-Configure the SQL settings for reading Excel.

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

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
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


Step 2 : Reading Excel

Excel must be inside any of the Drive folder with full permission of system having SQL Installed.

Execute the below query; Were excel is in D:\ExcelUpload folder

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\ExcelUpload\YourExccelName.xlsx', 'select * from [Sheet1$]')