Visual Basic How do I read a CSV file and display the values in a datagrid?

Question

I'm using VB 2005, how do I open a CSV file and read the columns/rows and display the values in a datagrid?

CSV file example: jsmith,[email protected]

I then want to perform an action on each row i.e. each user, how would I do this?

I'm a newbie as you can tell but happy to learn.

Thanks

Answer 1

Usage:

Dim reader As New Common.CSVReader("C:\MyFile.txt")
reader.DisplayResults(dgvMyView)

Class:

Imports System.Windows.Forms
Imports System.IO
Imports System.Text.RegularExpressions

Public Class CSVReader
    Private Const ESCAPE_SPLIT_REGEX = "({1}[^{1}]*{1})*(?<Separator>{0})({1}[^{1}]*{1})*"
    Private FieldNames As String()
    Private Records As List(Of String())
    Private ReadIndex As Integer 

    Public Sub New(File As String)
     Records = New List(Of String())
     Dim Record As String()
     Dim Reader As New StreamReader(File)
     Dim Index As Integer = 0
     Dim BlankRecord As Boolean = True 

     FieldNames = GetEscapedSVs(Reader.ReadLine())
     While Not Reader.EndOfStream
      Record = GetEscapedSVs(Reader.ReadLine())
      BlankRecord = True
      For Index = 0 to Record.Length - 1
       If Record(Index) <> "" Then BlankRecord = False 
      Next
      If Not BlankRecord Then Records.Add(Record)
     End While
     ReadIndex = -1
     Reader.Close()
    End Sub

    Private Function GetEscapedSVs(Data As String, Optional Separator As String = ",", Optional Escape As String = """") As String()
     Dim Result As String()  
     Dim Index As Integer
     Dim PriorMatchIndex As Integer = 0
     Dim Matches As MatchCollection = _
      Regex.Matches(Data, String.Format(ESCAPE_SPLIT_REGEX, Separator, Escape))

     ReDim Result(Matches.Count)

     For Index = 0 to Result.Length - 2
      Result(Index) = Data.Substring(PriorMatchIndex, Matches.Item(Index).Groups("Separator").Index - PriorMatchIndex)
      PriorMatchIndex = Matches.Item(Index).Groups("Separator").Index + Separator.Length
     Next
     Result(Result.Length - 1) = Data.Substring(PriorMatchIndex)

     For Index = 0 To Result.Length - 1
            If Regex.IsMatch(Result(Index), String.Format("^{0}[^{0}].*[^{0}]{0}$", Escape)) Then _
    Result(Index) = Result(Index).Substring(1, Result(Index).Length - 2)
      Result(Index) = Replace(Result(Index), Escape & Escape, Escape)
      If Result(Index) Is Nothing Then Result(Index) = ""
     Next

     GetEscapedSVs = Result 
    End Function

    Public ReadOnly Property FieldCount As Integer 
     Get
      Return FieldNames.Length 
     End Get
    End Property

    Public Function GetString(Index As Integer) As String
     Return Records(ReadIndex)(Index)
    End Function

    Public Function GetName(Index As Integer) As String
     Return FieldNames(Index)
    End Function

    Public Function Read() As Boolean
     ReadIndex = ReadIndex + 1
     Return ReadIndex < Records.Count 
    End Function


    Public Sub DisplayResults(DataView As DataGridView)
        Dim col As DataGridViewColumn
        Dim row As DataGridViewRow
        Dim cell As DataGridViewCell
        Dim header As DataGridViewColumnHeaderCell
     Dim Index As Integer
     ReadIndex = -1

     DataView.Rows.Clear()
        DataView.Columns.Clear()

        For Index = 0 to FieldCount - 1
            col = new DataGridViewColumn()
            col.CellTemplate = new DataGridViewTextBoxCell()
            header = new DataGridViewColumnHeaderCell()
            header.Value = GetName(Index)
            col.HeaderCell = header
            DataView.Columns.Add(col)
        Next 

        Do While Read()
            row = new DataGridViewRow()
            For Index = 0 to FieldCount - 1
                cell = new DataGridViewTextBoxCell()
                cell.Value = GetString(Index).ToString()
                row.Cells.Add(cell)
            Next
            DataView.Rows.Add(row)
        Loop
    End Sub
End Class

Answer 2

Here's a simple solution that uses ADO.Net's ODBC text driver:

Dim csvFileFolder As String = "C:\YourFileFolder"
Dim csvFileName As String = "YourFile.csv"

'Note that the folder is specified in the connection string,
'not the file. That's specified in the SELECT query, later.
Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
    & csvFileFolder & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""

Dim conn As New Odbc.OdbcConnection(connString)

'Open a data adapter, specifying the file name to load
Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM [" & csvFileName & "]", conn)
'Then fill a data table, which can be bound to a grid
Dim dt As New DataTable
da.Fill(dt)

grdCSVData.DataSource = dt

Once filled, you can value properties of the datatable, like ColumnName, to make utilize all the powers of the ADO.Net data objects.

In VS2008 you can use Linq to achieve the same effect.

Answer 3

Use the TextFieldParser that's built into VB.NET. Google found me this example

Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser _
  ("C:\test\info.csv")

  'Specify that reading from a comma-delimited file'
  MyReader.TextFieldType = FileIO.FieldType.Delimited
  MyReader.SetDelimiters(",")

  Dim currentRow As String()
  While Not MyReader.EndOfData
    Try
      currentRow = MyReader.ReadFields()
      With Me.dgvReport.Rows
        .Add(currentRow) 'Add new row to data grid view'
     End With
   Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
     MsgBox("Line " & ex.Message & _
       "is not valid and will be skipped.")
   End Try
 End While
End Using