CODEWITHSUNDEEP
c#oledb
apomene
apomene

Reputation: 14389

Read Data from Excel using OLEDB

I read an excel file using OLEDB. Below is the code:

            string conn;             
            conn = ("Provider=Microsoft.ACE.OLEDB.12.0;" +
            ("Data Source=" + _filename + ";" +
            "Extended Properties=\"Excel 12.0;\""));
            OleDbConnection oleDBCon = new OleDbConnection(conn);
            oleDBCon.Open();
            DataTable dt = oleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                    
            string SSQL = "SELECT * from [ Sheet1$ ]";
            OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
            DataSet ds = new DataSet();
            oleDA.Fill(ds);
            DataTable _DtTable = ds.Tables[0];
            oleDBCon.Close();
            dataGridView1.DataSource = _DtTable;
            foreach (DataRow rows in _DtTable.Rows)
            {
                string Description = rows[0].ToString();
                string Code= rows[1].ToString();              
                textBox1.AppendText("Printing Description: " + Description + " and Code: " + Code + ",Date:" + DateTime.Now.ToString() + Environment.NewLine);                             
            }

The excel file is as follows:

enter image description here

The data printed in textBox1 are:

Printing Description:Desc 2 and Code: Code 2,Date:20/12/2014 12:36:54 μμ
Printing Description: Desc 3 and Code: Code 3,Date:20/12/2014 12:36:54 μμ

So, my problem is that the 1st row of excel is going to the header of the Datatable. How can I avoid that (without adding any extra 1st row to excel)?

Upvotes: 1

Views: 3624

Answers (1)

Yogesh Sanchania
Yogesh Sanchania

Reputation: 48

Just add "HDR=No" at the end of your connection string which means "No header row that indicates column but it contains data", then you will be able to fetch 1st row data also.

So your complete connection string would be

 conn = ("Provider=Microsoft.ACE.OLEDB.12.0;" +
        ("Data Source=" + _filename + ";" +
        "Extended Properties=\"Excel 12.0;\";HDR=No"));

Upvotes: 2

Related Questions