Problem while reading excel file in C# with ADO.Net

Question

I just started C# today, and i have trouble reading an excel file.

Here's what i did :

OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended Properties=""Excel 12.0;HDR=YES;""");

OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet$]", connection);

connection.Open();

OleDbDataReader reader = command.ExecuteReader();

And i have an exception saying it can't find "Sheet$" (i can't copy/paste the exception, because the message is in french, and i don't know yet how to have generic messages in english)

Could someone tell me what did i do wrong ?

I followed what they say in tutorials or like here Reading Excel files from C#

Thanks, really !

The name is Sheet. I thought the $ was mandatory, it's in all examples. But with [Sheet] the exception says that it can't find "Sheet". — Maxime ARNSTAMM
– Maxime ARNSTAMM, Commented Jul 21, 2010 at 15:59

Simon · Accepted Answer · 2010-07-21 16:38:09Z

1

You can get a list of "Tables" (worksheets or named ranges) from your Excel file:

DataTable schema = connection.GetSchema(OleDbMetaDataCollectionNames.Tables);

The TABLE_NAME field is the one you should use in your query. If the value has single quotes around it, you'll need to include those. For example, my file has worksheets named "GP" and "Kimberly Clark". In the schema table, they appear as "GP$" and "'Kimberly Clark$'". In a query, I would reference them as "[GP$]" or "['Kimberly Clark$']".

answered Jul 21, 2010 at 16:38

Simon

1,2071 gold badge7 silver badges14 bronze badges

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

1 Comment

Maxime ARNSTAMM Over a year ago

i restarted VS and my code worked, i don't know why. But what you said is really useful, thanks !!

Collectives™ on Stack Overflow

Problem while reading excel file in C# with ADO.Net

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related