how to Read xls file using OLEDB?

Question

I want to read all data from an xls file using OLEDB, but I don't have any experience in that.

string filename = @"C:\Users\sasa\Downloads\user-account-creation_2.xls";
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties='Excel 8.0;HDR=YES'";

using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connString))
{
    conn.Open();
    System.Data.OleDb.OleDbCommand selectCommand = new System.Data.OleDb.OleDbCommand("select * from [Sheet1$]", conn);
    System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(selectCommand);

    DataTable dt = new DataTable();
    adapter.Fill(dt);

    int counter = 0;
    foreach (DataRow row in dt.Rows)
    {
        String dataA = row["email"].ToString();
        // String dataB= row["DataB"].ToString();
        Console.WriteLine(dataA + " = ");
        counter++;
        if (counter >= 40) break;
    }
}

I want to read all data from email row

I get this error

'Sheet$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long

Answer 1

Well, you don't have a sheet called Sheet1 do you? Your sheet seems to be called "email address from username" so your query should be....

Select * From ['email address from username$']

Also please don't use Microsoft.Jet.OLEDB.4.0 as it's pretty much obsolete now. Use Microsoft.ACE.OLEDB.12.0. If you specify Excel 12.0 in the extended properties it will open both .xls and .xlsx files.

You can also load the DataTable with a single line...

dt.Load(new System.Data.OleDb.OleDbCommand("Select * From ['email address from username$']", conn).ExecuteReader());

To read the names of the tables in the file use...

DataTable dtTablesList = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

foreach (DataRow drTable in dtTablesList.Rows)
{
    //Do Something
    //But be careful as this will also return Defined Names. i.e ranges created using the Defined Name functionality
    //Actual Sheet names end with $ or $'
    if (drTable["Table_Name"].ToString().EndsWith("$") || drTable["Table_Name"].ToString().EndsWith("$'"))
    {
        Console.WriteLine(drTable["Table_Name"]);
    }
}