CSV file to datagridview using list

Question

This is similar to: https://social.msdn.microsoft.com/Forums/en-US/7b0e28b4-c1ef-49d6-8f46-11b379428052/import-from-csv-file-to-two-dimensional-array?forum=vbgeneral

but the code that Cor Ligthert suggests doesn't work even though its exactly what I need.

The code is:

Public Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim FILE_NAME As String = "C:\Users\Admin\Documents\test.csv"

    Dim OutlookLines As New List(Of OutlookLine)
    Dim sr As New IO.StreamReader(FILE_NAME)
    Do Until sr.EndOfStream
        OutlookLines.Add(New OutlookLine With {.line = sr.ReadLine})
    Loop
    DataGridView1.DataSource = OutlookLines

End Sub


Private Class OutlookLine
    Private theLine As String
    Public Property line() As String
        Get
            Return theLine
        End Get
        Set(ByVal value As String)
            theLine = value
        End Set
    End Property
End Class

End Class

I try putting in:

.Split(","c)

after sr.ReadLine but it says "Value of type 1-D array of String cannot be converted to String"

The code above works fine but each row of the csv is scrunched into one column (obviously because I am not splitting it at the ",").

csv data:

1,2,3,4,5
6,7,8,9,0

Microsoft.VisualBasic.FileIO.TextFieldParser - because String.Split is not how you parse csv. — Sam Axe
– Sam Axe, Commented Mar 25, 2015 at 22:11
I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Mar 25, 2015 at 23:07

Craig Johnson · Accepted Answer · 2015-03-27 02:48:33Z

1

See if this works for you:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    DataGridView1.DataSource = CSVToDataTable("C:\Users\Admin\Documents\test.csv", True)
End Sub

Private Function CSVToDataTable(filePath As String, Optional hasHeaderRow As Boolean = False) As DataTable
    Dim rows = IO.File.ReadAllLines(filePath).Select(Function(l) l.Split(","c))

    Dim dt = New DataTable
    Dim count = 0

    dt.Columns.AddRange(rows.First.Select(Function(c) New DataColumn With {.ColumnName = If(hasHeaderRow, c, "Column" & Threading.Interlocked.Increment(count))}).ToArray())

    For Each row In rows.Skip(If(hasHeaderRow, 1, 0))
        Dim dr = dt.NewRow()
        dr.ItemArray = row
        dt.Rows.Add(dr)
    Next

    Return dt
End Function

edited Mar 27, 2015 at 2:48

answered Mar 25, 2015 at 23:00

Craig Johnson

7544 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user2382321 Over a year ago

@ Craig Johnson I tried yours as well and no luck either

Craig Johnson Over a year ago

With a blank GridView and a .CSV file with 1,2,3,4,5 6,7,8,9,0 The code above adds 5 columns and 2 rows to the GridView. Did you want something besides that?

user2382321 Over a year ago

@ Craig Johnson I get the 5 column headers but I dont get the rows filled in with the numbers from 1 through 10.

user2382321 Over a year ago

@ Craig Johnson my mistake. It does work. My csv file was flawed. Thanks very much. The only thing though is my actual csv file has 80 columns and while your method works, it seems a bit cumbersome to list out ".columnX = terms(X)" 80 times. Any suggestions?

Craig Johnson Over a year ago

@user2382321 see edits to dynamically generate columns.

|

NoAlias · Accepted Answer · 2015-03-25 22:10:45Z

0

If you wish for the line property of the OutlookLine class to be an array of string, you should define it like this:

Private Class OutlookLine
    Private theLine As String()
    Public Property line As String()
        Get
            Return theLine
        End Get
        Set(ByVal value As String())
            theLine = value
        End Set
    End Property
End Class

Then this will work:

        OutlookLines.Add(New OutlookLine With {.line = sr.ReadLine.Split(",")})

answered Mar 25, 2015 at 22:10

NoAlias

9,2032 gold badges30 silver badges46 bronze badges

1 Comment

user2382321 Over a year ago

@ NoAlias Thanks for this but it didn't work. I copied both lines in exactly how you typed them out but no luck.

Collectives™ on Stack Overflow

CSV file to datagridview using list

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related