将 CSV 文件中的数据提取到可搜索数据结构中的最佳方法是什么？

Question

我有一个包含 48 列数据的 csv 文件。 我需要打开这个文件，将其放入数据结构中，然后搜索该数据并将其呈现在 DataRepeater 中。

到目前为止，我已经成功使用 CSVReader 提取数据并将其绑定到 myDataRepeater。然而，我现在正在努力将数据放入表格中，以便我可以过滤结果。我不想使用 SQL 或任何其他数据库。

有人对执行此操作的最佳方法有建议吗？

到目前为止，这正在返回所有记录：

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

代码添加到过滤器结果：

 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

它确实返回了我期望的两行，但是当它绑定到 datarepeater 时，我收到以下错误： DataBinding：“System.Data.DataRow”不包含名称为“PropertyName”的属性。

更正的代码，未应用过滤器：

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

我做到这一点的方法之一是使用结构数组和反射。

首先，在模块中设置结构：CSVFileFields.vb

Imports System.Reflection

Public Module CSVFileFields
 #Region " CSV Fields "
    Public Structure CSVFileItem
        Dim NAME As String
        Dim ADDR1 As String
        Dim ADDR2 As String
        Dim CITY As String
        Dim ST As String
        Dim ZIP As String
        Dim PHONE As String

        Public Function FieldNames() As String()
            Dim rtn() As String = Nothing
            Dim flds() As FieldInfo = Me.GetType.GetFields(BindingFlags.Instance Or BindingFlags.Public)
            If Not flds Is Nothing Then
                ReDim rtn(flds.Length - 1)
                Dim idx As Integer = -1
                For Each fld As FieldInfo In flds
                    idx += 1
                    rtn(idx) = fld.Name
                Next
            End If
            Return rtn
        End Function

        Public Function ToStringArray() As String()
            Dim rtn() As String = Nothing
            Dim flds() As FieldInfo = Me.GetType.GetFields(BindingFlags.Instance Or BindingFlags.Public)
            If Not flds Is Nothing Then
                ReDim rtn(flds.Length - 1)
                Dim idx As Integer = -1
                For Each fld As FieldInfo In flds
                    idx += 1
                    rtn(idx) = fld.GetValue(Me)
                Next
            End If
            Return rtn
        End Function

        Public Shadows Function ToString(ByVal Delimiter As String) As String
            Dim rtn As String = ""
            Dim flds() As FieldInfo = Me.GetType.GetFields(BindingFlags.Instance Or BindingFlags.Public)
            If Not flds Is Nothing Then
                For Each fld As FieldInfo In flds
                    rtn &= fld.GetValue(Me) & Delimiter
                Next
                rtn = rtn.Substring(0, rtn.Length - 1)
            End If
            Return rtn
        End Function
    End Structure

#End Region
终端模块

接下来我们将从我们刚刚制作的结构中制作我们自己的集合。这将使我们的结构更容易使用 .Add() .Remove() 等。我们还可以使用 .RemoveAt(Index) 删除单个项目。文件：CSVFileItemCollection.vb

 #Region " CSVFileItem Collection "


公共类 CSVFileItemCollection
    继承System.Collections.CollectionBase

Public Sub Add(ByVal NewCSVFileItem As CSVFileItem)
    Me.List.Add(NewCSVFileItem)
End Sub

Public Sub Remove(ByVal RemoveCSVFileItem As CSVFileItem)
    Me.List.Remove(RemoveCSVFileItem)
End Sub

Default Public Property Item(ByVal index As Integer) As CSVFileItem
    Get
        Return Me.List.Item(index)
    End Get
    Set(ByVal value As CSVFileItem)
        Me.List.Item(index) = value
    End Set
End Property

Public Shadows Sub Clear()
    MyBase.Clear()
End Sub

Public Shadows Sub RemoveAt(ByVal index As Integer)
    Remove(Item(index))
End Sub

结束类
#End Region


接下来，您需要您的类来处理反射导入： CSVFile.vb


Imports System.Reflection
Imports System.IO
Imports Microsoft.VisualBasic.PowerPacks


 Public Class CSVFile
 #Region“私有变量”
    私有 _CSVFile 作为 CSVFileItem，_Delimiter 作为字符串，_Items 作为新的 CSVFileItemCollection
 #End Region

 #Region“私有方法”
    Private Sub FromString(ByVal 行作为字符串，ByVal 分隔符作为字符串)
        Dim CSVFileElements() As String = Line.Split(Delimiter)
        如果不是 CSVFileElements 那么什么都不是
            Dim fldInfo() As FieldInfo = _CSVFile.GetType.GetFields(BindingFlags.Instance 或 BindingFlags.Public)
            如果不是 fldInfo 那么什么都不是
                Dim itm As System.ValueType = CType(_CSVFile, System.ValueType)
                对于 fldIdx 作为整数 = 0 到 CSVFileElements.Length - 1
                    fldInfo(fldIdx).SetValue(itm, CSVFileElements(fldIdx).Replace(Chr(34), ""))
                下一个
                _CSVFile = itm
            别的
                将 itms 调暗为整数 = 0
                如果不是 fldInfo 那么什么都不是
                    itms = fldInfo.长度
                结束如果
                抛出新异常（“行定义无效。”）
            结束如果
        别的
            将 itms 调暗为整数 = 0
            如果不是 CSVFileElements 那么什么都不是
                itms = CSVFileElements.长度
            结束如果
            抛出新异常（“行定义无效。”）
        结束如果
    结束子
 #End Region

 #Region“公共方法”
    公共子新建()
        _CSVFile = 新的 CSVFileItem
    End Sub

Public Sub New(ByVal Line As String, ByVal Delimiter As String)
    _CSVFile = New CSVFileItem
    _Delimiter = Delimiter
    FromString(Line, Delimiter)
End Sub

Public Sub New(ByVal Filename As String)
    LoadFile(Filename)
End Sub

Public Sub LoadFile(ByVal Filename As String)
    Dim inFile As StreamReader = File.OpenText(Filename)
    Do While inFile.Peek > 0
        FromString(inFile.ReadLine, ",")
        _Items.Add(_CSVFile)
        _CSVFile = Nothing
    Loop
    inFile.Close()
End Sub

 #End Region
 #Region“公共职能”

    公共函数 ToDataSet(ByVal TableName As String) As DataSet

        Dim dsCSV As DataSet = Nothing

        如果不是 _Items 就什么都不是 AndAlso _Items.Count > 0 然后

            Dim flds() As FieldInfo = _Items.Item(0).GetType.GetFields(BindingFlags.Instance 或 BindingFlags.Public)

            如果不是 flds 那么什么都不是

                dsCSV = 新数据集

                dsCSV.Tables.Add(表名)

                对于每个 fld 作为 flds 中的 FieldInfo

                    '添加列名

                    使用 dsCSV.Tables.Item(TableName)

                        .Columns.Add(fld.Name, fld.FieldType)

                    结束于

                Next
            'Populate Table with Data
            For Each itm As CSVFileItem In _Items
                dsCSV.Tables.Item(TableName).Rows.Add(itm.ToStringArray)
            Next
        End If
    End If
    Return dsCSV
End Function

 #End Region
 #Region “公共属性”

    公共只读属性 Item() As CSVFileItem

        得到

            返回_CSV文件

        结束获取

    结束属性
Public ReadOnly Property Items() As CSVFileItemCollection
    Get
        Return _Items
    End Get
End Property

#End Region

结束课程


好的，稍微解释一下。这个类所做的是首先获取分隔（“，”）文本行并将其拆分为字符串数组。然后它会迭代结构 CSVFileItem 中的每个字段，并根据索引填充该结构变量。您拥有多少物品并不重要。只要声明结构的顺序与加载内容的顺序相同，您就可以有 1 或 1,000 个。例如，您的输入 CSV 应与 CSVFileItem 匹配为“Name,Address1,Address2,City,State,Zip,Phone”。这是通过上面代码中的循环完成的：


Dim fldInfo() As FieldInfo = _CSVFile.GetType.GetFields(BindingFlags.Instance Or BindingFlags.Public)
If Not fldInfo Is Nothing Then
    Dim itm As System.ValueType = CType(_CSVFile, System.ValueType)
    For fldIdx As Integer = 0 To CSVFileElements.Length - 1
        fldInfo(fldIdx).SetValue(itm, CSVFileElements(fldIdx).Replace(Chr(34), ""))
    Next
    _CSVFile = itm
Else
    Dim itms As Integer = 0
    If Not fldInfo Is Nothing Then
        itms = fldInfo.Length
    End If
    Throw New Exception("Invalid line definition.")
End If


为了让事情变得简单，我们不必从主类加载文件，我们只需将文件路径传递给它，这个类将完成所有工作并返回我们的结构的集合。我知道这看起来需要很多设置，但这是值得的，您可以返回并将原始结构更改为任何内容，其余代码仍然可以完美运行！
现在让一切顺利进行。现在您会发现只需几行代码即可轻松实现这一点。文件：frmMain.vb


Public Class Form1


Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Try
        Dim csv As New CSVFile("C:\myfile.csv")
        Dim ds As DataSet = csv.ToDataSet("MyTable")
        If Not ds Is Nothing Then
            'Add Labels
            Dim lblSize As New Size(60, 22), lblY As Integer = 10, lblX As Integer = 10, lblSpacing As Integer = 10
            For Each fldName As String In csv.Items.Item(0).FieldNames
                Dim lbl As New Label
                lbl.AutoSize = True
                lbl.Size = lblSize
                lbl.Location = New Point(lblX, lblY)
                lbl.Name = "lbl" & fldName
                lblX += lbl.Width + lblSpacing
                lbl.DataBindings.Add(New Binding("Text", ds.Tables.Item(0), fldName, True))
                drMain.ItemTemplate.Controls.Add(lbl)
            Next
            drMain.DataSource = ds
            drMain.DataMember = ds.Tables.Item(0).TableName
        End If
        ds = Nothing
        csv = Nothing
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

结束类


这确实有助于一些动态规划。您可以将它们包装在泛型类中并调用任何结构的函数。然后，您将拥有一些可重用的代码，这些代码将使您的程序高效并减少编程时间！
编辑：
添加了将结构集合转储到数据集然后动态填充数据重复器的功能。
希望这有帮助。 （我知道这是大量信息，而且看起来工作量很大，但我向您保证，一旦您将其落实到位，它将真正减少未来项目的编码时间！）

Answer 2

为什么不使用内置的 TextFileParser 将数据获取到 DataTable 中？就像 Paul Clement 在 这个线程中的回答