locked
oledbconnection connection error External table is not in the expected format. RRS feed

  • Question

  • User1893303432 posted

    I am trying to read xlsx file with oledbconnection, getting Error " External table is not in the expected format. " when attempting to open connection.

    Code i am using : Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\TempFileUpload\file.xlsx;Extended Properties="Excel 12.0;HDR=No;IMEX=1;"  

    But i have tested it with some other file having xlsx and it's working fine but there is issue with other file that's also have same extension xlsx.

    I would really appritiate it any one can help me to get it resolve.

    Thanks

    Azad

    Wednesday, June 11, 2014 5:14 AM

All replies

  • User-778131909 posted

    Hi Azad,

    External table is not in the expected format." typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

    The connection string in below format seems to fix most problems.

    public static string path = @"C:\src\RedirectApplication\RedirectApplication\RedirectApplication\301s.xlsx";
    public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

    Wednesday, June 11, 2014 8:07 AM
  • User1893303432 posted

    Thanks 

    VickySan

    i am using same code as you gave  me, over all it's working fine, the problem there were some issue in file, when i save file in new location 

    as a save as then it's working fine,

    Thanks once again.

    And my new problem : My dataset have data with date and it's have m/d/yyyy format but i need MM/dd/yyyy format ,

    and my query is : query = "SELECT * from [" + sheetName + "$B3:IM8] where [F1]='Date Value' or [F1]='Data Value'";

    could you suggest me to get date in MM/dd/yyyy format.

    Thursday, June 12, 2014 3:13 AM