How can I read Excel file? [duplicate]

Question

I want to read excell file with this code:

var fileName = @"d:\1.xlsx"; 
var connectionString = string.Format(
    "Provider=Microsoft.Jet.OLEDB.4.0; data source="+fileName+
    "; Extended Properties=Excel 8.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [sheet1$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds, "anyNameHere");

DataTable data = ds.Tables["anyNameHere"];

But when run the program I get this error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: External table is not in the expected format.

How can i solve that?

This answer is very highly rated: stackoverflow.com/a/1363480/49 try it maybe — Leon Bambrick
– Leon Bambrick, Commented Apr 28, 2015 at 6:51
you should take a look at this site connectionstrings.com/excel — Shell
– Shell, Commented May 8, 2015 at 11:30

Thanos Markou · Accepted Answer · 2015-04-28 06:53:06Z

3

Your excel file is 2007 version, *.xlsx and you are using the wrong provider(Microsoft.Jet.OLEDB.4.0).

Try this approach:

var fileName = @"d:\1.xlsx"; 
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0;";

answered Apr 28, 2015 at 6:53

Thanos Markou

2,6344 gold badges27 silver badges32 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:24:09Z

0

Its already answer the question in the past post Please follow the below link

Answer link

The answer is very simple

sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test.xlsx;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Apr 28, 2015 at 7:09

Abhilash Thomas

8832 gold badges12 silver badges25 bronze badges

Comments

BrainCoder · Accepted Answer · 2015-04-28 07:18:57Z

0

you have diff version of Excel file, get the file name, if its extension is .xlsx, use this

var fileName = @"d:\1.xlsx"; 
var connectionString = string.Format(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="{0}";Extended Properties=Excel 12.0;", fileName);

answered Apr 28, 2015 at 7:18

BrainCoder

3,2475 gold badges32 silver badges33 bronze badges

Comments

bhupendra patel · Accepted Answer · 2015-04-28 07:23:15Z

0

I would suggest this Libary called FileHelpers. Since discovering it i have never written the plumbing code myself. It lets you concentrate on the actual business logic.

answered Apr 28, 2015 at 7:23

bhupendra patel

3,1891 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

How can I read Excel file? [duplicate]

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related