SQLBulkCopy为所有列插入一个具有NULL值的新行

问题描述：

我有这段代码可以正常工作，并将excel数据加载到SQL表中.唯一的问题是，它还会为所有列插入一个带有NULL值的新行.

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();
                }
            }
}

此2988行在excel中不存在，但是代码创建了它.感谢您的帮助.

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

答

您可以更改查询以在excel中跳过空行:

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

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

这应避免将空行添加到 DataTable .当然，您也可以稍后将其删除，但这会降低效率.例如:

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();

SQLBulkCopy为所有列插入一个具有NULL值的新行

相关推荐