Unable to read first row from Excel using SqlBulkCopy

Question

I am using following code to update an Excel file into SQL Server. Code is working but it is not able to insert first row into table.

OleDbConnection OleDb = new OleDbConnection(ConnectionString);
OleDbCommand OleDbCmm = new OleDbCommand(Query,OleDb);
OleDbDataReader OleDbdr;
OleDb.Open();

if (OleDb.State == ConnectionState.Open)
{
    OleDbdr = OleDbCmm.ExecuteReader();
    SqlBulkCopy BulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["connstring"].ToString());
    BulkCopy.DestinationTableName = "TempTable";

    if (OleDbdr.Read())
    {
       BulkCopy.WriteToServer(OleDbdr);
    }
 }

 OleDb.Close();

Answer 1

Even I was facing same problem, this is because I was using Read() Method like below.

while (dr.Read()) {
    bulkcopy.WriteToServer(dr);
}

Solution to above problem is remove dr.Read() method and while loop use bulkcopy.WriteToServer(dr) without any condition and Read() Method.

Answer 2

One possible reason for this may be that you've indicated in your connection string that first row contains column names (HDR=YES) thus that row is not treated as containing data.

EDIT

Another possible reason for this would be the call to OleDbDataReader.Read() method before passing the reader to SqlBulkCopy object. MSDN states:

The copy operation starts at the next available row in the reader. Most of the time, the reader was just returned by ExecuteReader or a similar call, so the next available row is the first row.

Thus, in your case you should not call OleDbdr.Read() because this advances the reader to the first row; you should let BulkCopy call Read() and it will start reading from the first row. Your code should be:

OleDbdr = OleDbCmm.ExecuteReader();
SqlBulkCopy BulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["connstring"].ToString());
BulkCopy.DestinationTableName = "TempTable";
BulkCopy.WriteToServer(OleDbdr);

Answer 3

You need to set header for the Excel to sql as I have done in my

string conn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Your D S+ ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection sSourceConnection = new OleDbConnection(conn);
using (sSourceConnection)
{
   DataTable dtExcelData = new DataTable();
   string[] SheetNames = GetExcelSheetNames(strFileName);
   string[] preColumnHeader = new string[]{ "CarrierId", "StateId", "TerrCd", "ProgramId", "ClassId",
   "PremTypeID","Limit50_100", "Limit100_100", "Limit100_200", "Limit300_300", "Limit300_600", 
   "Limit500_500","Limit500_1mil", "Limit1mil_1mil", "Limit1mil_2mil", "OtherParameter" };
   sSourceConnection.Open();
   string strQuery = string.Empty;
   strQuery = "SELECT * FROM [" + SheetNames[0] + "]";

   OleDbDataAdapter oleDA = new OleDbDataAdapter(strQuery, sSourceConnection);
   oleDA.Fill(dtExcelData);
   sSourceConnection.Close();
   string[] colName = new string[dtExcelData.Columns.Count];
   int i = 0;
   foreach (DataColumn dc in dtExcelData.Columns)
   {
       colName[i] = dc.ColumnName;
       i++;
   }
   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr))
   {
       bulkCopy.DestinationTableName = "tbl_test";
       bulkCopy.WriteToServer(dtExcelData);
   }
}