Error External table is not in the expected format

Question

I am trying to get data to sqlserver2005 on my C# windows application, from sheet1.xls file through oledb connection in visual studio-2008 and I am using windows7 os and I didn't installed excel on my system.

This is my connection string:

 string excelconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +excelfilepath + ";"+"Extended Properties='Excel 8.0;HDR=Yes;'";

I tried many ways but still i am getting this error :

"External table is not in the expected format. "

Please help me.

Solution

Use Microsoft.ACE.OLEDB.12.0 for excel files

string excelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +excelfilepath + ";"+"Extended Properties="Excel 12.0;HDR=Yes";

connectionstrings

Comments

[FADV]

i installed AccessDatabaseEngine.exe(Microsoft Access Database Engine 2010 Redistributable) and i tried again but still iam getting the same error

[Nagaraj S]

can you provide your connection string@FADV

[FADV]

string excelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilepath + ";" + "Extended Properties='Excel 8.0;HDR=Yes;'"; and the excel file path is:C:\sheet1.xls

[FADV]

i changed the string like u given above,but still having the same problem. i am using .xls format that is old version of excel(97-2003)

[Nagaraj S]

It may be possible that your Excel file itself contains some invalid data. That's why OleDB will not be able to read your Excel sheet.