导入非常大的.csv以列出数组，然后将其复制到DataTable

Question

我正在尝试导入一个大的CSV文件，在该文件中，我将输入CSV文件的每一行倾倒到数组(vector(中，这是数字长的。我提取了一些代码将列表复制到数据表中，但是，我不确定是否需要iList(IseNumerable？(。我也没有研究T是什么。

我的直觉是，我可以转到其他一些代码，我必须从二维数组x(，(加载带有行和列数据的数据表，但是由于某些原因，我认为可能有一种快速的方法简单地.ADD(x(，即将整个行向量添加到数据表中以保持速度。您不想循环通过列(？(

以下是将打开任何.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和表单(如果要打印(。然后，您可以在下面使用TEH代码来完成您需要做的事情。

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并从那里工作

Answer 2

     Imports System.IO
Module Module3
    Public Function _simpleCSV2tbl(CSVfile As String) As DataTable
        Dim watch As Stopwatch = Stopwatch.StartNew()
        watch.Start()
        'A,B,C,D,E
        '00001,4,1,2,3560
        '00002,4,12,1,2000
        '00003,1,4,2,4500
        '00004,4,12,1,2538.63
        '00005,1,1,2,3400
        '00006,2,5,2,2996.48
        Dim dTable As New DataTable(CSVfile)
        Using reader As New StreamReader(CSVfile)
            Dim CSV1stLine As String = reader.ReadLine
            Dim getCols = (From s In CSV1stLine.Split(",") Select s).ToList()
            Dim setTblColumns = (From c In getCols Select dTable.Columns.Add(c, GetType(String))).ToList
            Dim ReadToEnd As String = reader.ReadToEnd()
            Dim getRows = (From s In ReadToEnd.Split(vbLf) Select s).ToArray
            _setTblRows(getRows, dTable)
            Console.WriteLine(String.Format("Elapsed: {0}", Format(watch.Elapsed.TotalMilliseconds, "F"), dTable.Rows.Count))
            reader.Close()
            reader.Dispose()
        End Using
        _ShowTbl(dTable, 10)
    End Function
    Public Function _setTblRows(getRows As String(), dTable As DataTable) As IEnumerable
        _setTblRows = getRows.Select(Function(r) dTable.LoadDataRow(r.Split(","), False)).ToArray()
    End Function
    Public Sub _ShowTbl(ByVal dTable As DataTable, Optional ByVal rPad As Short = 0)
        If dTable.TableName = Nothing Then dTable.TableName = "NoName"
        If rPad = 0 Then
            Console.WriteLine(String.Format("->Unformatted Table: {0}, Count={1}", dTable.TableName, dTable.Rows.Count))
        Else
            Console.WriteLine(String.Format("->Formatted Table: {0}, Count={1}", dTable.TableName, dTable.Rows.Count))
        End If
        _ShowTblColumns(dTable.Columns, rPad)
        _ShowTblRows(dTable.Rows, rPad)
    End Sub
    Public Function _ShowTblColumns(ByVal TblColumns As DataColumnCollection, Optional ByVal rPad As Short = 0)
        Dim getTblColumns = (From c As DataColumn In TblColumns Select c.ColumnName).ToList()
        Console.WriteLine(String.Join(",", getTblColumns.Select(Function(s) String.Format(s.PadLeft(rPad, vbNullChar)).ToString).ToArray))
    End Function
    Public Function _ShowTblRow(ByVal Row As DataRow, Optional ByVal rPad As Short = 0)
        Dim getRowFields = (From r In Row.ItemArray Select r).ToList
        Console.WriteLine(String.Join(",", getRowFields.Select(Function(s) String.Format(s.PadLeft(rPad, vbNullChar)).ToString).ToArray))
    End Function
    Public Function _ShowTblRows(ByVal Rows As DataRowCollection, Optional ByVal rPad As Short = 0)
        Dim rCount As Integer
        For Each row As DataRow In Rows
            _ShowTblRow(row, rPad)
            rCount += 1
            If rCount Mod 20 = 0 Then
                If NewEscape(String.Format(" {0} out of {1}", rCount.ToString, (Rows.Count).ToString)) Then Exit Function
            End If
        Next row
        Console.WriteLine()
    End Function
    Public Function _NewEscape(ByVal Message As String) As Boolean
        Console.Write("{0} / Press any key to continue or Esc to quit {1}", Message, vbCrLf)
        Dim rChar As Char = Console.ReadKey(True).KeyChar
        Dim rEscape As Boolean
        If rChar = Chr(27) Then rEscape = True
        Return rEscape
    End Function
End Module