如何从DataTable中删除空行

Question

我正在尝试从 Excel 表格导入数据到数据库。Excel 表格中包含一些空行，我想要去除这些空行，然后将清洗过的数据插入到数据库中。
我参考了其他代码并编写了一段插入值的代码：

OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + txtExcelFile.Text + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'");
//DataTable dt = new DataTable();

try
{
    cnn.Open();
    OleDbDataAdapter data = new OleDbDataAdapter("select * from [Customers$]", cnn);
    data.Fill(dsExcel);
    dgvCustomers.ColumnHeadersVisible = false;

    SqlConnection connection = new SqlConnection("Data Source=COMPUTER-8EB749;Initial Catalog=KITS;Integrated Security=true");
    connection.Open();
    for (int i = 0; i < dsExcel.Tables[0].Rows.Count; i++)
    {
        string ID = ds.Tables[0].Rows[i][0].ToString();
        Int16 CustID = Convert.ToInt16(ID);
        string CustName = dsExcel.Tables[0].Rows[i][1].ToString();
        string CardScheme = dsExcel.Tables[0].Rows[i][2].ToString();
        string Outlet = dsExcel.Tables[0].Rows[i][3].ToString();
        string TerminalNum = dsExcel.Tables[0].Rows[i][4].ToString();
        Int32 Terminal = Convert.ToInt32(TerminalNum);
        string Date1 = dsExcel.Tables[0].Rows[i][5].ToString();
        DateTime Date = Convert.ToDateTime(Date1);
        string Time = dsExcel.Tables[0].Rows[i][6].ToString();
        DateTime DateTime = Convert.ToDateTime(Time);
        string Amount1 = ds.Tables[0].Rows[i][7].ToString();
        double Amount = Convert.ToDouble(Amount1);

        SqlCommand com = new SqlCommand("insert into Customer(CustID,CustName,CardScheme,Outlet,TerminalNum,TranDate,TranDateTime,Amount) values ('" + CustID + "','" + CustName + "','" + CardScheme + "','" + Outlet + "','" + Terminal + "','" + Date + "','" + DateTime + "','" + Amount + "')", connection);
        com.ExecuteNonQuery();
    }
    connection.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    MessageBox.Show("Data Inserted Successfully.");
}

有谁能告诉我如何删除空行，以便我只能插入数据？！

- Nazima

---

顺便提一下，你最好通过使用 using 块来正确处理对象，例如 using (OleDbConnection cnn = new OleDbConnection(...))。目的是为了防止资源泄漏。 - Shadow The Spring Wizard

Answer 1

这将删除所有行，其中每列都包含空格或空值：

dataTable = dataTable.Rows
    .Cast<DataRow>()
    .Where(row => !row.ItemArray.All(field => field is DBNull || 
                                     string.IsNullOrWhiteSpace(field as string)))
    .CopyToDataTable();

- Levitikon

---

为什么有些人（有点理智的人）会连接到远程数据库服务器，遍历所有记录并删除空行？？为什么不直接在数据库服务器上使用TSQL代码进行操作（因为显然您具有写入权限）？这是一行明显聪明的LINQ代码 - 但使用它并不那么聪明。 - rsenna

@tEsTA - 你假设数据来自数据库，但它也可能来自CSV、Excel等文件。这对于从Excel导入数据到“DataTable”时清除额外/空行非常有用。Excel经常在工作表末尾留下空行，您需要编写/运行宏截断工作表，所以这确实很有用！ - Josh M.

@JoshM。你说得对 - 毕竟，这个SO问题是关于Excel电子表格的。但我会保留我的建议，因为它可能会被一些不知道更好的新手开发人员用于真正的数据库表。 - rsenna

请注意，对于值类型，field as string 将会返回 null，因此我建议使用 field as string ?? field.ToString() ;)。 - shA.t

当列只有标题而没有数据时，我遇到了以下异常："源不包含任何数据行。" - karunakar bhogyari

Answer 2

试试这个。

public bool InsertRowsToDataBase()
{
    try
    {
        DataTable excelTable = new DataTable();

        string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + txtExcelFile.Text + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
        using (OleDbConnection cnn = new OleDbConnection(connString))
        {
            string query = "select * from [Customers$]";
            using (OleDbDataAdapter data = new OleDbDataAdapter(query, cnn))
            {
                data.Fill(excelTable);
            }
        }
        dgvCustomers.ColumnHeadersVisible = false;

        connString = "Data Source=COMPUTER-8EB749;Initial Catalog=KITS;Integrated Security=true";
        using (SqlConnection connection = new SqlConnection(connString))
        {
            connection.Open();
            for (int i = 0; i < excelTable.Rows.Length; i++)
            {
                //takes from the 3rd row
                if (i > 1)
                {
                    DataRow row = excelTable.Rows[i];
                    object ID = row[0];
                    if (ID != null && !String.IsNullOrEmpty(ID.ToString().Trim()))
                    {
                        Int16 CustID = Convert.ToInt16(ID);
                        string CustName = row[1].ToString();
                        string CardScheme = row[2].ToString();
                        string Outlet = row[3].ToString();
                        string TerminalNum = row[4].ToString();
                        Int32 Terminal = Convert.ToInt32(TerminalNum);
                        string Date1 = row[5].ToString();
                        DateTime Date = Convert.ToDateTime(Date1);
                        string Time = row[6].ToString();
                        DateTime DateTime = Convert.ToDateTime(Time);
                        string Amount1 = row[7].ToString();
                        double Amount = Convert.ToDouble(Amount1);

                        string columnNames = "CustID,CustName,CardScheme,Outlet,TerminalNum,TranDate,TranDateTime,Amount";
                        string query = String.Format("insert into Customer(0}) values ('{1}', '{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
                            columnNames, CustID, CustName, CardScheme, Outlet, Terminal, Date, DateTime, Amount);
                        using (SqlCommand com = new SqlCommand(query, connection))
                        {
                            com.ExecuteNonQuery();
                        }
                    }
                }
                //this is your last row. do whatever you want with this
                DataRow lastRow = excelTable.Rows[excelTable.Rows.Count - 1];
            }
        }
        return true;
    }
    catch (Exception exception)
    {
        Elmah.ErrorSignal.FromCurrentContext().Raise(exception);
        return false;
    }
}

请注意，我只是检查ID是否为空，不会插入任何这样的行，因为ID将是您的表中的主键。

- naveen

---

第一行是空的吗？你在哪里遇到了错误？是哪一行？ - naveen

第一行包含子标题，即“日期:”，需要删除...这个工作得很好...第二行包含列名，从第三行开始是数据...我知道为什么出错，因为在第二行中，列名的类型是字符串而不是整数...这就是为什么会显示错误...错误显示在这一行：Int16 CustID = Convert.ToInt16(ID); 因为该行包含的是列名而不是数值...所以我想知道如何先遍历列名然后再遍历数值...希望你明白。 - Nazima

如果你有中级知识，可以阅读《C# in a Nutshell》、《C# in Depth》等书籍。如果你是初学者，可以看《Head First C#》。如果你有良好且便宜的网络连接，可以访问http://www.asp.net/general/videos。阅读Scott Gu、Jon Skeet、Dave Ward、Eric Lippert等人的博客...祝你一切顺利！ - naveen

你好，我想知道如何将Excel表格中的新数据插入到数据库中，而不是插入之前的数据。我正在尝试使用DataRow的NewRow()方法，这样做正确吗？请告诉我具体步骤。 - Nazima

请将其发布为新问题nazima。 - naveen

Answer 3

以下代码将从数据表中删除所有空行：

DataTable dt = dt.Rows
                 .Cast<DataRow>()
                 .Where(row => !row.ItemArray.All(f => f is DBNull))
                 .CopyToDataTable();

或者

DataTable dt = dt.Rows
                 .Cast<DataRow>()
                 .Where(row => !row.ItemArray.All(f => f is DBNull || 
                                  string.IsNullOrEmpty(f as string ?? f.ToString())))
                 .CopyToDataTable();

- Karthick Ganesan

Answer 4

try
{
    OpenOleDBConnection();
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [" + SelectedSheet + "]", Connection);

    dataAdapter.Fill(DataTable);

    if ((DataTable != null) && (DataTable.Rows != null) && (DataTable.Rows.Count > 0))
    {
        List<System.Data.DataRow> removeRowIndex = new List<System.Data.DataRow>();
        int RowCounter = 0;
        foreach (System.Data.DataRow dRow in DataTable.Rows)
        {                            
            for(int index = 0; index < DataTable.Columns.Count; index++)
            {
                if (dRow[index] == DBNull.Value)  
                {
                    removeRowIndex.Add(dRow);
                    break;
                }
                else if (string.IsNullOrEmpty(dRow[index].ToString().Trim()))
                {
                    removeRowIndex.Add(dRow);
                    break;
                }
            }
            RowCounter++;
        }
        // Remove all blank of in-valid rows
        foreach (System.Data.DataRow rowIndex in removeRowIndex)
        {
            DataTable.Rows.Remove(rowIndex);
        }
    }
}
catch(Exception e)
{
    WPFMessageBox.Show(e.Message, Globalization.GetValue("Import_ImportOption_FormHeader"), WPFMessageBoxButtons.OK, WPFMessageBoxImage.Error);
}
finally
{
    CloseOleDBConnection();
}

在这里，如果某行中有空白条目，我也会跳过这些行。

- Noora

Answer 5

我已经编写了一个私有方法，可以实现这个功能。它接受一个DataTable作为参数，并返回相同的DataTable，但去除了空行。

private DataTable StripEmptyRows(DataTable dt)
    {
        List<int> rowIndexesToBeDeleted = new List<int>();
        int indexCount = 0;
        foreach(var row in dt.Rows)
        {
            var r = (DataRow)row;
            int emptyCount = 0;
            int itemArrayCount = r.ItemArray.Length;
            foreach(var i in r.ItemArray) if(string.IsNullOrWhiteSpace (i.ToString())) emptyCount++;

            if(emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);

            indexCount++;
        }

        int count = 0;
        foreach(var i in rowIndexesToBeDeleted)
        {
            dt.Rows.RemoveAt(i-count);
            count++;
        }

        return dt;
    }

- Cfrim

---

这只对我的一半空字符串行起作用。我使用了IsNullOrWhiteSpace而不是IsNullOrEmpty，它适用于我所有的情况。 - Ray Koren

Answer 6

检查空行

Foreach(DataRow as row in datable.Rows) {
    var isEmpty = row.ItemArray.All(c => c is DBNull);
    if(!isEmpty) {
        //Your Logic
    }
}

- Muhammad hanif

Answer 7

为什么不直接忽略在插入之前的空行呢？

if(string.IsNullOrEmpty(ID + CustName + CardScheme /*.. and so on */))
{
    continue;
}

就像这样:

for (int i = 0; i < dsExcel.Tables[0].Rows.Count; i++)
{
    string ID = ds.Tables[0].Rows[i][0].ToString();
    Int16 CustID = Convert.ToInt16(ID);
    string CustName = dsExcel.Tables[0].Rows[i][1].ToString();
    string CardScheme = dsExcel.Tables[0].Rows[i][2].ToString();
    string Outlet = dsExcel.Tables[0].Rows[i][3].ToString();
    string TerminalNum = dsExcel.Tables[0].Rows[i][4].ToString();
    Int32 Terminal = Convert.ToInt32(TerminalNum);
    string Date1 = dsExcel.Tables[0].Rows[i][5].ToString();
    DateTime Date = Convert.ToDateTime(Date1);
    string Time = dsExcel.Tables[0].Rows[i][6].ToString();
    DateTime DateTime = Convert.ToDateTime(Time);
    string Amount1 = ds.Tables[0].Rows[i][7].ToString();
    double Amount = Convert.ToDouble(Amount1);

    /*** Add this if-statement to you code! ***/
    if(string.IsNullOrEmpty(ID + CustName + CardScheme + Outlet + TerminalNum + Date1 + Time + Amount1))
    {
        continue;
    }

    SqlCommand com = new SqlCommand("insert into Customer(CustID,CustName,CardScheme,Outlet,TerminalNum,TranDate,TranDateTime,Amount) values ('" + CustID + "','" + CustName + "','" + CardScheme + "','" + Outlet + "','" + Terminal + "','" + Date + "','" + DateTime + "','" + Amount + "')", connection);
    com.ExecuteNonQuery();
}

- Andreas Ågren

---

请问您要继续哪一部分？'if(string.IsNullOrEmpty(ID + CustName + CardScheme /*.. and so on */)) { continue; }' - Nazima

@Nazima 在这条语句之前，你的for循环中加入判断条件，例如：SqlCommand com = new SqlCommand("insert ...。这样就不会插入空数据了。 - Andreas Ågren

我已经尝试了您说的在if语句中放置for循环的方法，但是它显示了一个错误。 - Nazima

@Nazima，我已经更新了我的答案，指出了你在for循环中应该更新的内容。 - Andreas Ågren

Answer 8

我修改了Cfrim的答案。你需要检查空字符串和包含空格的字符串。空格来自于删除的单元格，而空字符串来自于已删除的数据。

private DataTable StripEmptyRows(DataTable dt)
        {
            List<int> rowIndexesToBeDeleted = new List<int>();
            int indexCount = 0;
            foreach(var row in dt.Rows)
            {
                var r = (DataRow)row;
                int emptyCount = 0;
                int itemArrayCount = r.ItemArray.Length;
                    foreach (var i in dr.ItemArray)
                    {
                        if (string.IsNullOrEmpty(i.ToString()) || string.IsNullOrWhiteSpace(i.ToString()))
                            emptyCount++;
                    }

                if(emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);

                indexCount++;
            }

            int count = 0;
            foreach(var i in rowIndexesToBeDeleted)
            {
                dt.Rows.RemoveAt(i-count);
                count++;
            }

            return dt;
        }

- LostCause

Answer 9

for (int i = dt.Rows.Count - 1; i >= 0; i--) {
    if (dt.Rows[i][1] == DBNull.Value) {
        dt.Rows[i].Delete();
    }
}
dt.AcceptChanges();
return dt;

- Kennedy Ngure

---

你能否[修改]你的回答，解释一下为什么它与其他14个回答不同？以及为什么它可以解决这个问题？ - Elikill58

虽然这段代码可能回答了问题，但是提供关于为什么和/或如何回答问题的额外上下文有助于提高其长期价值。 - PCM

如果没有函数定义，为什么要使用return？ - General Grievance

Answer 10

这对我来说完美运作：

dt.Load(cmd.ExecuteReader());
var x = dt.Rows.Cast<DataRow>()
   .Where(row => !Array.TrueForAll(row.ItemArray, value => 
   { return value.ToString().Length == 0; }
   ));

dt = x.CopyToDataTable();

- Jomba