Import Data from Excel sheet in SQL Server

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

?

1 2 3 4 5

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

?

1 2 3 4 5 6

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,

?

1 2 3 4 5 6 7

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

?

1 2 3 4 5 6 7 8

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'