locked
looping rows in a datacolumn RRS feed

  • Question

  • I have a table that has 4 columns...

    I want to go through each row of a column and find the blank rows... for example

    Row1:  Has data

    Row2:  Has data

    Row3: Blank

    Row4: has data.......

    so when I find a blank row(cell) in that column, if its not the end of the rows.. I want to move Row4: has data to Row3... then I would check max row in a column and delete all rows that were empty in all 4 columns.

    How can I do this?

    Friday, March 18, 2016 8:39 PM

Answers

  • Hi,

    You could ask for rows where the specific column is null e.g.

    Dim dt As New DataTable
    dt.Columns.Add(New DataColumn With {.ColumnName = "LastName", .DataType = GetType(String)})
    
    dt.Rows.Add(New Object() {})
    dt.Rows.Add(New Object() {"Payne"})
    dt.Rows.Add(New Object() {})
    dt.Rows.Add(New Object() {"Jones"})
    
    Dim NullResultsDataRow As List(Of DataRow) =
        dt.AsEnumerable.Where(Function(row) IsDBNull(row.Item("LastName"))).Select(Function(row) row).ToList

    I used a hard coded data table so it's easy to see things. Let's say we want to remove rows in this case where LastName is null.

    For Each row As DataRow In NullResultsDataRow
        dt.Rows.Remove(row)
    Next

    Need to know indices use this class with the code below it

    Public Class MyData
        Public Property DataRow As DataRow
        Public Property RowIndex As Integer
    End Class

    .

    Dim Results As List(Of MyData) =
        dt.AsEnumerable.Select(Function(row, index) _
            New MyData With {.DataRow = row, .RowIndex = index}) _
        .Where(Function(row) IsDBNull(row.DataRow.Item("LastName"))).ToList
    
    For Each item As MyData In Results
        Console.WriteLine("{0} is null", item.RowIndex)
    Next

    So there you have logic to work through your task. Console.WriteLine displays in the IDE Output Window.

    I should mention, the index on this example allows you to do a move up one row for that column

     


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, March 19, 2016 2:02 AM
  • Be aware that a datatable is meant to be unsorted. 

    To show a datatable sorted you've to use a dataview (which is even inside the datatable as property with the name defaultview).

    Therefore if you want to achieve what you want, simply create a rowfilter on the defaultview and set the sort property

    Something like 

            dt.DefaultView.Sort = "ColumnName2"
            dt.DefaultView.RowFilter = "Columname2 <> """
    


    Success
    Cor

    Saturday, March 19, 2016 8:42 AM

All replies

  • Hi

    You have a Table with columns - how many?

    Do you want to go through each column of the Table and check for blank/null values and if found, move the value up by one row IN THAT COLUMN ALONE? Meaning the relationship between columns is not of use to you? For example:

    Start with

    One        two      three

    Four       ......       six

    Seven    eight     nine

    Ten       eleven   twelve

    Ending with

    One        two       three

    Four       eight       six

    Seven     eleven    nine

    Ten          .......     twelve

    Is that typical of the result you want?

    The rest of your question is not something I can understand.


    Regards Les, Livingston, Scotland

    Friday, March 18, 2016 9:02 PM
  • Hi,

    You could ask for rows where the specific column is null e.g.

    Dim dt As New DataTable
    dt.Columns.Add(New DataColumn With {.ColumnName = "LastName", .DataType = GetType(String)})
    
    dt.Rows.Add(New Object() {})
    dt.Rows.Add(New Object() {"Payne"})
    dt.Rows.Add(New Object() {})
    dt.Rows.Add(New Object() {"Jones"})
    
    Dim NullResultsDataRow As List(Of DataRow) =
        dt.AsEnumerable.Where(Function(row) IsDBNull(row.Item("LastName"))).Select(Function(row) row).ToList

    I used a hard coded data table so it's easy to see things. Let's say we want to remove rows in this case where LastName is null.

    For Each row As DataRow In NullResultsDataRow
        dt.Rows.Remove(row)
    Next

    Need to know indices use this class with the code below it

    Public Class MyData
        Public Property DataRow As DataRow
        Public Property RowIndex As Integer
    End Class

    .

    Dim Results As List(Of MyData) =
        dt.AsEnumerable.Select(Function(row, index) _
            New MyData With {.DataRow = row, .RowIndex = index}) _
        .Where(Function(row) IsDBNull(row.DataRow.Item("LastName"))).ToList
    
    For Each item As MyData In Results
        Console.WriteLine("{0} is null", item.RowIndex)
    Next

    So there you have logic to work through your task. Console.WriteLine displays in the IDE Output Window.

    I should mention, the index on this example allows you to do a move up one row for that column

     


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, March 19, 2016 2:02 AM
  • Yes, that is what I am looking for... 6 columns.. mainly the column data are just lists... with 6 topics... and I store these in a file... Not much data, but I was viewing it and thought, I wonder how, I'd be able to "compress" something like that.   Given the example you have there.. if I had 10 rows... and the last 3 row were blank or null in all four columns, then I would delete those rows...

    But like your example... there could be a blank in one of the rows for a given column.

    Saturday, March 19, 2016 3:24 AM
  • Thanks Karen.. interesting concepts there... but if in your example, I would want payne moved to the first row and jones moved to the second row

    Saturday, March 19, 2016 3:35 AM
  • Be aware that a datatable is meant to be unsorted. 

    To show a datatable sorted you've to use a dataview (which is even inside the datatable as property with the name defaultview).

    Therefore if you want to achieve what you want, simply create a rowfilter on the defaultview and set the sort property

    Something like 

            dt.DefaultView.Sort = "ColumnName2"
            dt.DefaultView.RowFilter = "Columname2 <> """
    


    Success
    Cor

    Saturday, March 19, 2016 8:42 AM
  • Thanks Karen.. interesting concepts there... but if in your example, I would want payne moved to the first row and jones moved to the second row

    I understand this, my code samples are in short ideas that might assist you in working out the solution yourself rather than providing an exact solution.  Also, this is why I only used one column rather than two or more. I should had mentioned this in the reply.

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 19, 2016 1:03 PM