How can I use SQL statements to query Excel Data?

Source: Internet
Author: User
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 $ ]

 

 

 

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.