Example SQL statement
SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.16.0’,
‘Excel 12.0; Database=e:\excelimport\leadmaster.xlsx; HDR=YES; IMEX=1’,
‘SELECT * FROM [LeadMaster$]’);
explanation:
e:\excelimport\leadmaster.xlsx is the excel file path
[LeadMaster$] is the sheet name
if you try to run above SQL script , it show some errors, (after modifying the path and also sheet name).
Such as access denied or cannot find the server. you should try below steps
Go into your MSSQL management server studio,
go to connected-> Service objects-> Linked Servces->Provideres
you should see Microsoft.ACE.OLEDB.xxxx (12.0,15.0 or 16.0) depend which database engine you have installed.
Usually this will not be seen on MS Windows Server if you don’t have MS Excel installed.
https://www.microsoft.com/en-us/download/details.aspx?id=54920
go to above link and download the Microsoft Access Database engine Redistributable
then install it. Then you should see the driver
next go to SQL Script and run below
USE [MSDB]
GO
sp_configure ‘show advanced options’, 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE WITH OverRide
GO
it will show
Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.
now when you run the first SQL script you should be able to list out all the data.
UPDATE ON 2021 MAY 14:
if you get below message:
The OLE DB provider “Microsoft.ACE.OLEDB.16.0” for linked server “(null)” reported an error. Access denied
Try to run the MSSQL server using localAccount, rather that NTservices account
