How to SELECT from any spreadsheet in Excel File using OleDbDataAdapter

Question

I'm using OleDbDataAdapter to extract DataSet from excel file, but I have problems with SELECT statement inside

DataSet excelDataSet = new DataSet();
using (OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
{
     con.Open();
     OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [Name of spreadsheet]", con);
     cmd.Fill(excelDataSet);
     con.Close();
}

If you see I have "select * from [Name of spreadsheet]", but I need to get any spreadsheets, or for example 1st spreadsheet, but the name for this spreadsheet can be anything.

How to specify it? Is it any special characters like "select * from [%]"

Dutiful

Answer 1

You need to know the name of the sheet to apply the select statement at it.
And you need to add the special char $ at the end of the name.

Supposing you have a sheet named MyFirstSheet then you can select rows from it with

 OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [MyFirstSheet$]", con);

In case you don't know the names of your sheets you could call

using (OleDbConnection con = new OleDbConnection(connectionString))
{
    con.Open();
    DataTable dt = con.GetSchema("Tables");
    string firstSheet = dt.Rows[0]["TABLE_NAME"].ToString();
    ...... work with the first sheet .....
}

This example should give you the name of the first sheet in the excel file (other sheets are available in the successive rows after the first)

Karnak

Thanks! Works like a charm! Upvote and accept=) Will read about schemas in OleDbConnection=) – Dutiful