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

Last Reply one year ago By itsjayshah

Posted one year ago

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 one year ago Modified on one year ago

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];
}