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);
}

Sennit

FWIW I received this on an excel sheet, when attempting to open it, I was using the current ACE and the suggested extended properties. When I manually opened the file, it had that prompt at the top to enable editing, I need to sort out how to flip that bit automagically, but if you're getting this you may just need to open the file, and then enable editing. I may look to see if i can open the file read-only, i saw something very far down in this thread about that. – Crinite

Answer 1

"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;";

Vagary

Ironically, I received this error from someone else's application (Scribe), but the explanation still solved the problem for me: "Save As" Excel 97-2003, and error fixed. – Indefinite

you might have to install this first: microsoft.com/en-us/download/confirmation.aspx?id=23734 – Goodnatured

this may be unbelievable, but i simply change the sheet name to all lowercase and use sheet1$ – Incalescent

I'm using LinqToExcel, and to get LinqToExcel to use that query string, I have to rename the file to xlsx. The spreadsheet in question really is an excel 97 spreadsheet, but the odbc provider doesn't seem to care about that. Once I trick LinqToExcel into using the correct query string, the Provider apparently determines how to read the file independent of the file's extension. Convenient loophole in my case. – Thorin

@Incalescent this is not unbelievable because any time you open a file and make any change the format will be corrected. It is only helpful if you have control over the files being used. If your application is receiving files from random users then you are still stuck. – Nial

the code is perfectly working on 2003 excel connection string. After making Sheet name all in lower case in 2007, that also worked .I'm getting the sheet name dynamically though it was not working before. – Sessoms

I am still getting this error for XSLX files. I have Office 365 ProPlus installed on Windows Server 2012 R2. I installed all possible Access drivers for 2007, 2010 and 2013. I could not install 2016 drivers because of following error - support.office.com/en-us/article/… – Retroaction

@JobinJoseph but why- if you're making a product for a company - how can you comply them to always have a filename in small letters- this is BS. – Deposal

@GeorgeMcDowd Thanks for the solution, but in my case I do not have ACE driver installed, nor do my clients. Is there a way to read the file using JET? (x64bits processor and Office 2016 installed) – Waldenses

@Waldenses , I have the same question , I do not have ACE driver installed neither the other computers that should use this, is there a way to read the file using JET for Office 2016 or 365 . Did you solve this issue? Could you please help me? Thank you in advance – Tolkan

@Vagary , I have the same question with *Naddem_MK, could you also help me with this problem? – Tolkan

Sorry guys, it's been 10 years since I've done this. I wouldn't know how to troubleshoot any further. – Vagary

Answer 2

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;" & "Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=YES;"""

Ellary

FYI: This will throw an OleDbException if you try to open an .xls file on a PC that does not have the Jet OleDb installed on it. – Richella

@Ellary are you sure your last line of code is correct? Can you recheck it in some editor again? – Deposal

Answer 3

(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.

Knowle

I have the ACE engine installed, but I need to know what reference I I need to include in my project so that my installer includes it. Not all machines that my app is installed on will necessarily have MS Office installed. – Richella

The link now gives an error message - We're sorry, this download is no longer available. – Doff

Link works again – Shizukoshizuoka

Answer 4

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 <table>, each <tr> is a row and each <td> is a cell. Then I think I can parse it in a html way.

Themis

This was my case too, however my file was actually a XML. Still it would be nice to know how to import it using OBDC, but I don't think its supported. – Middlebuster

@DavidRogers, I ever saw something like XML ODBC Driver, but never used, you have a look at cdata.com/drivers/xml/odbc. – Themis

Same case here, I guess the magic started at opening the file with notepad, in fact I'm up-voting your answer because I didn't scroll down to see your post until now (and now I already opened the file/parsed it with Html Agility pack...) but your answer deserves to be at the top, by pure logic : OPEN THE FILE FIRST! and see if it has some Excel-ish file-style inside! – Ulphi

If it is an html file, simply apply the extended properties like so: Extended Properties=""HTML Import;HDR=No;IMEX=1 – Tallyho

Answer 5

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

Chariot

Boo! Reverting to an outdated file format should not even be a consideration. At the time of this answer, the 97-2003 format was 16 years old and 12 years out of date. I could understand a few years, but more than a decade out of date should not suggest to a professional developer that the file format needs to be older. – Denys

Answer 6

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

Amparoampelopsis

I found it easier to just go to Programs and Features and Repair ACE. (For me, ACE is named Microsoft Access Runtime 2016). I assume that I was having this variant of the problem and that Repair just reset all the registry keys for me without me having to bother with regedit ;-). – Echino

Answer 7

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.

Mclean

Answer 8

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.

Pochard

Answer 9

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

Lammastide

Answer 10

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 "Microsoft.ACE.OLEDB.12.0" as a Provider. When my code tried to read the XLSX file, it received the error "External table is not in the expected format." 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 "public". Detail: Even after saving as "public" the green check still remained marked in "Internal Use", but the problem remained solved after that.

Verbiage

Answer 11

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=" & importedFilePathAndName _
         & ";Extended Properties=""HTML Import;HDR=No;IMEX=1"";"

Lombardi

You have no idea how long I've been fighting this, turns out the XLSX files exported from our applications are just renamed HTML files. Thank you!!! – Klink

Answer 12

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(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\Book.xlsx;Extended Properties=Excel 12.0;");
        MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
        DtSet = new System.Data.DataSet();
        
        MyCommand.Fill(DtSet);
        dataGridView1.DataSource = DtSet.Tables[0];
        MyConnection.Close();

Subjoin

It does not. The problem can still occur, I have not yet found out why, since my files are all from excel 2007, and some of them works, some of them does not. – Lanza

Do you have a source for that claim? I don't know myself, just wondering. :-) – Succulent

nice, working perfectly with ACE instead of JET – Schock

Answer 13

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.

Eroto

I ran into the same issue of the table not in the expected format. I verified my workbook had no hidden sheets. The actual worksheet name in the workbook is capitalized but in the C# code to parse the file I added .ToLower() for the tab name and now I can parse the excel file again. THANK YOU! – Impermanent

Answer 14

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

Foramen

Excel interop is not a recommended method of working with Excel. It can cause many issues and therefore should not be recommended. – Cockayne

Though this is an old post, I agree with MaxOvrdrv, using interop is not a good idea and should be avoided, if for no other reason than it requires a full install of Excel on the server. – Denys

You should absolutely not do this. – Swill

Answer 15

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

Quadri

Answer 16

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

Mazur

Answer 17

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.

Viveca

Answer 18

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.

Canales

Answer 19

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.

Bield

Answer 20

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.

Hageman

Answer 21

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

Jungjungfrau

Answer 22

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.

Impermanent

Answer 23

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).

Dorsum

Answer 24

I've had this occur on a IIS website that I host, rarely but periodically this error will popup for files that I've previously parsed just fine. Simply restarting the applicable app pool seems to resolve the issue. Not quite sure why though...

Middlebuster

Answer 25

This happened to us just recently. A customer of ours was getting this error when trying to upload their excel file on our website. I could open the xlsx file fine on ms excel and don't see any irregularities with the file. I've tried all mentioned solutions in here and none worked. And i found this link Treating data as text using Microsoft.ACE.OLEDB.12.0. What worked for our case is adding IMEX=1 attribute to the connection string. So if you are using Microsoft ACE OLEDB 12.0 this might help fix your issue. Hope this helps.

<add name="ExcelTextConnection" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1};IMEX=1'" />

Castorina

Probably has nothing to do with the case. The IMEX attribute serves to automatically identify the "data type" of each column. – Verbiage