Import Data from Excel sheet in SQL Server

https://www.microsoft.com/en-us/download/details.aspx?id=54920

  

SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=NO;Database=D:\temp\Test.xlsx',
    'select * from [sheet1$]')

Before doing that, make sure you are a local admin in SQL Server and execute the following

  

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

Sometimes you may receive Microsoft OLEDB error,

  
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   
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DisallowAdHocAccess', 0
GO

To Get list of all sheets in a workbook, use the following

  

declare @FileName varchar(max) = 'test-file'
declare @FilePath varchar(max) = 'D:\temp\' + @FileName + '.xlsx'
declare @ConnectionString varchar(max) = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+@FilePath+';Extended Properties=''Excel 12.0;HDR=Yes'''
declare @r int, @objConnection int, @objRecordSet int
          exec @r = sp_OACreate 'ADODB.Connection', @objConnection output
if @r = 0 exec @r = sp_OAMethod @objConnection, 'Open', null, @connectionstring
if @r = 0 exec @r = sp_OAMethod @objConnection, 'OpenSchema', @objRecordSet output, 20
if @r = 0 exec @r = sp_OAGetProperty @objRecordSet, 'GetRows'