https://www.microsoft.com/en-us/download/details.aspx?id=54920
Before doing that, make sure you are a local admin in SQL Server and execute the following
Sometimes you may receive Microsoft OLEDB error,
To Get list of all sheets in a workbook, use the following
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$]' ) |
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 |
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 |
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' |
Comments
Post a Comment