none
Could not find installable ISAM. RRS feed

  • Question

  • ERROR: Could not find installable ISAM.  When trying to perform an Open.

     

    I have a large number of Office 2007 Excel Workbooks that I need to extract a sheet of data (it has a header row) and move that contents to a SQL Server 2005 database.  After doing some investigating it seems that SQLBulkCopy would be an easy way to perform that task.

     

    But I've never gotten that far in my code to determine if it works.  My connection.open() method errors with the above message.  I'm guessing that there is some compatibility problem using the OleDbManagedDataProvider in the .NET framework.

     

    I was getting the same error using the Jet.OLEDB.4.0 Provider and moved to the ACE.OLEDB.12.0 Provider as I assumed that the Jet OLEDB Provider could not handle my .xlsx files correctly.

     

    Just to see if it was a bad load problem, reloaded the drivers.  Additionally, I moved to another machine and reran the application with similar results.

     

    I have checked my connection string and I believe that it is correct.

     

    I loaded the machines with the the 2007 Office System Driver: Data Connectivity Components that is supposed to load the ACE 12.0 drivers.

     

    The Jet OleDb 4.0 provider and the ACE OleDb 12.0 providers are in the registry (but I don't know how to validate that they are correct).  I have been able to write a small C# Console Application that replicates the error in just a few lines of code.

     

    Here is the output.

     

    Welcome to the Perf Database Excel to SQL Converter
    Enter ExcelWorkbook Path (or NULL to Exit): c:\TestWorkbook.xlsx
    c:\TestWorkbook.xlsx
    Connection Open Failure
    ExcelConnectionString='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\TestWorkbook.xlsx;ExtendedProperties="Excel 12.0;HDR=YES;"'
    Index #0
    Message: Could not find installable ISAM.
    Native: -69141536
    Source: Microsoft Office Access Database Engine
    SQL: 3170
    Enter ExcelWorkbook Path (or NULL to Exit):

    Pertinent program segment enclosed.

     

            static void MoveExcelData(string pathname)
            {
                // Connection String to Excel Workbook
                string excelConnectionString =

                    "Provider=Microsoft.ACE.OLEDB.12.0;DataSource="
                    + pathname.ToString()
                    + ";ExtendedProperties=\"Excel 12.0;HDR=YES;\"";
                // Create Connection to Excel Workbook
                using (OleDbConnection connection =

                              new OleDbConnection(excelConnectionString))
                {
                    OleDbCommand command =

                              new OleDbCommand("SELECT * FROM [PerfData$]", connection);
                    try
                    {
                        connection.Open();
                    }
                    catch (OleDbException exception)
                    {
                        Console.WriteLine("Connection Open Failure");
                        Console.WriteLine("ExcelConnectionString='"

                                            + excelConnectionString.ToString() + "'");
                        for (int i=0; i < exception.Errors.Count; i++)
                        {
                            Console.WriteLine("Index #" + i + "\n" +
                                "Message: " + exception.ErrorsIdea.Message + "\n" +
                                "Native: " +

                                      exception.ErrorsIdea.NativeError.ToString() + "\n" +
                                "Source: " + exception.ErrorsIdea.Source + "\n" +
                                "SQL: " + exception.ErrorsIdea.SQLState);
                        }
                        return;
                    }
                    // Create DbDataReader to Data Worksheet
                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        // SQL Server Connection String
                        string sqlConnectionString =

                       "Data Source=.;Initial Catalog=TestPerfDB;Integrated Security=True";
                        // Bulk Copy to SQL Server
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                        {
                            bulkCopy.DestinationTableName = "TestPerfTable";
                            bulkCopy.WriteToServer(dr);
                        }
                    }
                }
                return;
            }


    Can anyone shed light on this problem? 

     

    Thank you.

    Joseph Aquilino

     

    Friday, April 18, 2008 11:15 PM

Answers

  • OK, I still see a syntax problem. The ExtendedProperties argument should be two words as well:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\TestWorkbook.xlsx;Extended Properties="Excel 12.0;HDR=YES;"

    Tuesday, April 22, 2008 12:31 PM
  • Sorry,

     

    When I made the post I did some editing and acceidently took out the space.  The actual code segment is.

     

                // Connection String to Excel Workbook
                string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                    + pathname.ToString()
                    + ";ExtendedProperties=\"Excel 12.0;HDR=YES;\"";

    Sorry for the confusion.  My problem still exists as reported.  Please help!!

     

     

     

     

     

    Monday, April 21, 2008 4:47 PM

All replies

  • One of the causes of this error is a syntactically incorrect connection string. The first thing I noticed is that "DataSource" is one word. It should be two. Give that a try to see if it fixes the problem.

    Saturday, April 19, 2008 3:37 AM
  • Sorry,

     

    When I made the post I did some editing and acceidently took out the space.  The actual code segment is.

     

                // Connection String to Excel Workbook
                string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                    + pathname.ToString()
                    + ";ExtendedProperties=\"Excel 12.0;HDR=YES;\"";

    Sorry for the confusion.  My problem still exists as reported.  Please help!!

     

     

     

     

     

    Monday, April 21, 2008 4:47 PM
  • Can you post the value of excelConnectionString after the above assignment?
    Monday, April 21, 2008 6:14 PM
  • Here is the connection string at the time of the error.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\TestWorkbook.xlsx;ExtendedProperties="Excel 12.0;HDR=YES;"

    Thanks.

    Joseph Aquilino

    Monday, April 21, 2008 7:27 PM
  • OK, I still see a syntax problem. The ExtendedProperties argument should be two words as well:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\TestWorkbook.xlsx;Extended Properties="Excel 12.0;HDR=YES;"

    Tuesday, April 22, 2008 12:31 PM
  • That was it!

     

    Thank you.

     

    Joseph Aquilino

     

    Tuesday, April 22, 2008 4:49 PM
  • Thanks Paul for this clue/idea this also solves my problem...

    Thursday, October 23, 2008 8:15 AM
  • Hi,

     

     please refer this blog..

    http://jomgrg.blogspot.com/2011/06/excel-2007-and-other-versions-uploading.html

     

    Thank you.

    Tuesday, June 21, 2011 9:44 AM
  • I was getting "Could not find installable ISAM" and it was driving me nuts!!... when i used this connection string 

    string conStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0;HDR=YES", defaultLeadsFilePath);

    and then i changed it to 

    string conStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", defaultLeadsFilePath);

    this connection string worked perfectly, no more error.. YaY!!




    • Edited by MelroyBritto Wednesday, October 17, 2012 8:11 PM deleting extra info
    • Proposed as answer by MelroyBritto Wednesday, October 17, 2012 8:40 PM
    Wednesday, October 17, 2012 8:05 PM
  • I was struggling with getting the connection string right too :-) - last error message was the "ISAM" thing -  ISAM errors seem to ba associated with the Excel part of the string?

    Thanks melroyBritto for posting - saved me needed time.


    bob mick

    Tuesday, August 13, 2013 12:45 PM
  • Hey there!

    What is defaultLeadsFilePath? Is it the path to the xlsx? At the moment I´m trying with this without success...


     OleDbConnection conn = new OleDbConnection();

    conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Arroyo\Desktop\Projects\ABB\ABB2014\Labview\ConnectivityMatrix.xlsx"+@";Extended Properties=""Excel 14.0; HDR=Yes;""";

    I tried also without success....


     OleDbConnection conn = new OleDbConnection();

    string conStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", C:\Users\Ar\Desktop\Projects\Matrix.xlsx);

    conn.ConnectionString=conStr;

    Any idea? 

    Thanks


    Tuesday, June 10, 2014 2:17 PM