Sorting Array By Date

[Minko]

Hello,

I am trying to place data from a text file into an array and then sort it by date and finally display it in a text box.

The part I am stuck on is sorting the data by date. As I do not want to confuse anyone trying to help me I will include an example of what I am trying to do.

Here is the how the text apears in the text file:

02/07/2010,18
29/06/2010,282
23/06/2010,302
22/06/2010,121
24/06/2010,282
25/06/2010,96
28/07/2010,113
30/06/2010,232
01/07/2010,51

And this how I want the text to appear after it has been sorted and placed in the textbox:

    22/06/2010 - 121
    23/06/2010 - 302
    24/06/2010 - 282
    25/06/2010 - 96
    29/06/2010 - 282
    30/06/2010 - 232
    01/07/2010 - 51
    02/07/2010 - 18
    28/07/2010 - 113

I have managed to place the text into an array but I just cannot figure out how to sort it.

Any help will be greatly appreciated.

Many thanks for taking the time to read this post.

Kindest Regards,

Minko

[TnTinMN]

Hi Minko,

You did not show how you defined your array so I had to guess. If you have a one dimensional array you can use the Array.Sort method. I defined a class to hold you data and made an array of those data items. Since the array holds a user defined class, it is necessary to define a way to compare them. The comparer is defined in the class. By implementing the Icomparable interface, it is possible to define a default comparer. It is also possible to specify a specific compare to use.

Public Class Form1

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      'create an array with 4 items
      Dim myArray(0 To 3) As data
      'create something to work with
      myArray(0) = New data(#3/1/2011#, 13)
      myArray(1) = New data(#2/1/2003#, 15)
      myArray(2) = New data(#3/1/2001#, 1000)
      myArray(4) = New data(#3/1/2001#, 1005)

      Array.Sort(myArray) 'use default comparer (ascending) defined in class
         'or
      Array.Sort(myArray, AddressOf data.Sort_Descending) 'specify a sort order

   End Sub

   Private Class data
      Implements IComparable(Of data)
      Public dt As Date
      Public data As Int32
      Public Sub New(ByVal dt As Date, ByVal data As Int32)
         Me.dt = dt.Date 'force date component only just in case datetime value given
         Me.data = data
      End Sub

      'default comparer ascending on date
      Public Function CompareTo(ByVal other As data) As Integer Implements System.IComparable(Of data).CompareTo
         If Me.dt = other.dt Then
            'sort ascending on data
            Return Me.data.CompareTo(other.data)
         Else
            Return Me.dt.CompareTo(other.dt) 'ascending order
         End If
      End Function

      Public Shared Function Sort_Ascending(ByVal x As data, ByVal y As data) As Int32
         If x.dt = y.dt Then
            'sort ascending on data
            Return x.data.CompareTo(y.data)
         Else
            Return x.dt.CompareTo(y.dt) 'ascending order
         End If
      End Function

      Public Shared Function Sort_Descending(ByVal x As data, ByVal y As data) As Int32
         If x.dt = y.dt Then
            'sort ascending on data
            Return x.data.CompareTo(y.data)
         Else
            Return y.dt.CompareTo(x.dt) 'descending order
         End If
      End Function

   End Class
End Class

[TnTinMN]

I just realized that I had this line in the code above: myArray(4) = New data(#3/1/2001#, 1005)

It should have been: myArray(3) = New data(#3/1/2001#, 1005)

[Reverend Jim]

I'm a big fan of letting the computer do as much as it can for you. Generally, the less code I have to write, the fewer chances I have to introduce a bug. In that case:

Dim mylist As New SortedList(Of Date, String)

For Each line As String In System.IO.File.ReadAllLines("d:\temp\test.txt")
    Dim flds() As String = line.Split(",")
    mylist.Add(CDate(flds(0)), flds(1))
Next

converts the values from string to Date which can be automatically kept in sorted order by the SortedList class. Once all of the values have been read in you can copy them, in sorted order, to whatever is appropriate - textbox, database, file, etc. Note that this code depends on the system short date format being set to dd/mm/yyyy.

@TnTinMN - I am aware that reading the file in one fell swoop may not be appropriate for exceedingly large files (I can't see this really being a problem - who stores data like the above in multi-gig text files), but for the sake of illustration I think it makes the sample code clearer by focusing on the technique rather than adding exttaneous file i/o interface code.

[Minko]

Hello,

First of all a big thank you to both of you for trying to help me with this.

I am trying to get both methods to work just for the experiance as I want to try and learn as much as I can.

However

Could you please explain to me how to put the data into a textbox because I keep getting a error saying value of type '' cannot be converted to string.

If you could help me with putting the data in a textbox I would be most grateful

Thanks in advance,

Minko.

[Reverend Jim]

        Dim mylist As New SortedList(Of Date, String)

        For Each line As String In System.IO.File.ReadAllLines("d:\temp\test.txt")
            Dim flds() As String = line.Split(",")
            mylist.Add(CDate(flds(0)), flds(1))
        Next

        For Each key As Date In mylist.Keys
            TextBox1.AppendText(key.ToString("dd/MM/yyyy") & vbTab & mylist(key) & vbCrLf)
        Next

You have to reformat the date for display as shown. If you just use ToString with no arguments you will get " 00:00:00" added to the date.

[Minko]

Hello again.

I forgot to mention at the top of the text file is the title for example:

Title
03/06/2010,106
16/06/2010,118
30/07/2010,130
14/07/2010,205
17/06/2010,133

How would I go about placing the title into a label first, then sorting and placing all of the other information into the textbox.

Sorry to keep asking questions, but reading and sorting from text files is something I am finding hard to figure out.

Kindest Regards,

Minko

[Reverend Jim]

All you have to do is add a flag so that the first line gets special processing.

        Dim mylist As New SortedList(Of Date, String)
        Dim firstLine As Boolean = True

        For Each line As String In System.IO.File.ReadAllLines("d:\temp\test.txt")
            Dim flds() As String = line.Split(",")
            If firstLine Then
                Label1.Text = line
                firstLine = False
            Else
                mylist.Add(CDate(flds(0)), flds(1))
            End If
        Next

        For Each key As Date In mylist.Keys
            TextBox1.AppendText(key.ToString("dd/MM/yyyy") & vbTab & mylist(key) & vbCrLf)
        Next

[TnTinMN]

How would I go about placing the title into a label first, then sorting and placing all of the other information into the textbox. Sorry to keep asking questions, but reading and sorting from text files is something I am finding hard to figure out.

Hi Minko,

I can sympathize with your problems with text files. I personally prefer to use a binary file as you don't need to go through all the conversions and the loss of floating point accuracy.

But that is not what you need today, so here is a revised version of my example that will process a text file. Most of the code in the subroutine to read the text file is error checking and handling. I added this just to give you an idea how to accomplish some of that. The checking is not exhaustive, but it covers most of the problems that might be encountered. I also added a short function to the Data class to format it as a string.

Public Class Form1

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

      'ignore the next line, I need this to test for your date format. 
      'You should delete it
      Threading.Thread.CurrentThread.CurrentCulture = New Globalization.CultureInfo("en-GB")

      Dim myArray() As Data = GetDataFromFile("data.txt", Label1.Text)
      If myArray IsNot Nothing Then 'sucessfully read file
         Array.Sort(myArray)
         TextBox1.Text = ""
         For i As Int32 = 0 To UBound(myArray)
            TextBox1.Text &= myArray(i).FormattedString & vbCrLf
         Next
         TextBox1.SelectionStart = 0 'move cursor to start of text
      Else
         TextBox1.Text = "file not read"
      End If
   End Sub

   Private Function GetDataFromFile(ByVal fn As String, ByRef fileheader As String) As Data()
      'Note the ByRef fileheader, this means that this subroutine can directly
      'change the value of the variable passed from the calling routine

      fileheader = Nothing 'initialize fileheader, the first line in file to be read

      'verify file exists
      If Not IO.File.Exists(fn) Then
         MsgBox(fn & " could not be found")
         Return Nothing
      End If

      Dim fs As IO.FileStream
      Try 'to open the file
         fs = New IO.FileStream(fn, IO.FileMode.Open, IO.FileAccess.Read)
      Catch ex As Exception
         'file may be locked 
         MsgBox(ex.Message)
         Return Nothing
      End Try

      Dim sr As New IO.StreamReader(fs) 'establish a reader for the file stream

      If sr.Peek = -1 Then
         'empty file
         sr.Close()
         Return Nothing
      Else
         '1st line in file is a label
         fileheader = sr.ReadLine

         Dim returneddata(0 To 99) As Data 'initially allow for 100 items
         Dim numitems As Int32 = 0 'counter to keep track of number of items read

         Dim line As String 'temp var to hold the read line
         Dim items() As String 'will be used to get substrings of parsed line

         'sr.Peek returns -1 if the end of the file has been reached
         Do While sr.Peek <> -1
            'expect line to contain:"Date,Int32" 
            line = sr.ReadLine
            line = line.Trim() 'trim it to remove extra spaces and start and end
            If line.Length = 0 Then Continue Do 'blank line, just skip it
      'Note:  If using .Net 4.0 or above, the above 2 lines above could be replaced by
      '       If String.IsNullOrWhiteSpace(line) Then Continue Do

            items = line.Split(","c)
            If items.Count <> 2 Then 'should only return 2 items
               'malformed data
               'you could throw an error here and exit the loop
               'MsgBox("Error in " & fn & " line " & (numitems + 2%).ToString & _
               '       vbCrLf & line)
               'Exit Do

               'I decided to see if the first two values are valid

            End If

            Try
               returneddata(numitems) = New Data(CType(items(0), DateTime).Date, _
                                                 CType(items(1), Int32))
               If numitems = UBound(returneddata) Then
                  'the array has reached its limit
                  'need to expand it, give it an additional 100 spaces
                  Array.Resize(returneddata, numitems + 100)
               End If
               numitems += 1 'increment the number of read items

            Catch ex As InvalidCastException
               'there was an error trying to convert the string
               'represntations to the datatypes, so could throw an err
               'for now just skip the line

               'ignoring an error is a decision you should not make lightly
               'you have to understand the consequence of continuing on and
               'how it may affect your program further on.

            Catch ex2 As Exception 'unxpected error
                MsgBox(ex2.Message)
                sr.Close()
                Return Nothing
            End Try
         Loop

         'adjust the size of the array to match number of items read
         Array.Resize(returneddata, numitems)
         Return returneddata
      End If
   End Function

   Private Class Data
      Implements IComparable(Of data)
      Public dt As DateTime
      Public data As Int32
      Public Sub New(ByVal dt As DateTime, ByVal data As Int32)
         Me.dt = dt.Date 'force date component only just in case time value given
         Me.data = data
      End Sub

      'default comparer ascending on date
      Public Function CompareTo(ByVal other As data) As Integer Implements System.IComparable(Of data).CompareTo
         If Me.dt = other.dt Then
            'sort ascending on data
            Return Me.data.CompareTo(other.data)
         Else
            'sort on date
            Return Me.dt.CompareTo(other.dt) 'ascending order
         End If
      End Function

      Public Shared Function Sort_Ascending(ByVal x As data, ByVal y As data) As Int32
         If x.dt = y.dt Then
            'sort ascending on data
            Return x.data.CompareTo(y.data)
         Else
            'sort on date
            Return x.dt.CompareTo(y.dt) 'ascending order
         End If
      End Function

      Public Shared Function Sort_Descending(ByVal x As data, ByVal y As data) As Int32
         If x.dt = y.dt Then
            'sort ascending on data
            Return x.data.CompareTo(y.data)
         Else
            'sort on date
            Return y.dt.CompareTo(x.dt) 'descending order
         End If
      End Function

      ''' <summary>helper function to format data into "dd/MM/yyyy - ####" </summary>
      Public Function FormattedString() As String
         Return Me.dt.ToString("dd/MM/yyyy") & " - " & Me.data.ToString
      End Function
   End Class
End Class

[Reverend Jim]

@TnTinMN - I think you are overcomplicating things. Also, I don't see any floating point values so loss of accuracy isn't a concern.

[Minko]

Hello,

Once again a big thanks to both of you.

@Reverand Jim

How would I go about picking out the top 5 entrys but going by the number next to them for example:

03/07/2010,106
16/06/2010,118
14/07/2010,205
17/07/2010,133
30/06/2010,130

I need to copy these and place them in labels. Going from the top label being the highest.
for example

Label 1 = 14/07/2010,205

Label 2 = 17/07/2010,133

Label 3 = 30/06/2010,130

Label 4 = 16/06/2010,118

Label 5 = 03/07/2010,106

I then need to get an average based upon these numbers and place it in a label.

Just one quick question how can I get data out of mylist from a specified position.

For example if I wanted to get the second to last entry from mylist?

If you could help me with this I would be very grateful. Many thanks for all the help so far it is very appreciated.

Kindest Regards,

Minko

[Reverend Jim]

To get a particular slot you can use the Keys collection. mylist.Keys(3) gets the key in the fourth (zero-relative) position and the value at that position is mylist(mylist.Keys(3)). If you want to sort on different fields then you will have to do it via code. Unless you want to get a little fancier. If so you can create what is called a detached recordset. The following code shows you how to do that. Along with the "Imports ADODB" line, you will also have to add a reference to adodb. To do that, go to the Project menu and select "Add Reference". You will find adodb under the .NET tab (you may have to sort on component name first).

Imports ADODB

Public Class Form1

    Private mydata As ADODB.Recordset

    Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        mydata.Close()
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        'Create a recordset object. Cursors can be server side or client side.
        'Because the recordset will not connect to a source (it is detached) we
        'have to specify a client side cursor (and no active connection).

        mydata = New ADODB.Recordset
        mydata.CursorLocation = CursorLocationEnum.adUseClient
        mydata.LockType = LockTypeEnum.adLockBatchOptimistic
        mydata.CursorType = CursorTypeEnum.adOpenStatic
        mydata.ActiveConnection = Nothing

        'Add one field for each field of data in the text file

        mydata.Fields.Append("fld1", DataTypeEnum.adDate)
        mydata.Fields.Append("fld2", DataTypeEnum.adInteger)

        mydata.Open()

        Dim first As Boolean = True

        For Each line As String In System.IO.File.ReadAllLines("d:\temp\test.txt")

            'split the input line inito two fields at the comma separator

            Dim flds() As String = line.Split(",")

            If first Then   'first line is the title
                Label1.Text = line
                first = False
            Else    'line of data - add a new record
                mydata.AddNew()
                mydata.Fields("fld1").Value = flds(0)
                mydata.Fields("fld2").Value = flds(1)
                mydata.Update()
            End If
        Next

    End Sub

    Private Sub btnByDate_Click(sender As System.Object, e As System.EventArgs) Handles btnByDate.Click

        Dim thedate As Date     'from mydata("fld1") which is the same as mydata(0)
        Dim thedata As Integer  'from mydata("fld2") which is the same as mydata(1)

        mydata.Sort = "fld1"    'sort ascending (could also have said "fld1 ASC"

        TextBox1.Text = ""

        'start at the first record and step through until the end

        mydata.MoveFirst()

        Do Until mydata.EOF
            thedate = mydata("fld1").Value
            thedata = mydata("fld2").Value
            TextBox1.AppendText(thedate.ToString("dd/MM/yyyy") & vbTab & thedata & vbCrLf)
            mydata.MoveNext()
        Loop

    End Sub

    Private Sub btnByValue_Click(sender As System.Object, e As System.EventArgs) Handles btnByValue.Click

        Dim thedate As Date
        Dim thedata As Integer

        mydata.Sort = "fld2 DESC"   'sort descending (largest to smallest)

        TextBox1.Text = ""

        mydata.MoveFirst()

        Do Until mydata.EOF
            thedate = mydata("fld1").Value
            thedata = mydata("fld2").Value
            TextBox1.AppendText(thedate.ToString("dd/MM/yyyy") & vbTab & thedata & vbCrLf)
            mydata.MoveNext()
        Loop

    End Sub

End Class

[Reverend Jim]

If you want to get the second to last entry you could do

mydata.MoveLast()
mydata.MovePrevious()

Assuming you have at least two records, mydata.BOF will be false and you will be pointing at the second to last record.

[Minko]

Hello, Many thanks for all of your help so far it is very appreciated.

I want to create a way for the client to enter there own values into text file.
I want it so that they can pick a date using the calendar function and type the qauntity in a text box then hit submit and the data is entered and then when they click a save button all of the information entered is written back into the text file.

Also if the client entered a date which is already in the text file the client is warned and given a choice whether to cancel or over-write the existing value.

I know this is asking a lot but I would be very grateful if you could help me with this.

Kindest Regards,

Minko

[Minko]

Hello,

I managed to solve the last question I asked so I am going to mark this as solved now.

Just wanted to say a big thanks to Reverend Jim and TnTinMN for all there help.

Kindest Regards,

Minko