SQLBulkCopy inserts a new row with NULL values for all columns

c# sqlbulkcopy

Question

I have this code which works fine and loads the excel data into an SQL table. The only problem is that it also inserts a new row with NULL values for all columns.

using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
 using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + Path.GetFileName(excelPath) + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }
            excel_con.Close();

using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.mySQLTable";


                    sqlBulkCopy.BulkCopyTimeout = 200;

                    sqlBulkCopy.ColumnMappings.Add("Employee", "Employee_Name");
                    sqlBulkCopy.ColumnMappings.Add("Sal/Hourly", "Sal/Hourly");

                    sqlBulkCopy.ColumnMappings.Add("Total", "Total"); 

                    con.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    con.Close();
                }
            }
}

This 2988 row is not present in excel, but the code creates it. Any help is appreciated.

enter image description here

Accepted Answer

You could change your query to skip empty rows in excel:

"SELECT * FROM [" + Path.GetFileName(excelPath) + "] WHERE [Employee] IS NOT NULL"

This should avoid that empty rows are added to the DataTable. Of course you could also remove them later but it would be less efficient. For example:

dtExcelData = dtExcelData.AsEnumerable()
    .Where(r => !String.IsNullOrEmpty(r.Field<string>("Employee")))
    .CopyToDataTable();



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why