OLE DB Connection String to an Excel File(.xls or .xlsx)

To Extract the extension:

string _connectionString= string.Empty;

// pathToFile is fully qualified file path

string Extension = Path.GetExtension(pathToFile);

// Pass the extension to Switch Module
switch (Extension)
{
case ".xls": //Excel 97-03
_connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + pathToFile + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""";

// If there is no header set HDR to NO

break;

case ".xlsx": //Excel 07
_connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + pathToFile + @";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""";

break;

}