1

Iam trying to open an XLSX file (so that I can create a datatable ) Iam using the the below code.

System.Data.OleDb.OleDbConnection oleDbCon;
System.Data.OleDb.OleDbDataAdapter oleDbDataAd;
oleDbCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");

but when file path contains a file with extension xlsx , i get an error "External table is not in the expected format."

Above code works fine when file is of extention xls.

Do I have to change the connection string ?

Any help ?Thanks in Advance.

npinti
  • 51,780
  • 5
  • 72
  • 96
Ananth
  • 10,330
  • 24
  • 82
  • 109

4 Answers4

4

Changed the connection string to

public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
    + path + ";Extended Properties=Excel 12.0;";
Dave Zych
  • 21,581
  • 7
  • 51
  • 66
Ananth
  • 10,330
  • 24
  • 82
  • 109
2

see Excel "External table is not in the expected format."

Community
  • 1
  • 1
KBoek
  • 5,794
  • 5
  • 32
  • 49
2

.xlsx has different connectionstrings

hallie
  • 2,760
  • 19
  • 27
2
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="
    + Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();  
string strExcel = "";   
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel="select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds,"table1");   
return ds;
Dave Zych
  • 21,581
  • 7
  • 51
  • 66
Lake
  • 46
  • 1
  • This is the same code which works well for Word 2003..Need to change the connection string to make it work for 2007 – Ananth Dec 01 '10 at 09:33