How to create a valid OLEDB select statement in SharePoint for Excel as external data source?
While integrating external data
sources into SharePoint via OLEDB the valid syntax of the select statements
depends on the types of data sources used. Below are given some samples
for Microsoft Excel data sources that are often used with the
Layer2 Business Data List Connector for
Please take a look at the following examples:
1. "SELECT * FROM [Sheet1$A:E]"
2. "SELECT * FROM [Sales$A:E] WHERE Name='Bob'"
Name being the column heading which will get treated as a recordset
How to connect to Ecxel via OleDb in detail:
MS Excel Use Case
How to connect to MS Access via OleDb
MS Access Use Case