Best way to sort data using two keys.

williepriester
williepriester used Ask the Experts™
on
Hello experts,

I am trying to figure out the best way to read in a large amount of data from a text file and then sort it using two keys. I have found many examples of sorting multidimensional arrays using a single key but not two keys. I have seen suggestions for using lists, tuples and datasets but still nothing on how to sort with two keys.

The text file I read in will typically have about 80 to 90 header records and then anywhere from 2000 to over 1000000 lines of data. I need to get four data items from each line.

Line# is in columns 2-5 and is an integer
Station# is in columns 22-25 and is an integer
Xcoord is in columns 47-55 and is a double
Ycoord is in columns 56-65 and is a double

Sample.txt

Once the data is loaded I need to sort by Line# as the first key and Station# as the second key.

So which is the best way to do this? Multidimension array? Lists? Tuples? Datasets? Datatable?

I did try to do it using a datagridview but it was taking way to long to load the data from a file with 650000 records.

Thanks in advance for any help that I can get.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi williepriester;

Can I assume that lines beginning in column one with an H our headers and lines with R in column one are the lines you are interested in?
williepriesterSenior Acquisition Geophysicist

Author

Commented:
Hi Fernando,

Yes that is correct, the headers have an "H" and the data records will have an "r" or an "S".
Retired
Distinguished Expert 2017
Commented:
Hi williepriester;

Something like the following code should do what you need. Microsoft Documentation  TextFieldParser
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        '' Holds the important fields of the file
        Dim listDataItems As New List(Of DataItem)
        '' Parse the input file
        '' Change Sample.txt to have the correct path and file name.
        Using reader As New FileIO.TextFieldParser("Sample.txt")  
            reader.CommentTokens = New String() {"H"}
            reader.TextFieldType = FileIO.FieldType.FixedWidth
            '' The width of the rows in the file that start with R in column 1
            reader.FieldWidths = {1, 4, 16, 4, 21, 9, 1, 9, -1}

            Dim currentRow As String()
            While Not reader.EndOfData
                currentRow = reader.ReadFields
                Dim di As New DataItem
                di.LineNo = Convert.ToInt32(currentRow(1))
                di.StationNo = Convert.ToInt32(currentRow(3))
                di.Xcoord = Convert.ToDouble(currentRow(5))
                di.Ycoord = Convert.ToDouble(currentRow(7))
                listDataItems.Add(di)
            End While

            '' Sort the data using Linq to Object
            Dim dataSorted = (From row As DataItem In listDataItems
                              Order By row.LineNo, row.StationNo
                              Select row).ToList

            '' Display the test data
            For Each di In dataSorted
                Console.WriteLine("{0}  {1}  {2}  {3}", di.LineNo, di.StationNo, di.Xcoord, di.Ycoord)
            Next

        End Using
    End Sub
End Class

Public Class DataItem
    Public LineNo As Integer
    Public StationNo As Integer
    Public Xcoord As Double
    Public Ycoord As Double
End Class

Open in new window

williepriesterSenior Acquisition Geophysicist

Author

Commented:
Thank you Fernando, this is exactly what I have been looking for.
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Not a problem williepriester, glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial