The best way to extract data from a CSV file into a searchable datastructure?

Question

I have a csv file with 48 columns of data. I need to open this file, place it into a data structure and then search that data and present it in a DataRepeater.

So far I have successfully used CSVReader to extract the data and bind it to myDataRepeater. However I am now struggling to place the data in a table so that I can filter the results. I do not want to use SQL or any other database.

Does anyone have a suggestion on the best way to do this?

So far, this is working in returning all records:

Private Sub BindCsv()
    ' open the file "data.csv" which is a CSV file with headers"
    Dim dirInfo As New DirectoryInfo(Server.MapPath("~/ftp/"))
    Dim fileLocation As String = dirInfo.ToString & "data.txt"
    Using csv As New CsvReader(New StreamReader(fileLocation), True)
        myDataRepeater.DataSource = csv
        myDataRepeater.DataBind()
    End Using
End Sub

Protected Sub myDataRepeater_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs) Handles myDataRepeater.ItemDataBound

    Dim dataItem As String() = DirectCast(e.Item.DataItem, String())

    DirectCast(e.Item.FindControl("lblPropertyName"), ITextControl).Text = dataItem(2).ToString
    DirectCast(e.Item.FindControl("lblPrice"), ITextControl).Text = dataItem(7).ToString

    DirectCast(e.Item.FindControl("lblPricePrefix"), ITextControl).Text = dataItem(6)
    DirectCast(e.Item.FindControl("lblPropertyID"), ITextControl).Text = dataItem(1)
    DirectCast(e.Item.FindControl("lblTYPE"), ITextControl).Text = dataItem(18)
    DirectCast(e.Item.FindControl("lblBedrooms"), ITextControl).Text = dataItem(8)
    DirectCast(e.Item.FindControl("lblShortDescription"), ITextControl).Text = dataItem(37)

    Dim dirInfo As New DirectoryInfo(Server.MapPath("~/ftp/images/"))
    DirectCast(e.Item.FindControl("imgMain"), Image).ImageUrl = dirInfo.ToString & "pBRANCH_" & dataItem(1) & ".jpg"
    DirectCast(e.Item.FindControl("linkMap"), HyperLink).NavigateUrl = "http://www.multimap.com/map/browse.cgi?client=public&db=pc&cidr_client=none&lang=&pc=" & dataItem(5) & "&advanced=&client=public&addr2=&quicksearch=" & dataItem(5) & "&addr3=&addr1="
End Sub

Code add to filter results:

 Try
        Dim csv As New CSVFile(fileLocation)
        Dim ds As DataSet = csv.ToDataSet("MyTable")
        If Not ds Is Nothing Then


            Dim strExpr As String = "Bedrooms >= '3'"
            Dim strSort As String = "PropertyID ASC"

            'Use the Select method to find all rows matching the filter.
            Dim myRows() As DataRow
            'myRows = Dt.Select(strExpr, strSort)
            myRows = csv.ToDataSet("MyTable").Tables("MyTable").Select(strExpr, strSort)
            myDataRepeater.DataSource = myRows
            myDataRepeater.DataBind()
        End If
    Catch ex As Exception

    End Try

Which does return the two rows I am expecting but then when it binds to the datarepeater I get the following error: DataBinding: 'System.Data.DataRow' does not contain a property with the name 'PropertyName'.

Corrected code, filter not being applied:

Public Sub PageLoad(ByVal Sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not Page.IsPostBack Then
        ReadCsv()
        lblSearch.Text = "Lettings Search"
    End If
End Sub

Private Sub ReadCsv()
    Dim dirInfo As New DirectoryInfo(Server.MapPath("~/ftp/"))
    Dim fileLocation As String = dirInfo.ToString & "data.txt"

    Try
        Dim csv As New CSVFile(fileLocation)
        Dim ds As DataSet = csv.ToDataSet("MyTable")
        If Not ds Is Nothing Then
            myDataRepeater.DataSource = ds
            myDataRepeater.DataMember = ds.Tables.Item(0).TableName
            myDataRepeater.DataBind()
        End If
        ds = Nothing
        csv = Nothing
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnSubmit.Click
    Dim rowCount As Integer

    rowCount = QueryCsv()
    pnlSearch.Visible = False
    lblResults.Visible = True
    lblSearch.Text = "Search Results"
    lblResults.Text = "Your search returned " & rowCount.ToString & " results"

    If rowCount > 0 Then
        myDataRepeater.Visible = True
        pnlResults.Visible = True
        btnBack.Visible = True
    End If

End Sub

Protected Function QueryCsv() As Integer

    Dim dirInfo As New DirectoryInfo(Server.MapPath("~/ftp/"))
    Dim fileLocation As String = dirInfo.ToString & "data.txt"
    Dim numberofRows As Integer

    Try
        Dim csv As New CSVFile(fileLocation)
        Dim ds As DataSet = csv.ToDataSet("MyTable")
        If Not ds Is Nothing Then


            Dim strExpr As String = "PropertyID = 'P1005'"
            Dim strSort As String = "PropertyID DESC"

            Try

                ds.Tables.Item(0).DefaultView.RowFilter = strExpr
                ds.Tables.Item(0).DefaultView.Sort = strSort
                myDataRepeater.DataSource = ds.Tables.Item(0).DefaultView

            Catch ex As Exception
            End Try

        End If
    numberofRows = ds.Tables("MyTable").Rows.Count
    Catch ex As Exception

    End Try
    Return numberofRows
End Function

Answer 1

Why not use the built-in TextFileParser to get the data into a DataTable? Something like Paul Clement's answer in this thread