How can I use SQL statements to query Excel Data?

How can I use SQL statements to query Excel Data?

Q: How can I use SQL statements to query Excel Data?
A: The following statements can be used to 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: d100] ' )

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] ' )

In addition, you also need to use a Logon account with the server SysAdmin role permissions. First, run the following statement to enable the SQL server's 'ad hoc distributed queries 'switch.

Exec Sp_configure ' Show advanced options ' , 1 ; Reconfigure ;
Exec Sp_configure ' Ad hoc distributed queries ' , 1 ; Reconfigure ;

After the query is executed, turn off the 'ad hoc distributed queries 'switch with the reverse order statement.

 

Exec Sp_configure ' Ad hoc distributed queries ' , 0 ;
Reconfigure ; Exec Sp_configure ' Show advanced options ' , 0 ; Reconfigure ;

# Website name: | new territory of Excel 2010/2007
# Signed by: Apollo
# Copyright notice: If the copyright is not reproduced by the author, the copyright must be licensed and indicated by the author and its source.
# This article comes from:
# Reference:
# Applicable versions: 2010 2007 versions earlier than 2003
# Language environment: Simplified Chinese (Simplified Chinese)
# Content Overview:
#ArticleAddress: http://www.exceltip.net/thread-5862-1-1-0.html
# Learning method: the Key to mastering Excel skills is hands-on operations | download knowledge =

 

Both 2007 and 2003 are available.

 

Select   *   From   OpenDataSource ( ' 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 $ ]