Unable to Open an Excel file using System.Data.OleDb.OleDbConnection [duplicate]

Question

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.

Dave Zych · Accepted Answer · 2013-05-21 20:07:04Z

4

Changed the connection string to

public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
    + path + ";Extended Properties=Excel 12.0;";

edited May 21, 2013 at 20:07

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

answered Dec 1, 2010 at 9:31

Ananth

10.8k25 gold badges86 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:18:39Z

2

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

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Dec 1, 2010 at 7:22

KBoek

5,9555 gold badges35 silver badges51 bronze badges

1 Comment

Ananth Over a year ago

Thanks ..Got the answer from there

hallie · Accepted Answer · 2010-12-01 07:27:04Z

2

.xlsx has different connectionstrings

answered Dec 1, 2010 at 7:27

hallie

2,83320 silver badges28 bronze badges

1 Comment

Ananth Over a year ago

Thanks hallie...Changed the connection string and it started working.

Dave Zych · Accepted Answer · 2013-05-21 20:07:36Z

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;

edited May 21, 2013 at 20:07

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

answered Dec 1, 2010 at 7:27

Lake

461 bronze badge

1 Comment

Ananth Over a year ago

This is the same code which works well for Word 2003..Need to change the connection string to make it work for 2007

Collectives™ on Stack Overflow

Unable to Open an Excel file using System.Data.OleDb.OleDbConnection [duplicate]

4 Answers 4

Comments

1 Comment

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

1 Comment

Linked

Related