How do I query Excel data with SQL statements?

How do I query Excel data with SQL statements?

Q: How do I query Excel data with SQL statements?
A: The following statements can query data in an Excel worksheet in SQL Server.

Versions 2007 and 2010:

SELECT *
From OpenDataSource (' microsoft.ace.oledb.12.0 ',
' Data source= ' c:\book1.xlsx '; User id=admin; password=; Extended properties=excel 12.0 ') ... [sheet1$]

SELECT * from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' Excel 12.0;database=c:\book1.xlsx ', ' SELECT * from [sheet1$a1:d 100] ')

SELECT * from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' Excel 12.0;database=c:\book1.xlsx ', ' SELECT * from [sheet1$] ')

Version 2003:

SELECT *
From OpenDataSource (' microsoft.jet.oledb.4.0 ',
' Data source= ' C:\Book1.xls '; User id=admin; password=; Extended properties=excel 8.0 ') ... [sheet1$]

SELECT * from OPENROWSET (' microsoft.jet.oledb.4.0 ', ' Excel 8.0;database=c:\book1.xls ', ' SELECT * from [sheet1$] ')

SELECT * from OPENROWSET (' microsoft.jet.oledb.4.0 ', ' Excel 8.0;database=c:\book1.xls ', ' select * from [sheet1$a1:d100] ' )

Also, you need to use the login account with the server sysadmin role permission to open the SQL Server ' Ad Hoc distributed Queries ' switch by first executing the following statement.

exec sp_configure ' show advanced options ', 1;reconfigure;
exec sp_configure ' Ad Hoc distributed Queries ', 1;reconfigure;

After executing the query, turn off the ' Ad Hoc distributed Queries ' switch with the reverse order of the statements.

exec sp_configure ' Ad Hoc distributed Queries ', 0;
Reconfigure;exec sp_configure ' show advanced options ', 0;reconfigure;

# Site Name: Excel Tips Web | Excel 2010/2007 New Territories
# Attribution Author: Apolloh
# Copyright Disclaimer: All rights reserved by the author and the author and source
# This article comes from:
# Citation works:
# Applicable version: 2010 2007 2003 Previous version
# Locale: Simplified Chinese (the top right corner of the page is simple and easy to transform)
# Content Introduction:
# Article Address: http://www.exceltip.net/thread-5862-1-1-0.html
# How to learn: The key to mastering Excel skills is hands-on | download ≠ knowledge

Both 2007 and 2003 are available

How do I query Excel data with SQL statements?