Import data from excel sheet to MSSQL

Show configuation settings.


If you don't see 'Ad Hoc Distributed Queries' option in table.

Allow show advance configuation settings.

sp_configure 'show advanced options', 1

Allow OpenRowset/OpenDatasource queries.

sp_configure 'Ad Hoc Distributed Queries', 1 

Select data from excel file.

SELECT * FROM EXCELLINK...[Sheet$] -- EXCELLINK have to be linked server
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=c:\Excel.xls;Extended Properties=Excel 8.0')...[Sheet$]
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\Excel.xls', [Sheet$])

If 'Ad Hoc Distributed Queries' is set to 0 you get

"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."

in this case use code at page top.

programming/mssql/importfromexcel.txt · Last modified: 2018-06-21 19:48 (external edit)
CC Attribution-Noncommercial-Share Alike 4.0 International
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0