Oledb Exception in excel to datatable conversion

Question

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...

SELECT * FROM [Sheet1$] or [Customers$]

Manish Goswami
– Manish Goswami

2015-02-13 11:07:00 +00:00
Commented Feb 13, 2015 at 11:07 — Manish Goswami
– Manish Goswami, Commented Feb 13, 2015 at 11:07
thanks mani i gotta solution

Selvakumar Duraipandian
– Selvakumar Duraipandian

2015-02-13 11:12:22 +00:00
Commented Feb 13, 2015 at 11:12 — Selvakumar Duraipandian
– Selvakumar Duraipandian, Commented Feb 13, 2015 at 11:12

Davide Piras · Accepted Answer · 2015-02-13 10:58:43Z

1

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

answered Feb 13, 2015 at 10:58

Davide Piras

44.7k10 gold badges103 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oledb Exception in excel to datatable conversion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related