none
Microsoft.ACE.OLEDB.12.0 'External table is not in the expected format' error on Windows 2008 server R2 RRS feed

  • Question

  • Hello, 

    While reading an excel file ( .xlsx , office 2010) using the OLEDB 12.0 I am getting the following error "External table is not in the expected format", when I removed the "IMEX=1" from the connection string the error diapered and the file was read successfully.

    How can we read the file with the "IMEX=1" in the connection string as the excel file has a mixed data column. 

    Wednesday, February 15, 2017 10:25 AM

All replies

  • It would help to post your connection string. It should look something like the following:

            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=C:\Documents and Settings\...\My Documents\My Database\Excel\Book2007.xlsx;" & _
                                "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""



    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 15, 2017 1:51 PM
  • The connection string is as below:

    cnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xlsx;Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1'" 

    Wednesday, February 15, 2017 2:06 PM
  • The connection string is as below:

    cnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xlsx;Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1'" 


    Your connection string works fine for me. Do any of the Worksheets in the Workbook have any Cells where the values are calculated from an expression?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 15, 2017 6:04 PM
  • Hi varouqa_adiga,

    As paul said, the connection string is ok, Could you please share related code and excel file, which I could reproduce the issue on my side.

    In addition, Here is a similar issue, which provide a solution by installing MSXML 6.0 package.

    https://social.msdn.microsoft.com/Forums/en-US/57cb3121-755d-4cd5-8cda-a0630cbf535b/microsoftaceoledb120-external-table-is-not-in-the-expected-format-error-on-windows-2003-server?forum=adodotnetdataproviders

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 16, 2017 6:39 AM
    Moderator
  • I already install MSXML 6.0 package but this did not fix the problem.
    Recreating the project from scratch with the same code works for me without error, strange! 

    Thank you for your time and support.

    <o:p></o:p>

    Thursday, February 16, 2017 9:27 AM
  • Hi varouqa_adiga,

    I am glad to know that you solve the issue and share the solution to us, it will be beneficial to other communities who have the similar issue.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 22, 2017 2:32 AM
    Moderator
  • I was having the same issue when I finally realized that the XLSX I work trying to work with had data that did not start in cell A1. Once I fixed this, the ADO connection works perfectly! Hopefully this will save someone else some headache!
    • Proposed as answer by osukef Wednesday, October 17, 2018 5:45 PM
    Wednesday, October 17, 2018 5:45 PM