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*fromopendatasource (' microsoft.ace.oledb.12.0 ', ' Data source= ' c:\book1.xlsx '; User id=admin; password=; Extended properties=excel 12.0 ') ... [sheet1$]Copy Code

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

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

Version 2003:

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

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

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

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;Copy Code

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;Copy Code

# Site Name: Excel Tips Web | Excel 2010/2007 New Territories # Attribution Author: Apolloh # copyright notice: Copyright non-author himself reproduced subject to permission and note the author and source # This article from: # Citation: # Applicable version: 2010 2007 2003 Previous version # locale: Simplified Chinese Text (in the top right corner of the page) # Content Introduction: # Article Address: http://www.exceltip.net/thread-5862-1-1-0.html # Learning Method: Mastering Excel Skills is the key to hands-on operation | download ≠ knowledge

Both 2007 and 2003 are available

Select*fromopendatasource (' microsoft.ace.oledb.12.0 ', ' Data source= ' D:\test1.xls '; User id=admin; password=; Extended properties= "Excel 12.0 Xml; Hdr=yes;imex=1 "; ') ... [sheet1$]Copy Code

How do I query Excel data with SQL statements?