OLEDB Does not return first row of excel file

Question

I'm using Microsoft.ACE.OLEDB.12.0 to connect to Microsoft excel file and fetch data from it. I write my codes in C# language using Visual Studio 2012. here is my code:

public DataTable getData(string fileName, string sheetName)
{
    connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" 
                      + fileName
                      + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";

    errorCode = ErrorDefinition.ERROR_NOERROR;
    errorMessage = "";
    DataTable dt = new DataTable();            
    try
    {
            string query = "SELECT * FROM [" + sheetName + "]";
            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, con);
            dataAdapter.Fill(dt);
    }
    catch (Exception exp)
    {
            errorCode = ErrorDefinition.ERROR_OLEDBERROR;
            errorMessage = exp.Message;
    }
    return dt;
}

The problem is that dt does not contain the first row of the specified sheet in file. What's wrong with it?

Answer 1

In your connection string you use the setting "HDR=YES", this means that the first row from your Excel file is treated by OleDb as the row containing the table's field names represented by the current sheet.

Using "HDR=NO" indicates to OleDb that the first row contains data and the column names are automatically named, in progression. as "F1", "F2", "F3" etc....