locked
Getting "external table is not in the expected format" error while opening an excel connection RRS feed

  • Question

  • User-62381292 posted

    Hi All,

    I am trying to read data from excel file. I am using excel 2010 and trying to import file in xlsx format. Below are the two connectionstrings I have tried.

    1)   pConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source="+excelFilePath+";Extended Properties=Excel 12.0;HDR=Yes;";

    2)   pConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Excel 12.0 Xml;HDR=YES";

    Using these connection strings when I say connection.Open() I get different errors.

    With connectionstring 1 I am getting an error "Could not find installable ISAM."

    With connectionstring 2 I am getting "external table is not in the expected format" error

    I am fed up with this please let me know how to fix this and open the connection successfully to read the data.

    Regards,
    Girish 

    Thursday, April 30, 2015 10:28 AM

All replies

  • User269602965 posted

     connXLS.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & strExcelFile & """;Extended Properties='Excel 12.0 Xml ;HDR=YES;'"

    this works for me in VB.NET

    that is focus on the extra "quoting" I use.

    Thursday, April 30, 2015 2:07 PM
  • User-62381292 posted

    Hi,

    I have converted the connectionstring given by you into C# 

    connXLS.ConnectionString == "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"" + strExcelFile + "\";Extended Properties='Excel 12.0 Xml ;HDR=YES;'"

    But with this again I am getting "Could not find installable ISAM" error.

    Not sure what mistake I am doing.

    Regards,
    Girish

    Sunday, May 3, 2015 11:22 PM
  • User269602965 posted

    Did you install the 32-bit Microsoft.ACE.OLEDB.12.0 driver, but are trying to compile app as 64-bit?

    or visa versa?

    If so, the app will fail

    Sunday, May 3, 2015 11:27 PM