vb.net - 将非常大的 .csv 导入 List 数组，然后复制到 DataTable

Question

我正在尝试导入一个大型 CSV 文件，我将输入的 csv 文件的每一行转储到一个数组(向量)中，该数组的长度为 NumColumns。我获取了一些代码以将列表复制到 DataTable，但是，我不确定是否需要 IList(IsEnumerable？)。我也没有研究过 T 是什么。

我的直觉是，我可以转到其他一些代码，我必须从二维数组 x(,) 中加载具有行和列数据的 DataTable，但出于某种原因，我认为可能有一种快速的方法来只需 .add(x)，即将整个行向量添加到 DataTable 以保持速度。你不想遍历列(？)

下面是将打开任何 .csv 的代码。

Imports System.ComponentModel
Imports System.IO

Public Class Form1
    Dim NumColumns As Integer
    Dim ColumnNames() As String

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim filename As String = Nothing
        With OpenFileDialog1
            .FileName = "*.csv"
            .CheckFileExists = True
            .ShowReadOnly = True
            .Filter = "Comma delimited *.csv|*.csv"
            If .ShowDialog = DialogResult.OK Then
               filename = .FileName
            End If
        End With
        Dim csvreader As New StreamReader(filename)
        Dim inputLine As String = ""
        inputLine = csvreader.ReadLine()
        Dim buff() As String = Split(inputLine, ",")
        NumColumns = UBound(buff)
        ReDim ColumnNames(UBound(buff) + 1)
        For j As Integer = 0 To NumColumns
            ColumnNames(j + 1) = buff(j)
        Next
        inputLine = csvreader.ReadLine()
        Do While inputLine IsNot Nothing
            Dim rowdata = New MyDataArray(NumColumns)
            Dim csvArray() As String = Split(inputLine, ",")
            For i As Integer = 0 To NumColumns
                rowdata.x(i) = csvArray(i)
            Next
            MyDataArray.DataArray.Add(rowdata)
            inputLine = csvreader.ReadLine()
        Loop
        Dim dgv As New DataGridView
        dgv.DataSource = ToDataTable(MyDataArray.DataArray)
        dgv.Width = 1000
        dgv.Height = 1000
        Me.Controls.Add(dgv)
    End Sub

    Public Shared Function ToDataTable(Of T)(data As IList(Of T)) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim dt As New DataTable()
        For i As Integer = 0 To properties.Count - 1
            Dim [property] As PropertyDescriptor = properties(i)
            dt.Columns.Add([property].Name, [property].PropertyType)
        Next
        Dim values As Object() = New Object(properties.Count - 1) {}
        For Each item As T In data
            For i As Integer = 0 To values.Length - 1
                values(i) = properties(i).GetValue(item)
            Next
            dt.Rows.Add(values(1))
        Next
        Return dt
    End Function
End Class

Public Class MyDataArray
    Public Shared DataArray As New List(Of MyDataArray)()
    Public Property x() As Object
    Sub New(ByVal cols As Integer)
        ReDim x(cols)
    End Sub
End Class

Answer 1

也许这段代码会有帮助？您可以使用 OleDB 将 CSV 转换为数据库，然后将其放入数据表中。您只需要一个 DataGridView 和表单(如果您想打印它)。然后您可以使用下面的代码来完成您需要做的事情。

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim file As String = "test.txt"
        Dim path As String = "C:\Test\"
        Dim ds As New DataSet
        Try
            If IO.File.Exists(IO.Path.Combine(path, file)) Then
                Dim ConStr As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
                Dim conn As New OleDb.OleDbConnection(ConStr)
                Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
                file, conn)
                da.Fill(ds, "TextFile")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
        DataGridView1.DataSource = ds.Tables(0)
    End Sub
End Class

但是，您始终可以将其转换为 xml 并从那里开始工作