Error: The Microsoft Jet database engine could not find the object when excel sheet name with number in ASP.Net

Last Reply on Sep 28, 2017 06:31 AM By itsjayshah

Posted on Sep 28, 2017 05:24 AM

Excel sheet name start with number is not working in asp.net

 

 

       OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel + "; Extended Properties= \"Excel 8.0;HDR=Yes;IMEX=1\";");
      
        connection.Open();
        DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dt != null)
        {
            string[] excelSheets = new String[dt.Rows.Count];
           
            string sheetname;
            sheetname = "";
            foreach (DataRow row in dt.Rows)
            {
                if (row["TABLE_NAME"].ToString().Contains("$"))
                {
                    sheetname = row["TABLE_NAME"].ToString();

                    //Load the DataTable with Sheet Data so we can get the column header
                    OleDbCommand oconn = new OleDbCommand("select top 1 * from ["
                        +  sheetname  + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", connection);
                    OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                    DataTable dtdata = new DataTable();
                    adp.Fill(dtdata);
                    connection.Close();

		}
	    
	   }
	}

getting Error  This:-  

 

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

Posted on Sep 28, 2017 06:15 AM Modified on on Sep 28, 2017 06:17 AM

Hi itsjayshah;

I have checked. There is no problem with number.

The problem is when there is only number then the select query generates like SELECT * From ['9286$'A1:B4] which is not valid and the ' must be replaced.

Refer the below sample.

C#

private DataTable ImportExcel(String strFilePath)
{
    DataSet ds = new DataSet();
    //if (!File.Exists(strFilePath)) return false;
    String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFilePath + "; Extended Properties='Excel 8.0;HDR=Yes'";
    OleDbConnection connExcel = new OleDbConnection(strExcelConn);
    OleDbCommand cmdExcel = new OleDbCommand();
    try
    {
        cmdExcel.Connection = connExcel;
        //Check if the Sheet Exists
        connExcel.Open();
        DataTable dtExcelSchema;
        //Get the Schema of the WorkBook
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        connExcel.Close();
        //Read Data from Sheet1
        connExcel.Open();
        OleDbDataAdapter da = new OleDbDataAdapter();
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        //cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        //Range Query
        string query = "SELECT * From [" + SheetName + "A1:B4]";
        query = query.Replace("'", "");
        cmdExcel.CommandText = query;
        da.SelectCommand = cmdExcel;
        da.Fill(ds);
        connExcel.Close();
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        cmdExcel.Dispose();
        connExcel.Dispose();
    }
    return ds.Tables[0];
}