The following code snippet work fine under VS 2005, but it throws an exception:
System.Data.OleDb.OleDbException: External table is not in the expected format.
under VS 2003, ASP 1.1 when trying to open the connection. This occurs only for specific excel files. Does anybody know what's wrong? And where should I look for answers? Am I doing something wrong from ASP, or my excel file is the one to blame? Help me out,
pls.
System.Data.OleDb.OleDbConnection cn;
System.Data.OleDb.OleDbDataAdapter cmd;
System.Data.DataSet ds = new DataSet();
cn = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "data source=E:\\a.xls;Extended Properties=Excel 8.0;");
this error generally occurs if the excel u r trying to connect is not in a valid format...for ex..the excel might be in HTML table format or SYLK file format...U can happily connect excel excel read it and fill it into a datatable..fast and smooth too...here
is sample vb code..below IMEX property is used to avoid datatypes of cells..
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\new1.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
oledbConn.ConnectionString = strConn
' Open oledb connection
If oledbConn.State = Data.ConnectionState.Closed Then
oledbConn.Open()
End If
Dim oledbdatatbl As New System.Data.DataTable
' Select excel sheet.
oledbCmd.CommandText = "SELECT * FROM [sheet1$]"
oledbCmd.CommandType = Data.CommandType.Text
oledbCmd.Connection = oledbConn
oledbadap.SelectCommand = oledbCmd
oledbadap.Fill(oledbdatatbl)
GridView1.DataSource = oledbdatatbl.DefaultView
GridView1.DataBind()
If oledbConn.State = Data.ConnectionState.Open Then
oledbConn.Close()
End If
The File that you are accessing using OLEDB is not in correct format i-e information missing.
i was also getting the same error. i was using my application to extract contact information from yellowpages and then save that information to a excel file generated using code. now when i wanted to open that file and insert / read data using oledb it gave
me the error "External table is not in the expected format."
Resolution
I created an excel file using MS Excel and then i defined my columns over there and when accessed using OLEDB there was no error and data insertion was successful using code. Now this doesnot seems to be a good solution but it worked for me in that typical
scenario
The excel file that i was creating from code and the file created by MS Excel Application had different file sizes so this means that some of the information in file created by me using code was missing.
Hi i am new in this group but i always use to take help from this forum. So thanks for all that.
Now i am also facing the same problem. I tried to export excel in dataset by using below code but getting error "External table is not in the expected format."
Hi i am new in this group but i always use to take help from this forum. So thanks for all that.
Now i am also facing the same problem. I tried to export excel in dataset by using below code but getting error "External table is not in the expected format."
OleDbDataAdapter MyOwner=new OleDbDataAdapter("select distinct [Opportunity Owner] from [SalesReport_Cap$]",conSalesRpt);
DataSet downer=
new DataSet();
Thanks,
MyOwner.Fill(downer,"SalesReport")
<div>Even now i am also facing when i delete old excel file and copy same file again and than try to excess it gives me same error.
When you download file from ABC site to
let say c drive and run the scheduler, Scheduler doesn’t read that file.
When you open that excel, Copy the excel data, create new excel by and past all data to new excel file. Scheduler reads that new
file easily and process.
</div>
Its really important and urgent for me and i am also struggling on this from last 2 days. so please help me out. lots of thanks...
I can solve this problem. It happens to me because the xls file I have was a exported file from my system in HTML format (my system creates a HTML file named with XLS extension). So I could not use the connection string with the Extended Property = Excel
8.0, but with "HTML Import".
Consegui solucionar este problema. Isto aconteceu comigo porque o arquivo xls que eu tenho era um arquivo que foi exportado do meu sistema no formato HTML (meu sistema cria um arquivo HTML nomeado com a extensão XLS). Assim eu não pude usar a connection string com a Extended Property = Excel 8.0, mas com "HTML Import"
regenieva
Member
35 Points
7 Posts
External table is not in the expected format.
May 25, 2006 08:28 AM|LINK
Hi all!
The following code snippet work fine under VS 2005, but it throws an exception: System.Data.OleDb.OleDbException: External table is not in the expected format.
under VS 2003, ASP 1.1 when trying to open the connection. This occurs only for specific excel files. Does anybody know what's wrong? And where should I look for answers? Am I doing something wrong from ASP, or my excel file is the one to blame? Help me out, pls.
System.Data.OleDb.OleDbConnection cn;
System.Data.OleDb.OleDbDataAdapter cmd;
System.Data.DataSet ds = new DataSet();
cn = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "data source=E:\\a.xls;Extended Properties=Excel 8.0;");
cn.Open();
Thanks!!
Eva.
e-Realm
Member
5 Points
1 Post
Re: External table is not in the expected format.
Sep 11, 2006 11:58 PM|LINK
Hi Eva,
I am having the same problem.
Have you found a solution yet?
Help very much appreciated.
regards
Mark
Caddre
All-Star
26581 Points
5308 Posts
Re: External table is not in the expected format.
Sep 12, 2006 12:13 AM|LINK
Gift Peddie
net4develope...
Member
10 Points
2 Posts
Re: External table is not in the expected format.
Nov 06, 2006 11:10 AM|LINK
donot you know that you can query the Excel sheet and use it like a DB then..
sorry
http://www.Net4developers.com
Caddre
All-Star
26581 Points
5308 Posts
Re: External table is not in the expected format.
Nov 06, 2006 01:11 PM|LINK
Gift Peddie
bharathreddy...
Member
365 Points
113 Posts
Re: External table is not in the expected format.
Dec 20, 2006 03:18 AM|LINK
this error generally occurs if the excel u r trying to connect is not in a valid format...for ex..the excel might be in HTML table format or SYLK file format...U can happily connect excel excel read it and fill it into a datatable..fast and smooth too...here is sample vb code..below IMEX property is used to avoid datatypes of cells..
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\new1.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
oledbConn.ConnectionString = strConn
' Open oledb connection
If oledbConn.State = Data.ConnectionState.Closed Then
oledbConn.Open()
End If
Dim oledbdatatbl As New System.Data.DataTable
' Select excel sheet.
oledbCmd.CommandText = "SELECT * FROM [sheet1$]"
oledbCmd.CommandType = Data.CommandType.Text
oledbCmd.Connection = oledbConn
oledbadap.SelectCommand = oledbCmd
oledbadap.Fill(oledbdatatbl)
GridView1.DataSource = oledbdatatbl.DefaultView
GridView1.DataBind()
If oledbConn.State = Data.ConnectionState.Open Then
oledbConn.Close()
End If
syedrizwansh...
Member
14 Points
10 Posts
Re: External table is not in the expected format.
Sep 12, 2007 07:41 AM|LINK
The File that you are accessing using OLEDB is not in correct format i-e information missing.
i was also getting the same error. i was using my application to extract contact information from yellowpages and then save that information to a excel file generated using code. now when i wanted to open that file and insert / read data using oledb it gave me the error "External table is not in the expected format."
Resolution
I created an excel file using MS Excel and then i defined my columns over there and when accessed using OLEDB there was no error and data insertion was successful using code. Now this doesnot seems to be a good solution but it worked for me in that typical scenario
The excel file that i was creating from code and the file created by MS Excel Application had different file sizes so this means that some of the information in file created by me using code was missing.
hemantsudele...
Member
4 Points
2 Posts
Re: External table is not in the expected format.
Sep 19, 2007 01:40 PM|LINK
Hi i am new in this group but i always use to take help from this forum. So thanks for all that.
Now i am also facing the same problem. I tried to export excel in dataset by using below code but getting error "External table is not in the expected format."
SO can you please help me on this.
string
conSalesRpt = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Scheduler_Automation_Extraction\Scheduler_db_SalesReport\SalesReport_Cap.xls;Extended Properties=""Excel 8.0;"""; OleDbDataAdapter MyOwner=new OleDbDataAdapter("select distinct [Opportunity Owner] from [SalesReport_Cap$]",conSalesRpt);DataSet downer=
new DataSet();
Thanks,MyOwner.Fill(downer,"SalesReport");
hemantsudele...
Member
4 Points
2 Posts
External table is not in the expected format.
Sep 21, 2007 03:28 AM|LINK
Hi i am new in this group but i always use to take help from this forum. So thanks for all that.
Now i am also facing the same problem. I tried to export excel in dataset by using below code but getting error "External table is not in the expected format."
SO can you please help me on this.
string conSalesRpt = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Scheduler_Automation_Extraction\Scheduler_db_SalesReport\SalesReport_Cap.xls;Extended Properties=""Excel 8.0;""";
OleDbDataAdapter MyOwner=new OleDbDataAdapter("select distinct [Opportunity Owner] from [SalesReport_Cap$]",conSalesRpt);DataSet downer=
new DataSet();
Thanks,MyOwner.Fill(downer,"SalesReport")
<div>Even now i am also facing when i delete old excel file and copy same file again and than try to excess it gives me same error.- When you download file from ABC site to
let say c drive and run the scheduler, Scheduler doesn’t read that file.
- When you open that excel, Copy the excel data, create new excel by and past all data to new excel file. Scheduler reads that new
file easily and process.
</div>Its really important and urgent for me and i am also struggling on this from last 2 days. so please help me out. lots of thanks...
<p>
Marcus Miris
Member
2 Points
1 Post
Re: External table is not in the expected format.
Oct 16, 2007 05:55 PM|LINK
Hi!
I can solve this problem. It happens to me because the xls file I have was a exported file from my system in HTML format (my system creates a HTML file named with XLS extension). So I could not use the connection string with the Extended Property = Excel 8.0, but with "HTML Import".