How do you read a CSV file and display the results in a grid in Visual Basic 2010?

Question

How do you read a CSV file and display the results in a grid in Visual Basic 2010? This sounds so simple but I still can't find the answer to it after googling for a while. I have DataGridView on a form and it's called DataGridView1. I have a csv with just 3 columsn of data and I want to be able to display them.

Answer 1

Consider this snippet of code. Modify as you see fit, or to fit your requirements. You'll need to have Imports statements for System.IO and System.Data.OleDb.

Dim fi As New FileInfo("c:\foo.csv")
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName

Dim conn As New OleDbConnection(connectionString)
conn.Open()

'the SELECT statement is important here, 
'and requires some formatting to pull dates and deal with headers with spaces.
Dim cmdSelect As New OleDbCommand("SELECT Foo, Bar, FORMAT(""SomeDate"",'YYYY/MM/DD') AS SomeDate, ""SOME MULTI WORD COL"", FROM " & fi.Name, conn)

Dim adapter1 As New OleDbDataAdapter
adapter1.SelectCommand = cmdSelect

Dim ds As New DataSet
adapter1.Fill(ds, "DATA")

myDataGridView.DataSource = ds.Tables(0).DefaultView 
myDataGridView.DataBind
conn.Close()

Answer 2

Consider this CodeProject article/project: LINQ TO CSV.

It will enable you to create a custom class that is shaped like your .csv file's columns. You'd then consume the CSV and bind to your DataGridView.

Dim cc As new CsvContext()
Dim inputFileDescription As New CsvFileDescription() With { _
    .SeparatorChar = ","C, _
    .FirstLineHasColumnNames = True _
}

Dim products As IEnumerable(Of Product) = _
     cc.Read(Of Product)("products.csv", inputFileDescription)

' query from CSV, load into a new class of your own   
Dim productsByName = From p In products
    Select New CustomDisplayClass With _
       {.Name = p.Name, .SomeDate = p.SomeDate, .Price = p.Price}, _        
    Order By p.Name


myDataGridView1.DataSource = products
myDataGridView1.DataBind()

Answer 3

Use the TextFieldParser class built into the .Net framework.

Here's some code copied from an MSDN forum post by Paul Clement. It converts the CSV into a new in-memory DataTable and then binds the DataGridView to the DataTable

    Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\SemiColonDelimited.txt")

    TextFileReader.TextFieldType = FileIO.FieldType.Delimited
    TextFileReader.SetDelimiters(";")

    Dim TextFileTable As DataTable = Nothing

    Dim Column As DataColumn
    Dim Row As DataRow
    Dim UpperBound As Int32
    Dim ColumnCount As Int32
    Dim CurrentRow As String()

    While Not TextFileReader.EndOfData
        Try
            CurrentRow = TextFileReader.ReadFields()
            If Not CurrentRow Is Nothing Then
                ''# Check if DataTable has been created
                If TextFileTable Is Nothing Then
                    TextFileTable = New DataTable("TextFileTable")
                    ''# Get number of columns
                    UpperBound = CurrentRow.GetUpperBound(0)
                    ''# Create new DataTable
                    For ColumnCount = 0 To UpperBound
                        Column = New DataColumn()
                        Column.DataType = System.Type.GetType("System.String")
                        Column.ColumnName = "Column" & ColumnCount
                        Column.Caption = "Column" & ColumnCount
                        Column.ReadOnly = True
                        Column.Unique = False
                        TextFileTable.Columns.Add(Column)
                    Next
                End If
                Row = TextFileTable.NewRow
                For ColumnCount = 0 To UpperBound
                    Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
                Next
                TextFileTable.Rows.Add(Row)
            End If
        Catch ex As _
        Microsoft.VisualBasic.FileIO.MalformedLineException
            MsgBox("Line " & ex.Message & _
            "is not valid and will be skipped.")
        End Try
    End While
    TextFileReader.Dispose()
    frmMain.DataGrid1.DataSource = TextFileTable