Click here to Skip to main content
13,193,064 members (62,870 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Hi Friends,
I am generating an Excel file (Excel 2003 i.e. ".xls") using some data tables.
File generates successfully.
Now I am trying to open the same file using upload control and OLEDB connection, but I am getting this strange error.
"External table is not in the expected format."
Now, if I open the file and try to run my code, it works fine....
whereas it is expected that it should give me an error saying "File is already being used." (Please correct me here if I am wrong.)

I googled a lot about this but got no solution. All I got is,
use Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";
instead of "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

I use these two providers depending upon type of the file (i.e .xls / .xlsx)

I ahve (Do spell check before you post.) MS Office 2007 installed on my machine.

Can you please help me friends??


Please refer this code..

public enum ExcelVersion
private string ProviderExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
private string ExtendedPropertiesExcel = "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
private string ProviderExcel2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";
private string ExtendedPropertiesExcel2007 = "Extended Properties=\"Excel 12.0;HDR=YES\"";
private string mConnString = "";
protected void btnPost_Click(object sender, EventArgs e)
	if (rdpUpload.UploadedFiles.Count == 0)
        	throw new Exception("Please select file to upload");
	string sExtension = "";
        string FileName = "";
        ApplicationDirectoryTypeInfoList oApplicationDirectoryTypeInfoList = ApplicationDirectoryTypeInfoList.GetAllApplicationDirectories();  // my classes to read Directory..
        ApplicationDirectoryTypeInfo oApplicationDirectoryTypeInfo = oApplicationDirectoryTypeInfoList.GetItem(35);
        string FilePath = oApplicationDirectoryTypeInfo.ApplicationDirectoryFQN;
        FilePath = FilePath.Substring(FilePath.IndexOf("/") + 1);
        FilePath = AppDomain.CurrentDomain.BaseDirectory + FilePath;
        foreach (UploadedFile file in rdpUpload.UploadedFiles)
		FileName = file.GetName();
                sExtension = file.GetName();
                sExtension = sExtension.Substring(sExtension.LastIndexOf(".") + 1);
                if (sExtension != "xls")
                    throw new System.Exception("Select .xls/.xlsx file ");
                 ExcelImport(ExcelVersion.Excel, FilePath + "//" + FileName);
public void ExcelImport(ExcelVersion ver, string FilePath)
	switch (ver)
                case ExcelVersion.Excel:
                    mConnString = ProviderExcel + FilePath + ";" + ExtendedPropertiesExcel;
                case ExcelVersion.Excel2007:
                    mConnString = ProviderExcel2007 + FilePath + ";" + ExtendedPropertiesExcel2007;
public void GetPaymentVouchers()
	OleDbConnection cn = new OleDbConnection(mConnString);
		cn.Open();           //   I get Error here while opening Connection...
                DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string SheetName = schemaTable.Rows[0][2].ToString();
                StringBuilder SelectStatement = new StringBuilder();
                SelectStatement.Append("SELECT * FROM [").Append(SheetName).Append("]");
                OleDbDataAdapter da = new OleDbDataAdapter(SelectStatement.ToString(), cn);
                DataSet ds = new DataSet();
	catch(Exception ex)
Posted 22-Nov-11 18:13pm
Updated 9-Aug-12 2:17am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

You can find information on how to open an Excel file here:[^]

If the file is creating properly (and you can open it directly from Excel), then it's in the right format - it must be your code that needs updating!!
LokeshZende 23-Nov-11 1:47am
Hi _Domain S_,
Thanks for your quick response.
Please see my code and tell me if anything is wrong with it..
debbie61 4-Apr-12 13:15pm
In respect to Damian's post that if the file is creating properly(and you can open it directly from Excel)....
That is not correct. I have run the same code over a spreadsheet multiple times and had it fail for External table format error and then magically it works.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Thanks... It was really of great help...
Member 8931254 21-Aug-13 0:55am
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ofImport.FileName + ";Extended Properties=" + "\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
I am using this connection to import a excel file but when i import .csv that time i got the exception "external table is not in the expected format" how to solve this error

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.171017.2 | Last Updated 9 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100