+012 617 9233 opaps.com@gmail.com
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