I wrote this program because it seems that there is no official documentation on the format of such connection strings. Even after searching multiple times, I didn't find a satisfying page on the internet or microsoft.com.
The program contains a private string array, connectionStringTemplates, over which the program iterates. In each iteration, the substring XLSXPATH is replaced with the file path of an Excel Worksheet. with the resulting connection string (variable connectionString), the program tries to establish a connection to the worksheet.
If this is possible, it acknowledges it with OK, otherwise, it prints the caught exception.
using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
class Program {
private static string[] connectionStringTemplates = new string[] {
// ------------------------------------------------------------------------------------------
@"Data Source=XLSXPATH" , // ArgumentException: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH" , // OleDbException: Unrecognized database format '….xlsx'
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;" , // OleDbException: Unrecognized database format '….xlsx'
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;Extended Properties=Excel 12.0 Xml" , // OK
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;Extended Properties=Excel 12.0 Xml;HDR=Yes" , // OleDbException: Could not find installable ISAM.
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;Extended Properties='Excel 12.0 Xml;HDR=Yes'" , // OK
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;Extended Properties='Excel 12.0 Xml'" , // OK
@"Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;Extended Properties='Excel 12.0 Xml;HDR=Yes'" , // ArgumentException: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;Extended Properties='Excel 12.0 Xml;IMEX=Yes'", // OK
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;Extended Properties='Excel 12.0 Xml'" , // OK
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;Excel 12.0 Xml" , // ArgumentException: Format of the initialization string does not conform to specification
@"Provider=Microsoft.ACE.OLEDB.12.0;Excel 12.0 Xml;Data Source=XLSXPATH" , // OleDbException: Invalid argument
@"Provider=Microsoft.ACE.OLEDB.12.0;Excel 12.0 Xml;Data Source=XLSXPATH;HDR=Yes" , // OleDbException: Invalid argument
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLSXPATH;Extended Properties='Excel 12.0 Xml;foo=1'" , // OK
@"Provider=Microsoft.ACE.OLEDB.12.0;Excel 12.0 Xml;Data Source=XLSXPATH;foobarbaz=1" , // OK
@"Provider=Microsoft.ACE.OLEDB.12.0;Excel 12.0 Xml;Data Source=XLSXPATH;foobarbaz" , // ArgumentException: Format of the initialization string does not conform to specification
@"Provider=Microsoft.ACE.OLEDB.12.0;Excel 12.0 Xml;HDR=Yes;Data Source=XLSXPATH;foobarbaz=1" , // OK
@"Provider=Microsoft.ACE.OLEDB.12.0;Excel 12.0 Xml;HDR=Yes;Data Source=XLSXPATH" , // OK
@"complete-crap" , // ArgumentException: Format of the initialization string does not conform to specification ….
// ------------------------------------------------------------------------------------------
};
static void Main() {
string xlsxPath = Directory.GetCurrentDirectory() + @"\" + "excelFile.xlsx";
foreach (string connectionStringTemplate in connectionStringTemplates) {
string connectionString = connectionStringTemplate.Replace("XLSXPATH", xlsxPath);
Console.WriteLine(connectionString);
try {
using (OleDbConnection connection = new OleDbConnection(connectionString)) {
Console.WriteLine(" OK create connection");
OleDbCommand command = new OleDbCommand("select id, num, txt from [tab$]", connection);
Console.WriteLine(" OK create command ");
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read()) {
Console.WriteLine("\t{0}\t{1}\t{2}", reader[0], reader[1], reader[2]);
}
reader.Close();
Console.WriteLine(" OK test");
}
}
catch (System.ArgumentException argEx) {
Console.WriteLine($" NOK: ArgumentException: {argEx.Message}");
}
catch (System.Data.OleDb.OleDbException oleDbEx) {
Console.WriteLine($" NOK: OleDbException: {oleDbEx.Message}");
}
Console.WriteLine("");
}
}
}
Microsoft has the following comment about data types:
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to "guess" the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.