Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#2.0 ASP.NET
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??
 
Thanks,
Lok..
 

Please refer this code..
 
public enum ExcelVersion
{
	Excel,
        Excel2007
};
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);
                 GetPaymentVouchers();
        }
}
public void ExcelImport(ExcelVersion ver, string FilePath)
{
	switch (ver)
        {
		
                case ExcelVersion.Excel:
                    mConnString = ProviderExcel + FilePath + ";" + ExtendedPropertiesExcel;
                    break;
                case ExcelVersion.Excel2007:
                    mConnString = ProviderExcel2007 + FilePath + ";" + ExtendedPropertiesExcel2007;
                    break;
        }
}
public void GetPaymentVouchers()
{
	OleDbConnection cn = new OleDbConnection(mConnString);
        try
        {
		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();
                da.Fill(ds);
	}
	catch(Exception ex)
	{}
}
Posted 22-Nov-11 19:13pm
Edited 9-Aug-12 3:17am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can find information on how to open an Excel file here: http://www.connectionstrings.com/excel[^]
 
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!!
  Permalink  
Comments
LokeshZende at 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 at 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
good
Please Sign up or sign in to vote.

Solution 2

Thanks... It was really of great help...
  Permalink  
Comments
Member 8931254 at 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
0 OriginalGriff 400
1 Sergey Alexandrovich Kryukov 329
2 Afzaal Ahmad Zeeshan 264
3 BillWoodruff 245
4 CPallini 195
0 OriginalGriff 5,560
1 DamithSL 4,476
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 9 Aug 2012
Copyright © CodeProject, 1999-2014
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