“Could not find installable ISAM” when opening MS-Excel using OleDb tip

[kareninstructor]

The most common reason for this exception is the connection string used to open a specific MS-Excel file is not correct. For example look at the connection strings in figure 1 and figure 2. At first glance they appear the same but take the results shown in figure 1a and figure 2a and examine them as shown in figure 3 and see that the second string (from figure 2) is missing two quotes which causes the Could not find installable ISAM exception as the string is not properly formatted.

One solution is to create a function or extension method (VS2008 or higher) that alleviates you from recreating an Excel connection string or grabbing a template you created and hopefully remember where you put this template. While we are at this why not set up the function or method so it is highly configurable from passing a file name to changing providers, specifying whether the first row contains data or headers plus should data be treated as strings rather than allowing the decision for data types is done for you. Figure 4 shows without using an extension method while figure 5 uses an extension method using one of the overloaded versions of the extension method. If you simply want to open the Excel file with no special conditions then figure 6 would be suffice.

All of the examples so far are for opening MS-Excel prior to Excel 2007; to open an Excel 2007 we use ExcelProvider.XLSX rather than ExcelProvider.XLS for the second parameter.

How you setup the connection string should be dependent on the data within your MS-Excel file.

Figure 1

Dim ExcelFileToRead As String = "test.xls"
Dim cnRange As String = _
String.Format("provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source='{0}';" & _
    "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;""", ExcelFileToRead)

Figure 1a

provider=Microsoft.Jet.OLEDB.4.0; data source='test.xls';Extended Properties="Excel 8.0; IMEX=1; HDR=No;"

Figure 2

Dim ExcelFileToRead As String = "test.xls"

Dim cnRange As String = _
String.Format("provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source='{0}';" & _
    "Extended Properties=Excel 8.0; IMEX=1; HDR=No;", ExcelFileToRead)

Figure 2a

provider=Microsoft.Jet.OLEDB.4.0; data source='test.xls';Extended Properties=Excel 8.0; IMEX=1; HDR=No;

Figure 3

provider=Microsoft.Jet.OLEDB.4.0; data source='test.xls';Extended Properties="Excel 8.0; IMEX=1; HDR=No;"
provider=Microsoft.Jet.OLEDB.4.0; data source='test.xls';Extended Properties=Excel 8.0; IMEX=1; HDR=No;

Figure 4

Dim Excel2003File As String = "test.xls"
Dim ConnectionString As String = _
String.Format("provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source='{0}';" & _
    "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;""", _
    Excel2003File)

Dim cn As New OleDbConnection(ConnectionString)

cn.Open()

Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", cn)
Dim dt As New DataTable
dt.Load(cmd.ExecuteReader)
DataGridView1.DataSource = dt

Figure 5

Dim Excel2003File As String = "test.xls"
Dim cn As New OleDb.OleDbConnection()
cn.ConnectionStringEx(Excel2003File, _
                      ExcelProvider.XLS, _
                      ImportExportMode.AsText, _
                      UseHeader.No)
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", cn)
Dim dt As New DataTable
dt.Load(cmd.ExecuteReader)
DataGridView1.DataSource = dt

Figure 6

Dim Excel2003File As String = "test.xls"
Dim cn As New System.Data.OleDb.OleDbConnection()
cn.ConnectionStringEx(Excel2003File, ExcelProvider.XLS)
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", cn)
Dim dt As New DataTable
dt.Load(cmd.ExecuteReader)
DataGridView2.DataSource = dt