Question Trimming a datagridview based on latest rows

[Arash88]

i'm in VS2008 Studio, i have this datagridview with multiple columns which the last column contains a date and time value.

lot's of rows are pretty the same except by they're date column.

what i wanted to do is to trim the whole datagridview duplicate rows except they're most recent ones based on they're date column.

i have sth like this:
Administrator,192.168.137.221,2,file://C:\WMPub\WMRoot\industrial.wmv , 07.Jul.2014 - 23:11:59
Administrator,192.168.137.221,2,file://C:\WMPub\WMRoot\industrial.wmv , 07.Jul.2014 - 21:11:59
Administrator,192.168.137.221,2,file://C:\WMPub\WMRoot\industrial.wmv , 07.Jul.2014 - 22:11:59

Administrator,192.168.137.221,2,file://C:\WMPub\WMRoot\industrial.wmv , 07.Jul.2014 - 20:11:59
Administrator,192.168.137.221,2,file://C:\WMPub\WMRoot\industrial.wmv , 07.Jul.2014 - 11:11:59
Everyone ,192.168.137.221,2,file://C:\WMPub\WMRoot\industrial.wmv , 07.Jul.2014 - 17:11:59
Everyone ,192.168.137.221,2,file://C:\WMPub\WMRoot\industrial.wmv , 07.Jul.2014 - 14:11:59


the output i want should be like this:



Administrator 192.168.137.221 2 file://C:\WMPub\WMRoot\industrial.wmv 07.Jul.2014 - 23:11:59

Everyone 192.168.137.201 2 file://C:\WMPub\WMRoot\industrial.wmv 07.Jul.2014 - 17:11:59



please consider "," as column seprators! (i don't know how to draw a table here, sorry again)!
i have this code but it throw a nullreferenceexception at the If dtRow("dateColumn") >.... and if i sort my last column and then remove the check inside this code it make a mess: before: i61.tinypic.com/2s1wrdj.jpg and after trimming: i62.tinypic.com/ogwizq.jpg

Public Function RemoveDuplicateRows(ByVal dTable As DataTable, ByVal colName As String)Dim dict As New dictionary(Of String, DataRow)

For Each dtRow As DataRow In dTable.Rows
  Dim key As String = dtRow("column1") + "," + dtRow("column2") ' + etc.
  Dim dictRow As DataRow = Nothing
  If dict.TryGetValue(key, dictRow) Then
    'check and update date
    'you can skip this part, if your data is sorted
    If dtRow("dateColumn") > dictRow("dateColumn") Then
      dictRow("dateColumn") = dtRow("dateColumn")
    End If
  Else
    dict.Add(key, dictRow)
  End If
Next

Return dict.Values.ToArray()

    End Function

and i use this function like this:

DataGridView1.DataSource = RemoveDuplicateRows(DataGridView1.DataSource, "Lastseen")

this is the whole routine:

http://pastebin.com/FrLpJbrK

 

[IanRyder]

Hi,

You can do this by Grouping the Information in the fields that you want and then extracting the Latest DataRow to create a new Data Table. This can then be used to replace the existing DataSource of the DataGridView.

Have a read through this example:-

'Load your data from where ever it is stored
Private myFileLogData As New DataTable '<<In this example this is your Data Table
Private myGroupedLogData As New DataTable
 
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
  'Clone the source Data Tables Schema so that we can create a new table in the same structure
  myGroupedLogData = myFileLogData.Clone
 
  'Create an Enumeration of Groups which is a concatenation of your similar Data.
  'The similar data in this instance I have assumed to be column indexes 1 through 3
  Dim myGroups As IEnumerable(Of IGrouping(Of String, DataRow)) = myFileLogData.Rows.Cast(Of DataRow).GroupBy(Function(x) String.Concat(x.ItemArray.Skip(1).Take(3)))
 
  'Iterate Each Group to get the row you want
  For Each groupElement As IGrouping(Of String, DataRow) In myGroups
    'See the Key if you need to
    MsgBox(groupElement.Key)
 
    'Get the Latest entry in the Group by ordering by the date and taking the first element
    Dim latestGroupEntry As DataRow = groupElement.OrderByDescending(Function(x) x(4)).First
 
    'Add that Row to a new Data Table
    myGroupedLogData.Rows.Add(latestGroupEntry.ItemArray)
  Next
 
  'Show the next Data Table
  DataGridView2.DataSource = myGroupedLogData
End Sub

Hope that helps.

Cheers,

Ian

 

[Arash88]

hi Ian, tnx for this great solution,w0w it's so much easier than i thought! :victorious:
but there is a problem, as you see in my op, i have different ip's that i should preserve'em, your method works fine but it gets the latest entry for just one user! like if i have this lines:

-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial.wmv,10-10-2014
-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial.wmv,10-10-2014
-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial.wmv,10-10-2014
-,AST-VM2003\Administrator,192.168.137.222,2,file:--C:\WMPub\WMRoot\industrial.wmv,12-10-2014
-,AST-VM2003\Administrator,192.168.137.222,2,file:--C:\WMPub\WMRoot\industrial.wmv,12-11-2014

it only return this:

-,AST-VM2003\Administrator,192.168.137.222,2,file:--C:\WMPub\WMRoot\industrial.wmv,12-11-2014

so let me explain more
i have these rows in my csv which i load'em into a datagridview:

-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial2.wmv,11-10-2014
-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial2.wmv,22-10-2014
-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial2.wmv,29-10-2014
-,AST-VM2003\Anonymous,192.168.137.223,3,file:--C:\WMPub\WMRoot\industrial.wmv,21-10-2014
-,AST-VM2003\Anonymous,192.168.137.223,3,file:--C:\WMPub\WMRoot\industrial.wmv,10-10-2014
-,AST-VM2003\Everyone,192.168.137.224,2,file:--C:\WMPub\WMRoot\industrial.wmv,10-10-2014
-,AST-VM2003\Everyone,192.168.137.224,2,file:--C:\WMPub\WMRoot\industrial.wmv,10-11-2014
BC1,AST-VM2003\Everyone,192.168.137.222,2,file:--C:\WMPub\WMRoot\industrial.wmv,12-10-2014
BC1,AST-VM2003\Administrator,192.168.137.222,2,file:--C:\WMPub\WMRoot\industrial.wmv,12-11-2014
.
.
.

so as you can see even that there are lots of similarities, but in overall they're no match to each other, i want to achieve this output out of it:

BC1,AST-VM2003\Administrator,192.168.137.222,2,file:--C:\WMPub\WMRoot\industrial3.wmv,12-11-2014
-,AST-VM2003\Everyone,192.168.137.224,2,file:--C:\WMPub\WMRoot\industrial.wmv,10-11-2014
-,AST-VM2003\Anonymous,192.168.137.223,3,file:--C:\WMPub\WMRoot\industrial.wmv,21-10-2014

i hope i didn't bother you to much, tanks anyway, it's very kind of you

 

[jmcilhinney]

Where did this data come from? If it came from a database like that then it would be easier and more practical to use the original SQL query to do the job.

 

[Arash88]

Where did this data come from? If it came from a database like that then it would be easier and more practical to use the original SQL query to do the job.

hi dear jmcilhinney, thanks for your reply, i appreciate it
no, unfortunately i'm getting those data which i save 'em to a CSV from some where else. because of the situation i'm trying everything to avoid using and installing a SQLServer due to lack of availability of the system resources, but if it's possible to use an accessdb it would be fine with me, but if its not to much trouble please help me to save/sort and restore because i don't know jack 'bout those creepy strings to interact with a SQL!:stupid:
thank you so much

 

[IanRyder]

Hi,

When reading your original post it looks like I missed some columns in the example that I gave so all you need to do is expand the example given to Group by the information by the right columns in your Datatable.

From what I can see, you have 6 columns and you want to Group by the first 5 columns? If so, just change the Lambda expression to say:-

String.Concat(x.ItemArray.Take(5))

If that?s still not quite right then just work out for yourself what you need to Group by and make the appropriate changes.

Cheers,

Ian

 

[Arash88]

Hi,

When reading your original post it looks like I missed some columns in the example that I gave so all you need to do is expand the example given to Group by the information by the right columns in your Datatable.

From what I can see, you have 6 columns and you want to Group by the first 5 columns? If so, just change the Lambda expression to say:-

String.Concat(x.ItemArray.Take(5))

If that?s still not quite right then just work out for yourself what you need to Group by and make the appropriate changes.

Cheers,

Ian

hi dear ian, thanks for your time,i appreciate it
i used .Take(6) and this time the result got worse!
my inputs look like this:

-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial2.wmv,11-10-2014

and here's what happened:
before



after:

 

[IanRyder]

Well looking at those pictures it looks like you should be Grouping on the first 5 columns of the Datatable and not 6?

Cheers,

Ian

[Edit]
Or Skip the first column and Take the next 4?

 

[Herman]

The problem is that your initial approach is wrong. Instead of adding the whole CSV to the DGV and then removing the rows you don't want, you need to select the correct information first and then populate the DGV, ideally through databinding.

For example, assuming this CSV :

-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial2.wmv,11-10-2014
-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial2.wmv,22-10-2014
-,AST-VM2003\Administrator,192.168.137.221,2,file:--C:\WMPub\WMRoot\industrial2.wmv,29-10-2014
-,AST-VM2003\Anonymous,192.168.137.223,3,file:--C:\WMPub\WMRoot\industrial.wmv,21-10-2014
-,AST-VM2003\Anonymous,192.168.137.223,3,file:--C:\WMPub\WMRoot\industrial.wmv,10-10-2014
-,AST-VM2003\Everyone,192.168.137.224,2,file:--C:\WMPub\WMRoot\industrial.wmv,10-10-2014
-,AST-VM2003\Everyone,192.168.137.224,2,file:--C:\WMPub\WMRoot\industrial.wmv,10-11-2014
BC1,AST-VM2003\Everyone,192.168.137.222,2,file:--C:\WMPub\WMRoot\industrial.wmv,12-10-2014
BC1,AST-VM2003\Administrator,192.168.137.222,2,file:--C:\WMPub\WMRoot\industrial.wmv,12-11-2014

And using this code:

Public Class Form1

    Public Class CsvRow
        Public Property SomeField As String
        Public Property HostUser As String
        Public Property IpAddress As String
        Public Property SomeIndex As Integer
        Public Property Filename As String
        Public Property SomeDate As Date

        Public Shared Function Create(ByVal rowData As String) As CsvRow
            Dim output As CsvRow = Nothing
            Dim fields() = rowData.Split(",")

            If fields.Length = 6 Then
                Try
                    output = New CsvRow With {.SomeField = fields(0),
                                              .HostUser = fields(1),
                                              .IpAddress = fields(2),
                                              .SomeIndex = CInt(fields(3)),
                                              .Filename = fields(4),
                                              .SomeDate = Date.ParseExact(fields(5), "dd-MM-yyyy", Nothing)}
                Catch ex As Exception
                End Try
            End If

            Return output
        End Function
    End Class


    Public Function ParseCsv(ByVal filename As String) As List(Of CsvRow)
        Dim output As New List(Of CsvRow)

        For Each row In IO.File.ReadAllLines(filename)
            output.Add(CsvRow.Create(row))
        Next

        Return output
    End Function


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim MyGroupedRows = From row In ParseCsv("C:\Users\SomeUser\Desktop\CSV\input.txt")
                            Order By row.SomeDate Descending
                            Group row By row.SomeField, row.HostUser, row.IpAddress, row.SomeIndex, row.Filename Into Group
                            Select Group.First

        DataGridView1.DataSource = MyGroupedRows.ToList
        DataGridView1.Update()
    End Sub


End Class

And the output:

View attachment 4046