"External table is not in the expected format"

Hii..

I am retrieving data from excel using following connection string:

static string datasource = "C:\\Drive\\Training_Template.xlsx";
OleDbConnection DBConnection = new OleDbConnection(@"PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=" + datasource + ";Extended Properties='Excel 12.0 Xml'");

Its working perfectly fine when i am using VS 2010 server but its giving error

"External table is not in the expected format"

when I am hosting it on IIS 7.5.

I am using excel 2010.

please suggest me some solution to deal with this error..

Thanks..

Re: "External table is not in the expected format"

Hi, are you sure that u are uploading a '.xlsx' file and that it haven't password ? :-)

This is the connection that I'm using and it works:

@"provider=Microsoft.ACE.OLEDB.12.0;data source=" + pathExcel + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'"

Re: "External table is not in the expected format"

Hii alejandro.lagos

Thanks for reply:)

I am not getting the same error now. This time I used the below mentioned connection string

OleDbConnection DBConnection = new OleDbConnection(@"PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=" + datasource + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'");

But now I am getting a new error

Microsoft Excel cannot access the file 'C:\trng\abc.xslx'. There are several possible reasons:

The file name or path does not exist.
The file is being used by another program.
The workbook you are trying to save has the same name as a currently open workbook.

for this I have killed "EXCEL.EXE" file from task manager but still it gives above mentioned error.

I seriously dont know what to do:(

Re: "External table is not in the expected format"

mannnn

Its working perfectly fine when i am using VS 2010 server but its giving error

"External table is not in the expected format"

when I am hosting it on IIS 7.5.

Are you sure that your issue happens when you deploy the file onto IIS7.5 and the problem happensAnd everything goes well when using the file on your local machineIf yesI guess there's something wrong your deployment of the file or something related to that

Re: "External table is not in the expected format"

Hii..

To ensure that there is no problem in hosting, I hosted another application in same way and it worked perfectly...

Re: "External table is not in the expected format"

mannnn
Extended Properties='Excel 12.0 Xml'

Try to remove "Xml" and have a try:-)

Re: "External table is not in the expected format"

mannnn
Extended Properties='Excel 12.0 Xml'

Try to remove "Xml" and have a try:-)

Re: "External table is not in the expected format"

Hi Decker Dong-MSFT

I have tried all these things...

Re: "External table is not in the expected format"

mannnn

Hi Decker Dong-MSFT

I have tried all these things...

Still the same errorOr error is changed

And is your excel file valid

Re: "External table is not in the expected format"

yup same error..

Re: "External table is not in the expected format"

mannnn

yup same error..

This seems to be a little strangeWell, let's try another way

1File - > Save As - Microsoft Excel 97-Excel 2003 &5.0/95 workbook(*.xls)

2Then do this

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                            "Data Source=C:\\Drive\\Training_Template.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
OleDbConnection DBConnection = new OleDbConnection(strConn);