locked
How to skip blank rows in data table in C# using LINQ RRS feed

  • Question

  • I'm using data table which contains more than 100 columns.

    In my data table most of the rows are blank.

    I don't want to read all the rows, if the rows are blank. then the rows suppose to skip.

    because it takes more time to read the rows and columns even if the blank.

    sample data is below:

    EmpNo     Name      Sal     Sec

    A101        Ajesh            A

    A102        

    A103    

    A104        James     1000

    A105     

    A106        Rubin      2000   ZZ   

    below is my code
    ----------------

    for (int i = 0; i < dt.Rows.Count; i++) { //Need to check the condition for blank //if rows are blank then skip using linq

      string str1 = dt.Rows[i].ToString().Trim();
           string str2 = dt.Rows[i][0].ToString();
           if (string.IsNullOrEmpty(str1) && (str2 != null))
           {
                      break;
           }

    ?????? ==> THE ABOVE CONDITION NOT WORKING for (int j = 0; j < dt.Columns.Count; j++) { //Do some process } }

    Note : Except first columns remaining have to check the blank cells. because first columns has unique and values always will be there.



    • Edited by Gani tpt Friday, January 3, 2020 5:22 AM CONDITION
    Friday, January 3, 2020 4:56 AM

Answers

  • Okay, the line above declaration of HasValue should be the following instead: 

    int j = 1;

    Now it shows A101, A104 and A106.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace DataSkip
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataTable dt = GetData();
    
                for (int i = 0; i < dt.Rows.Count; i++)
                {
    
                    //Need to check the condition for blank
                    //if rows are blank then skip using linq
    
                    //I use the good old Convert.ToString() here because it knows how to handle DBNull, but okay if you use libraries that don't use DBNull to represent empty value when reading data 
                    string str1 = Convert.ToString(dt.Rows[i][0]);
    
                    if (String.IsNullOrEmpty(str1)) continue;
    
    
                    int j = 1;
                    bool HasValue = false;
                    while (!HasValue && (j < dt.Columns.Count))
                    {
                        if (!String.IsNullOrEmpty(Convert.ToString(dt.Rows[i][j]))) HasValue = true;
    
                        j++;
                    }
    
                    if (!HasValue) continue;
    
                    for (j = 0; j < dt.Columns.Count; j++)
                    {
                        //Do some process
                    }
    
                    Console.WriteLine(dt.Rows[i][0]);
                }
    
                Console.ReadKey();
            }
    
            public static DataTable GetData()
            {
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("EmpNo", typeof(string));
                dataTable.Columns.Add("Name", typeof(string));
                dataTable.Columns.Add("Sal", typeof(double));
                dataTable.Columns.Add("Sec", typeof(string));
                dataTable.Rows.Add("A101", "Ajesh", null, "A");
                dataTable.Rows.Add("A102", null, null, null);
                dataTable.Rows.Add("A103", null, null, null);
                dataTable.Rows.Add("A104", "James", 1000, null);
                dataTable.Rows.Add("A105", null, null, null);
                dataTable.Rows.Add("A106", "Rubin", 2000, "ZZ");
    
                return dataTable;
            }
        }
    }
    

    • Marked as answer by Gani tpt Friday, January 3, 2020 12:16 PM
    Friday, January 3, 2020 11:19 AM
    Answerer

All replies

  • I think it would be better for you to add a column "HasData" default to "0", then add a insert/update trigger to set it to 1 when non-null value is set to any of the fields.

    Now in your C# code you can just check this one field instead of "all".

    If it happens that your application logic would allow revert some/all value to null, you may consider to change the "HasData" to computed column of COALESCE(field1, field2...) with "PERSISTED" and check for null of this field instead.

    Always try to do data filtering on SQL Server if possible for performance reason.


    Friday, January 3, 2020 6:00 AM
    Answerer
  • No.. I don't want to use trigger. because, the data table values coming from excel file.

    I want to process if there is no data in the rows except one unique column.

    I think, this we can handle using LINQ easily. But, i have no idea how to handle except columns..

    var dtCheckNull = dtEmp.AsEnumerable().Where(r => r.ItemArray.Any(v => v != null && v != DBNull.Value));

    with the above query i just want to include one more condition. that is, except one column should not be null.

    ???

    Friday, January 3, 2020 6:09 AM
  • Try the following, I just typed it out of my mind because I don't have dev environment here:

    for (int i = 0; i < dt.Rows.Count; i++)
    {
    
         //Need to check the condition for blank
         //if rows are blank then skip using linq
         
         //I use the good old Convert.ToString() here because it knows how to handle DBNull, but okay if you use libraries that don't use DBNull to represent empty value when reading data 
         string str1 = Convert.ToString(dt.Rows[i][0]);
    
         if (String.IsNullOrEmpty(str1)) break;
    
         
         int j = 0;
         bool HasValue = false;
         for ( j < dt.Columns.Count; j++)
         {
                 if (!String.IsNullOrEmpty(Convert.ToString(dt.Rows[i][0]))) HasValue = true;
         }
    
         if (!HasValue) break;
    
         for (int j = 0; j < dt.Columns.Count; j++)
         {
                 //Do some process
         }
    }
    
    

    Friday, January 3, 2020 6:35 AM
  • EDIT AS "Edit" button is missing.

    Try the following, I just typed it out of my mind because I don't have dev environment here:

    for (int i = 0; i < dt.Rows.Count; i++) { //Need to check the condition for blank //if rows are blank then skip using linq //I use the good old Convert.ToString() here because it knows how to handle DBNull, but okay if you use libraries that don't use DBNull to represent empty value when reading data string str1 = Convert.ToString(dt.Rows[i][0]); if (String.IsNullOrEmpty(str1)) continue; int j = 0; bool HasValue = false; while (!HasValue && (j < dt.Columns.Count)) { if (!String.IsNullOrEmpty(Convert.ToString(dt.Rows[i][j]))) HasValue = true;

    j++; } if (!HasValue) continue; for (int j = 0; j < dt.Columns.Count; j++) { //Do some process } }


    EDIT: forgotten to replace "break" wih "continue"

    Friday, January 3, 2020 6:38 AM
    Answerer
  • Hi Gani,

    Thank you for posting here.

    I have an idea, we can filter out the eligible DataRows by judging how many null values in each DataRow.

            static void Main(string[] args)
            {
                DataTable dataTable = GetData();
              
                int columnNum = dataTable.Columns.Count;
                var result = from row in dataTable.AsEnumerable()
                             where GetNullNums(row, columnNum) < columnNum - 1
                             select row;
    
                foreach (var item in result)
                {
                    for (int i = 0; i < columnNum; i++)
                    {
                        Console.Write(item[i]+"\t");
                    }
                    Console.WriteLine();
                }
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }
            static int GetNullNums(DataRow row,int columnNum) 
            {
                int num = 0;
                
                for (int i = 0; i < columnNum; i++)
                {
                    if (row[i] == DBNull.Value)
                    {
                        num++;
                    }
                }
                return num;
            }
    
            public static DataTable GetData()
            {
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("EmpNo", typeof(string));
                dataTable.Columns.Add("Name", typeof(string));
                dataTable.Columns.Add("Sal", typeof(double));
                dataTable.Columns.Add("Sec", typeof(string));
                dataTable.Rows.Add("A101", "Ajesh", null, "A");
                dataTable.Rows.Add("A102", null, null, null);
                dataTable.Rows.Add("A103", null, null, null);
                dataTable.Rows.Add("A104", "James", 1000, null);
                dataTable.Rows.Add("A105", null, null, null);
                dataTable.Rows.Add("A106", "Rubin", 2000, "ZZ");
    
                return dataTable;
            }

    Result:

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 3, 2020 7:05 AM
  • loop always executing...it will not check the blank rows...???

    Note : The important thing is, First column should not blank and that rows has blank then break the loop.

    So we have to check blank all the columns except first column...

    ???


    • Edited by Gani tpt Friday, January 3, 2020 9:11 AM
    Friday, January 3, 2020 8:56 AM
  • The important thing is, First column should not blank and that rows has blank then break the loop.

    So we have to check blank all the columns except first column...

    Friday, January 3, 2020 9:12 AM
  • Check if I got you wrong.

    The above code will skip "do some process" when one of the following condition is met:
    a) The first column is empty (first continue statement)
    b) The above is not true but, any column other than first is non-empty (second continue statement)


    Friday, January 3, 2020 9:23 AM
    Answerer

  • The below example "A102","A013","A105" is blank rows then it should skip the loop. But it will not skip the loop and rather it will do some process.

    a) The first column is empty (first continue statement) ==> Correct
    b) The above is not true but, any column other than first is non-empty (second continue statement) ==> any column other than first is non-empty ==> it should move to the next loop

    See my complete test code.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    
    namespace WinForm
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
              
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                DataTable dt = GetData();
    
                for (int i = 0; i < dt.Rows.Count; i++)
                {
    
                    //Need to check the condition for blank
                    //if rows are blank then skip using linq
    
                    //I use the good old Convert.ToString() here because it knows how to handle DBNull, but okay if you use libraries that don't use DBNull to represent empty value when reading data 
                    string str1 = Convert.ToString(dt.Rows[i][0]);
    
                    if (String.IsNullOrEmpty(str1)) continue;
    
    
                    int j = 0;
                    bool HasValue = false;
                    while (!HasValue && (j < dt.Columns.Count))
                    {
                        if (!String.IsNullOrEmpty(Convert.ToString(dt.Rows[i][j]))) HasValue = true;
                        j++;
                    }
    
                    if (!HasValue) continue;
    
                    for (int j1 = 0; j1 < dt.Columns.Count; j1++)
                    {
                        //Do some process
                        
                    }
                }
            }
    
            public static DataTable GetData()
            {
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("EmpNo", typeof(string));
                dataTable.Columns.Add("Name", typeof(string));
                dataTable.Columns.Add("Sal", typeof(double));
                dataTable.Columns.Add("Sec", typeof(string));
                dataTable.Rows.Add("A101", "Ajesh", null, "A");
                dataTable.Rows.Add("A102", null, null, null);
                dataTable.Rows.Add("A103", null, null, null);
                dataTable.Rows.Add("A104", "James", 1000, null);
                dataTable.Rows.Add("A105", null, null, null);
                dataTable.Rows.Add("A106", "Rubin", 2000, "ZZ");
    
                return dataTable;
            }
        }
    }

    ?????????


    • Edited by Gani tpt Friday, January 3, 2020 10:45 AM
    Friday, January 3, 2020 10:27 AM
  • Okay, the line above declaration of HasValue should be the following instead: 

    int j = 1;

    Now it shows A101, A104 and A106.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace DataSkip
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataTable dt = GetData();
    
                for (int i = 0; i < dt.Rows.Count; i++)
                {
    
                    //Need to check the condition for blank
                    //if rows are blank then skip using linq
    
                    //I use the good old Convert.ToString() here because it knows how to handle DBNull, but okay if you use libraries that don't use DBNull to represent empty value when reading data 
                    string str1 = Convert.ToString(dt.Rows[i][0]);
    
                    if (String.IsNullOrEmpty(str1)) continue;
    
    
                    int j = 1;
                    bool HasValue = false;
                    while (!HasValue && (j < dt.Columns.Count))
                    {
                        if (!String.IsNullOrEmpty(Convert.ToString(dt.Rows[i][j]))) HasValue = true;
    
                        j++;
                    }
    
                    if (!HasValue) continue;
    
                    for (j = 0; j < dt.Columns.Count; j++)
                    {
                        //Do some process
                    }
    
                    Console.WriteLine(dt.Rows[i][0]);
                }
    
                Console.ReadKey();
            }
    
            public static DataTable GetData()
            {
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("EmpNo", typeof(string));
                dataTable.Columns.Add("Name", typeof(string));
                dataTable.Columns.Add("Sal", typeof(double));
                dataTable.Columns.Add("Sec", typeof(string));
                dataTable.Rows.Add("A101", "Ajesh", null, "A");
                dataTable.Rows.Add("A102", null, null, null);
                dataTable.Rows.Add("A103", null, null, null);
                dataTable.Rows.Add("A104", "James", 1000, null);
                dataTable.Rows.Add("A105", null, null, null);
                dataTable.Rows.Add("A106", "Rubin", 2000, "ZZ");
    
                return dataTable;
            }
        }
    }
    

    • Marked as answer by Gani tpt Friday, January 3, 2020 12:16 PM
    Friday, January 3, 2020 11:19 AM
    Answerer
  • Hello,

    The following assumes you the same check as shown e.g. is Name and Sal fields populated, no check for Sec field but you can easily add that.

    A mocked DataTable is created and populated on form load.

    • Button1 does a conventional check, iterating rows with a if statement. If a row is empty show the EmpNo in brackets otherwise show data in the ItemArray of the current DataRow
    • Button2 uses a lambda statement to create an array of DataRow then creates a new DataTable for those rows.
    • Button3 uses the same lambda statement as Button2 but uses the original DataTable to iterate.

    The following requires three standard buttons

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            private void Form1_Load(object sender, EventArgs e)
            {
                Populate1();
            }
    
            private DataTable _dataTable1 = new DataTable();
    
            private void Populate1()
            {
                _dataTable1.Columns.Add(new DataColumn() {ColumnName = "EmpNo", DataType = typeof(string)});
                _dataTable1.Columns.Add(new DataColumn() { ColumnName = "Name", DataType = typeof(string) });
                _dataTable1.Columns.Add(new DataColumn() { ColumnName = "Sal", DataType = typeof(string) });
                _dataTable1.Columns.Add(new DataColumn() { ColumnName = "Sec", DataType = typeof(string) });
    
                _dataTable1.Rows.Add("A101", "", "", "");
                _dataTable1.Rows.Add("A102", "Karen Payne", "2000", "");
                _dataTable1.Rows.Add("A103", "", "", "");
                _dataTable1.Rows.Add("A104", "Rubin", "400", "ZZ");
            }
            /// <summary>
            /// loop through rows, look for empty fields excluding first and last column.
            /// If last column is needed simply add to the if statement
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void button1_Click(object sender, EventArgs e)
            {
                foreach (DataRow dataRow in _dataTable1.Rows)
                {
                    if (string.IsNullOrWhiteSpace(dataRow.Field<string>("Name")) && string.IsNullOrWhiteSpace(dataRow.Field<string>("Sal")))
                    {
                        Console.WriteLine($"[{dataRow.Field<string>("EmpNo")}]");
                    }
                    else
                    {
                        Console.WriteLine(string.Join(",", dataRow.ItemArray));
                    }
                }
            }
            /// <summary>
            /// Create a new DataTable from a lambda statement where rows for
            /// new DataTable must have Name and Sal fields not empty, if Sec field needs checking
            /// add it to the Where predicate.
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void button2_Click(object sender, EventArgs e)
            {
    
                DataRow[] dataRows = _dataTable1
                    .AsEnumerable()
                    .Where(row => !string.IsNullOrWhiteSpace(row.Field<string>("Name")) && !string.IsNullOrWhiteSpace(row.Field<string>("Sal")))
                    .ToArray();
    
                DataTable noEmptyRowsTable = dataRows.CopyToDataTable();
                for (int index = 0; index < noEmptyRowsTable.Rows.Count; index++)
                {
                    Console.WriteLine($"{noEmptyRowsTable.Rows[index].Field<string>("EmpNo")}  {noEmptyRowsTable.Rows[index].Field<string>("Name")}");
                }
    
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                DataRow[] dataRows = _dataTable1
                    .AsEnumerable()
                    .Where(row => !string.IsNullOrWhiteSpace(row.Field<string>("Name")) && !string.IsNullOrWhiteSpace(row.Field<string>("Sal")))
                    .ToArray();
    
                for (int index = 0; index < dataRows.Length; index++)
                {
                    Console.WriteLine($"{dataRows[index].Field<string>("EmpNo")}  {dataRows[index].Field<string>("Name")}");
                }
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 3, 2020 11:20 AM
  • Yes. Correct..You charm and rocking.

    Thanks for all others support and beautiful explanation.

    Friday, January 3, 2020 12:16 PM