The Microsoft Jet database engine could not find the object 'Sheet1$_'

Question

I am reading data from an Excel file. when I read the normal Excel file,It works fine but when I read an excel file which has columns like shown below it does not find the work sheet and gives an exception-

The Microsoft Jet database engine could not find the object 'Sheet1$_'. Make sure the object exists and that you spell its name and the path name correctly.

My Code to read the excel is-

  private static DataTable getExcelData(string ExcelPath)
    {
        OleDbConnection con;
        string connectionString;
        string[] pathArray = ExcelPath.Split('.');
        var Extention = pathArray[pathArray.Length - 1];
        if (Extention == "xlsx")
            //read a 2007 file
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
        else
            //read a 97-2003 file
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                ExcelPath + ";Extended Properties=Excel 8.0;";

        con = new OleDbConnection(connectionString);

        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        DataTable dbSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
        var firstSheetName = dbSchema.Rows[0]["TABLE_NAME"];
        OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [" + firstSheetName + "] Where NOT [Event Code]=''", con);
        DataSet ds = new DataSet();
        cmd.Fill(ds);
        con.Close();
        return ds.Tables[0];
    }

}

I have to get all the columns inside Mon,Tues etc.

Answer 1

GetOleDbSchemaTable also returns hidden tables in your Excel file: usually a name like Sheet1$_ indicates an hidden table created when you apply a filter on Sheet1$.

You need to change your code: search for table that ends with $ to set firstSheetName.

Please note that OLEDB does not preserve the sheet order as they were in Excel.

Also note that you need to do this to read an excel file with multirow titles:

• set HDR=No in EXTENDED PROPERTIES of your connection string
• specify column name and select range in your OleDbCommand in order to skip the first two rows

For example:

SELECT [F1] AS Location,
    [F2] AS EmpId,
    [F3] AS EmpName,
    [F4] AS MondayShift,
    [F5] AS Monday15Min,
    [F6] AS Monday30Min,
    [F7] AS Monday15Min2
FROM [Sheet1$A3:G]