Best way to implement a big lookup table in VB

Havooma 156 Reputation points
2023-04-08T14:54:35.6966667+00:00

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!

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,594 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Jiachen Li-MSFT 26,916 Reputation points Microsoft Vendor
    2023-04-10T06:43:12.5166667+00:00

    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.

    0 comments No comments