Import very large .csv to List array, then copy to DataTable

Question

I am trying to import a large CSV file, where I am dumping each row of the input csv file into an array (vector), which is NumColumns long. I fetched some code to copy a list to a DataTable, however, I am not sure the IList (IsEnumerable?) is needed. I also haven't looked into what T is.

My gut feeling is that I can go to some other code I have to load a DataTable with row and column data from a 2-dimensional array x(,), but for some reason I think there may be a fast way to simply .add(x), i.e. add the entire row vector to the DataTable to keep the speed up. You don't want to loop through columns(?)

Below is the code which will open up any .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

Maybe this code will help? You can use OleDB to convert the CSV to a Database and then put it into a datatable. All you need is a DataGridView and form (If you want to print it). Then you can use teh code below to accomplish what you need to do.

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

However, you could always convert it to an xml and work from there

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