Can't read from my Excel file using C#

Question

I have an Excel 2007 file "my.xlsx" and a sheet named "States", and I have the following code

 using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\my.xlsx;Extended Properties='Excel 12.0 Xml;HDR=NO'"))
        {
            OleDbCommand cmd = new OleDbCommand("select * from [States]", con);

            con.Open();
            OleDbDataReader reader = cmd.ExecuteReader();
            while(reader.Read())
                Console.WriteLine(reader[0]);
        }

It keeps throwing exception saying "The Microsoft Office Access database engine could not find the object 'States'. Make sure the object exists and that you spell its name and the path name correctly.".

Could someone help to see what's wrong with my code please?

Chris · Accepted Answer · 2010-07-20 00:50:52Z

4

I know this may be not exactly what you want to hear, but you're in a long line of people who have had struggles trying to read excel files by using oledb...

I've had a lot more luck using libraries such as NPOI to read Excel files from C#:

http://npoi.codeplex.com/ (recommended)

http://nexcel.sourceforge.net/

http://sourceforge.net/projects/koogra/

answered Jul 20, 2010 at 0:50

Chris

41k46 gold badges197 silver badges239 bronze badges

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

Comments

Oren · Accepted Answer · 2010-07-20 00:33:34Z

2

I think that you need to open the connection before creating the command - not sure if it's a big deal...

Then add a $ at the end of the sheet name:

OleDbCommand cmd = new OleDbCommand("select * from [States$]", con);

Basically [Name] refers to a named range, whereas [Name$] refers to a sheet.

For more information see this KB: http://support.microsoft.com/kb/316934

edited Jul 20, 2010 at 0:33

answered Jul 20, 2010 at 0:24

Oren

3,2462 gold badges21 silver badges15 bronze badges

3 Comments

Ray Over a year ago

Tried that and got same exception saying could not find object "States$"

Mikos Over a year ago

double check what your worksheet is named as, is it named "States"?

Oren Over a year ago

Also, try opening the connection, before creating the command.

Ray · Accepted Answer · 2010-07-20 16:59:11Z

1

I just got it working by visiting this page

http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

For my requirement, I only need to read an Excel file. Most of the solutions I searched show you to use some kind of library, which is overkill for me. I was really looking for someone to just post a code snippet on how to read the file, but I only found that on the page I put the link with.

edited Jul 20, 2010 at 16:59

answered Jul 20, 2010 at 0:37

Ray

12.5k28 gold badges99 silver badges144 bronze badges

1 Comment

TEEKAY Over a year ago

link is dead. do you have the answer/snippet handy or could you come up with one from memory?

Collectives™ on Stack Overflow

Can't read from my Excel file using C#

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related