Dundas Chart for SharePoint Send comments on this topic.
Excel Connection Strings

Glossary Item Box

Overview

This article provides you with examples of the connection strings used to connect to some common Excel Spreadsheet data providers. It is worthy to note that each connection is different, therefore it is usually up to the server administrator to create and deploy the correct connection string for each application, user account, and type.

Example

This example demonstrates the SQL syntax to use to access the data stored within an Excel worksheet, and more specifically, an Excel sheet called MySheet.

SELECT * FROM [MySheet$]

The simple query statement shown above uses a SELECT statement followed by the * character (indicating that a selection of all items is to take place), the worksheet name (in this case a sheet called MySheet) with a $ character appended to the end of it, and enclosed in square brackets. In short, the above SQL statement is saying "Select all items from my Excel sheet named MySheet".

ACE OLEDB 12.0 Provider

Copy Code Standard Security 
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0;HDR=YES";

 

ODBC Provider

Copy Code Standard Security 
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;

 

OLE DB Provider

Copy Code Standard Security 
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

 

Note
The HDR parameter defines whether or not the first row will contain column names instead of data.