Excel External table is not in the expected format

Question

I m trying to read an Excel  xlsx  file using the code shown below  I get an  External table is not in the expected format   error unless I have the file already open in Excel  In other words  I have to open the file in Excel first before I can read if from my C  program  The xlsx file is on a share on our network  How can I read the file without having to open it first  Thanks  string sql    SELECT   FROM  Sheet1     string excelConnection    Provider Microsoft Jet OLEDB 4 0 Data Source     pathname     Extended Properties   Excel 8 0 HDR YES IMEX 1       using  OleDbDataAdapter adaptor   new OleDbDataAdapter sql  excelConnection         DataSet ds   new DataSet        adaptor Fill ds

User · Answer

ACE has Superceded JET Ace Supports all Previous versions of Office This Code works well          OleDbConnection MyConnection          DataSet DtSet          OleDbDataAdapter MyCommand                   MyConnection   new System Data OleDb OleDbConnection   quot Provider Microsoft ACE OLEDB 12 0 Data Source     Book xlsx Extended Properties Excel 12 0  quot            MyCommand   new System Data OleDb OleDbDataAdapter  quot select   from  Sheet1   quot   MyConnection           DtSet   new System Data DataSet                     MyCommand Fill DtSet           dataGridView1 DataSource   DtSet Tables 0           MyConnection Close

User · Answer

I had this same issue Using the ACE OLEDB  and what resolved it for me was this link  http   support microsoft com kb 2459087 The gist of it is that installing multiple office versions and various office sdk s  assemblies  etc  had led to the ACEOleDB dll reference in the registry pointing to the OFFICE12 folder instead of OFFICE14 in  C  Program Files Common Files Microsoft Shared OFFICE14 ACEOLEDB DLL  From the link   Alternatively  you can modify the registry key changing the dll path to match that of your Access version  Access 2007 should use OFFICE12  Access 2010 - OFFICE14 and Access 2013 - OFFICE15  OS  64bit Office  64bit  or  OS  32bit Office  32bit  Key  HKCR CLSID 3BE786A0-0366-4F5C-9434-25CF162E475E  InprocServer32  Value Name   Default  Value Data  C  Program Files Common Files Microsoft Shared OFFICE14 ACEOLEDB DLL  OS  64bit Office  32bit  Key  HKCR Wow6432Node CLSID 3BE786A0-0366-4F5C-9434-25CF162E475E  InprocServer32  Value Name   Default  Value Data  C  Program Files  x86  Common Files Microsoft Shared OFFICE14 ACEOLEDB DLL

User · Answer

I had the same problem  which as resolved using these steps   1   Click File   2   Select  save as   3   Click on drop down  Save as type     4   Select Excel 97-2003 Workbook    5   Click on Save button

User · Answer

I was getting errors with third party and Oledb reading of a XLSX workbook  The issue appears to be a hidden worksheet that causes a error   Unhiding the worksheet enabled the workbook to import

User · Answer

I have too low reputation to comment  but this is comment on JoshCaba s entry  using the Ace-engine instead of Jet for Excel 2007   If you don t have Ace installed registered on your machine  you can get it at  https   www microsoft com en-US download details aspx id 13255  It applies for Excel 2010 as well

User · Answer

Just add my case  My xls file was created by a data export function from a website  the file extention is xls  it can be normally opened by MS Excel 2003  But both Microsoft Jet OLEDB 4 0 and Microsoft ACE OLEDB 12 0 got an  External table is not in the expected format  exception   Finally  the problem is  just as the exception said   it s not in the expected format   Though it s extention name is xls  but when I open it with a text editor  it is actually a well-formed html file  all data are in a  lt table gt   each  lt tr gt  is a row and each  lt td gt  is a cell  Then I think I can parse it in a html way

User · Answer

Instead of OleDb  you could use the Excel Interop and open the worksheet as read-only   https   msdn microsoft com en-us library microsoft office interop excel workbooks open v office 15  aspx

User · Answer

I recently had this  System Data OleDb OleDbException  0x80004005   External table is not in the expected format   error occur  I was relying on Microsoft Access 2010 Runtime  Prior to the update that was automatically installed on my server on December 12th 2018 my C  code ran fine using Microsoft ACE OLEDB 12 0 provider  After the update from December 12th 2018 was installed I started to get the    External table is not in the expected format  in my log file   I ditched the Microsoft Access 2010 Runtime and installed the Microsoft Access 2013 Runtime and my C  code started to work again with no  System Data OleDb OleDbException  0x80004005   External table is not in the expected format   errors   2013 version that fixed this error for me https   www microsoft com en-us download confirmation aspx id 39358  2010 version that worked for me prior to the update that was automatically installed on my server on December 12th  https   www microsoft com en-us download confirmation aspx id 10910 https   www microsoft com en-us download confirmation aspx id 10910  I also had this error occur last month in an automated process  The C  code ran fine when I ran it debugging  I found that the service account running the code also needed permissions to the C  Windows Temp folder

User · Answer

Just adding my solution to this issue  I was uploading a  xlsx file to the webserver  then reading from it and bulk inserting to SQL Server  Was getting this same error message  tried all the suggested answers but none worked  Eventually I saved the file as excel 97-2003   xls  which worked    only issue I have now is that the original file had 110 000  rows

User · Answer

This can occur when the workbook is password-protected  There are some workarounds to remove this protection but most of the examples you ll find online are outdated  Either way  the simple solution is to unprotect the workbook manually  otherwise use something like OpenXML to remove the protection programmatically

User · Answer

I had this problem and changing Extended Properties to HTML Import fixed it as per this post by Marcus Miris   strCon    Provider Microsoft ACE OLEDB 12 0 Data Source    amp  importedFilePathAndName             amp    Extended Properties   HTML Import HDR No IMEX 1

User · Answer

Thanks for this code    I really appreciate it  Works for me   public static string connStr    Provider Microsoft ACE OLEDB 12 0 Data Source     path     Extended Properties Excel 12 0      So if you have diff version of Excel file  get the file name  if its extension is  xlsx  use this   Private Const connstring As String    Provider Microsoft ACE OLEDB 12 0 Data Source     path     Extended Properties Excel 12 0      and if it is  xls  use   Private Const connstring As String    Provider Microsoft Jet OLEDB 4 0    amp   Data Source     path     Extended Properties   Excel 8 0 HDR YES

User · Answer

My scope consists of template download and verifies the template when it s filled with data So   1  Download a template   xlsx  file with the header row  the file is generated using openxml and it s working perfectly   2  Upload the same file without any change from it s downloaded state  This will cause a connection error and fails  OLEDB connection is using for reading the excel sheet     Here if the data is filled the program works as expected   Anybody having an idea the issue is connected with the file we are creating it s in xml format if we open it and just save convert it in to excel format and it works well   Any idea to download the excel with the preferred file type

User · Answer

the file might be locked by another process  you need to copy it then load it as it says in this post

User · Answer

If you still have this problem  then check your permissions  I tried many of these suggestions and my concrete problem was that the file that I wanted to process was under source control and the thread had no permissions  I had to change the entire folder permissions and it started to work  I was processing many files in there     It also matches many suggestions like change the name of the file or check that the file is not loicked by another process   I hope it helps you

User · Answer

Ran into the same issue and found this thread  None of the suggestions above helped except for  Smith s comment to the accepted answer on Apr 17  13   The background of my issue is close enough to  zhiyazw s - basically trying to set an exported Excel file  SSRS in my case  as the data source in the dtsx package  All I did  after some tinkering around  was renaming the worksheet  It doesn t have to be lowercase as  Smith has suggested   I suppose ACE OLEDB expects the Excel file to follow a certain XML structure but somehow Reporting Services is not aware of that

User · Answer

I have also seen this error when trying to use complex INDIRECT   formulas on the sheet that is being imported   I noticed this because this was the only difference between two workbooks where one was importing and the other wasn t   Both were 2007   XLSX files  and the 12 0 engine was installed   I confirmed this was the issue by    Making a copy of the file  still had the issue  so it wasn t some save-as difference  Selecting all cells in the sheet with the Indirect formulas Pasting as Values only   and the error disappeared

User · Answer

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  Using the following connection string seems to fix most problems   public static string path     C  src RedirectApplication RedirectApplication 301s xlsx   public static string connStr    Provider Microsoft ACE OLEDB 12 0 Data Source     path     Extended Properties Excel 12 0

User · Answer

I recently saw this error in a context that didn t match any of the previously listed answers   It turned out to be a conflict with AutoVer   Workaround  temporarily disable AutoVer

User · Answer

I know this is a very old post  but I can give my contribution too  on how I managed to resolve this issue  I also use  quot Microsoft ACE OLEDB 12 0 quot  as a Provider  When my code tried to read the XLSX file  it received the error  quot External table is not in the expected format  quot  However  when I kept the file open in Excel and then the code tried to read it     it worked  SOLUTION  I use Office 365 with company documents and in my case  the solution was very simple  I just needed to disable the sensitivity of the document  setting it to  quot public quot   Detail  Even after saving as  quot public quot  the green check still remained marked in  quot Internal Use quot   but the problem remained solved after that

User · Answer

This can also be a file that contains images or charts  see this  http   kb tableausoftware com articles knowledgebase resolving-error-external-table-is-not-in-expected-format  The recommendation is to save as Excel 2003

User · Answer

That excel file address may have an incorrect extension  You can change the extension from xls to xlsx or vice versa and try again

User · Answer

Working with some older code and came across this same generic exception   Very hard to track down the issue  so I thought I d add here in case it helps someone else     In my case  there was code elsewhere in the project that was opening a StreamReader on the Excel file before the OleDbConnection tried to Open the file  this was done in a base class    So basically I just needed to call Close   on the StreamReader object first  then I could open the OleDb Connection successfully  It had nothing to do with the Excel file itself  or with the OleDbConnection string  which is naturally where I was looking at first

User · Answer

If the file is read-only  just remove it and it should work again

[c#] Excel "External table is not in the expected format."

ACE has Superceded JET

Examples related to c#

Examples related to excel-2007

Examples related to xlsx

Examples related to import-from-excel