Best way to implement a big lookup table in VB

Question

I have a Visual Basic program that needs to use a lookup table to get 5 numbers from a given row number. Problem is, this table of data has 890,000 rows and ia currently in a csv file. I’ve managed to do what I want using VB and reading the whole thing into an array but this is obviously time consuming.
What id like to do is something more clever and quicker and be able to package this table with the software when I publish it. I have looked at Datatable and managed to create one but got no idea on how to get the csv data into it and then ensure is packaged as part of the build. If datatable isn’t sensible then any suggestions on what else I should use would be much appreciated!

Answer 1

Hi, @Havooma ,

You can refer to the following code to read the csv file into the datatable.

Use DataTable's BeginLoadData and EndLoadData methods to implement bulk inserts of data, reducing the overhead of insert operations.

        Dim filePath As String = "test.csv"
        Dim dt As New DataTable()
        Using sr As New StreamReader(filePath)
            Dim headers As String() = sr.ReadLine().Split(",")
            For Each header As String In headers
                dt.Columns.Add(header)
            Next
            While Not sr.EndOfStream
                Dim rows As String() = sr.ReadLine().Split(",")
                Dim dr As DataRow = dt.NewRow()
                For i As Integer = 0 To headers.Length - 1
                    dr(i) = rows(i)
                Next
                dt.Rows.Add(dr)
            End While
        End Using

Best Regards.

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.