How to read a huge txt file to a ObservableCollection

Question

I have an excel file which has more than 200,000 row. It takes more than one minute if I use OleDbConnection to read it into a datatable or ObservableCollection and then display it in datagrid. I change the xlsx to txt which is tab-delimited. I found it was very fast to put the whole txt file in the datatable. It takes less than 10s. I want to know how to do that if I put the whole txt to the ObservableCollection directly. It can't be done line by line since it will take more than one minute again.

'Define Product property    
     Public Class Product
                    Public Property Model As String
                    Public Property Opt As String
                    Public Property Description As String
                    Public Property Price As String               
     End Class

'define Products as ObservableCollection       
 Dim Products As New ObservableCollection(Of Product)()

'Read txt file to datatable
Private Sub LoadBound(ByVal fName As String)
Dim textLine As String = String.Empty
Dim splitLine As String()

CSVdata.Columns.AddRange({New DataColumn("Model"),
                          New DataColumn("Opt"),
                          New DataColumn("Description"),
                          New DataColumn("Price")})
                    CSVdata.Rows.Clear()

                    If System.IO.File.Exists(fName) Then
                        Dim objReader As System.IO.StreamReader = New System.IO.StreamReader(fName)
                        Dim contents = objReader.ReadToEnd()
                        Dim strReader = New System.IO.StringReader(contents)


        Do
            textLine = strReader.ReadLine()
            If textLine.Contains("""") Then
                textLine = textLine.Replace("""", "")

            End If
                            If textLine <> String.Empty Then
                                splitLine = textLine.Split(vbTab)

                                If splitLine(0) <> String.Empty OrElse splitLine(1) <> String.Empty Then
                                    CSVdata.Rows.Add(splitLine)

                                End If
                            End If
                        Loop While strReader.Peek() <> -1
                    End If

 End Sub

Answer 1

Comments and explanations in line.

Public Class Product
    Public Property Model As String
    Public Property Opt As String
    Public Property Description As String
    Public Property Price As String
    'Add a parameterized constructor to your class to make coding easier
    Public Sub New(Mdl As String, O As String, Desc As String, P As String)
        Model = Mdl
        Opt = O
        Description = Desc
        Price = P
    End Sub
End Class

Dim Products As ObservableCollection(Of Product)

Private Sub LoadBound(ByVal fName As String)
    'Build a List(Of Product) from the text file
    Dim lstProducts As New List(Of Product)
    Dim lines = File.ReadAllLines(fName)
    For Each line In lines
        Dim Props = line.Split(CChar(vbTab))
        Dim p As New Product(Props(0), Props(1), Props(2), Props(3))
        lstProducts.Add(p)
    Next
    'The constructor of an ObservableCollection can take a List(Of T)
    Products = New ObservableCollection(Of Product)(lstProducts)
End Sub

EDIT

Public Class Product
    Public Property Model As String
    Public Property Opt As String
    Public Property Description As String
    Private strPrice As String
    Public Property Price As Double
    'Add a parameterized constructor to your class to make coding easier
    Public Sub New(Mdl As String, O As String, Desc As String, P As String)
        Model = Mdl
        Opt = O
        Description = Desc
        Dim ParsedDouble As Double
        Double.TryParse(P, ParsedDouble)
        Price = ParsedDouble
    End Sub
End Class

To change the type of Price

1. Change the type of the Property

2. Adjust Sub New to get a double from the string. If the parse fails price will be 0.

   If you actually want a null (nothing in vb) see https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/nullable-value-types