A data source could be an Excel or similar spreadsheet where the data source is either an OleDB Jet connection for.xls files or and OleDb ACE for .xlsx files.
We use these files using either the ODBC data source or the OleDB data source.
To use you will have to have the appropriate software installed, that is MS Office or similar.
JET connection for .xls files on and before Ms Office 2007
Jet is better known as Microsoft Jet Database Engine and is installable from Microsoft.
For an OleDB connection we can use a connection string like this
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=“Excel 8.0;HDR=Yes;IMEX=1″;
For an ODBC connection we can use a connection string like this
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
This required that the jet drivers are available, which may require the MS Office be installed on the running machine. This will include any servers.
ACE connection for .xlsx files on and after Ms Office 2010
For an OleDB connection we can use a connection string like this
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=“Excel 12.0 Xml;HDR=YES;IMEX=1″;
For an ODBC connection we can use a connection string like this
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\MyExcel.xlsx;
This required that the jet drivers are available, which may require the appropriate MS Office be installed on the running machine. This will include any servers.