How can i remove empty rows from DataTable from Excel?

Question

I have the same problem. But the differences is that my empty rows are in the middle and I have over 50 columns. The user wants to see the duplicated rows, so that means I cannot use SELECT DISTINCT * FROM [excel]

The empty rows could be anywhere. The largest excel I faced with so far have over 100,000 rows.

Is there a more efficient way to REMOVE the empty rows or MUST I loop through and check all columns in each row??

   void SelectDataFromExcel()
        {

            string connectionString = ConfigurationSettings.AppSettings["ExcelConn"].ToString();
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();   
            OleDbCommand dbCommand;
            OleDbDataAdapter dataAdapter;
             foreach (var sheet in Sheets)
            {
                dbCommand = new OleDbCommand("select DISTINCT* From[" + sheet + "$]", excelConnection);
                System.Threading.Thread.Sleep(1000);
                this.Invoke((MethodInvoker)delegate
                {
                    listBox1.Items.Add("Tablo ismi: " + sheet.ToUpper(CultureInfo.InvariantCulture) + " Tablo Satır Sayısı: "+ dSet.Tables[sheet].Rows[0][0].ToString());
                });
                dataAdapter = new OleDbDataAdapter(dbCommand);
                dTable = new DataTable();
                dataAdapter.Fill(dTable);
                dTable.TableName = sheet.ToUpper(CultureInfo.InvariantCulture);;

                ArrangedDataList(dTable);
                FillSqlTable(dTable, dTable.TableName);
                dTable.Dispose();
                dataAdapter.Dispose();
                dbCommand.Dispose();
            }

            excelConnection.Close();
            excelConnection.Dispose();
            t1.Abort();
        }

Hi I found solution for this which i have pasted in my edited answer — Pranay Rana, May 05 '12 at 09:43
Dont forget to mark answer as accepted and upvote it if it works for you.... — Pranay Rana, May 05 '12 at 09:44

Pranay Rana · Accepted Answer · 2012-05-05T09:43:16.253

10

EDIT

This will remove all rows that which each of it's columns contain either nothing or white space:

dataTable = dataTable.Rows.Cast<DataRow>().
    Where(row => !row.ItemArray.All(field => field is System.DBNull ||   
          string.Compare((field as string).Trim(), string.Empty) ==
                                                      0)).CopyToDataTable();

Pre

one way to achive is to check for empty value

DataView dv = null;
dv = new DataView();

{
    dv.Table = myDatatable;
    dv.AllowDelete = true;
    dv.AllowEdit = true;
    dv.AllowNew = true;
    dv.RowFilter = "myField = ' '";
}

edited May 05 '12 at 09:43

answered Jun 21 '10 at 13:15

Pranay Rana

175,020
35
237
263

you name of column of your table – Pranay Rana Jun 21 '10 at 13:47
How can i do for more columns? – Penguen Jun 21 '10 at 13:56
use and clause or "or" clause in rowfilter – Pranay Rana Jun 22 '10 at 04:39

score 3 · Answer 2 · edited Apr 10 '18 at 12:56

3

Try This

for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
    if (dt.Rows[i][0].ToString() == String.Empty )
    {
        dt.Rows.RemoveAt(i);
    }
}

edited Apr 10 '18 at 12:56

Matthias Seifert

2,033
3
29
41

answered Apr 10 '18 at 11:44

Ninja

338
4
12

score 2 · Answer 3 · answered Dec 03 '20 at 02:43

2

This also worked for me.

 DataTable dt = table.Rows.Cast<DataRow>().Where(r => string.Join("",  r.ItemArray).Trim() != string.Empty).CopyToDataTable();

answered Dec 03 '20 at 02:43

Reza Iranpour

181
1
5

How can i remove empty rows from DataTable from Excel?

3 Answers3