CODEWITHSUNDEEP
c#asp.netexcel
Selvakumar Duraipandian
Selvakumar Duraipandian

Reputation: 457

Oledb Exception in excel to datatable conversion

I am trying to use Excel as my Database in asp.net and i used the following code 

 public DataTable exceldata(string filePath)
{
    DataTable dtexcel = new DataTable();
    bool hasHeaders = true;
    string HDR = hasHeaders ? "Yes" : "No";
    string strConn;
    if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
    else
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
    OleDbConnection conn = new OleDbConnection(strConn);
    conn.Open();
    DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    //Looping Total Sheet of Xl File
    /*foreach (DataRow schemaRow in schemaTable.Rows)
    {
    }*/
    //Looping a first Sheet of Xl File
    DataRow schemaRow = schemaTable.Rows[0];
    string sheet = schemaRow["TABLE_NAME"].ToString();
    if (!sheet.EndsWith("_"))
    {
        string query = "SELECT  * FROM Customers";
        OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
        dtexcel.Locale = CultureInfo.CurrentCulture;
        daexcel.Fill(dtexcel);
    }

    conn.Close();
    return dtexcel;

}

When i executed this code,

The Microsoft Access database engine could not find the object 'Customers'. Make sure the object exists and that you spell its name and the path name correctly. If 'Customers' is not a local object, check your network connection or contact the server administrator. it was populated here..What should i do.help me out guys please...

Upvotes: 1

Views: 385

Answers (1)

Davide Piras
Davide Piras

Reputation: 44595

When you query Excel file via .NET you should use the following syntax:

select * from [Customers$]

this assuming that your Excel workbook contains a WorkSheet called Customers

Upvotes: 1

Related Questions