Reading .CSV file in Visual Basic

[iMacRicky]

Hello ,
I Wanted to know how can we read in a .CSV file into Visual Basic.i wanted to use the data using field seperator a comma. I wanted read data from the .CSV file in visual basic row by row as each is a record of an event. as i am novive in this field so would be really thankful regarding any help in this this topic. An example of the type of data being used is shown below:

Date Name Age Height
04/06/08 joe hack 28 192
04/06/08 miss sammy 32 162

[JoeFrat]

Grab the whole file into a string. Then use Split to break it into lines. For each line, use Split again to break it into fields.

Private Sub cmdGo_Click()
Dim file_name As String
Dim fnum As Integer
Dim whole_file As String
Dim lines As Variant
Dim one_line As Variant
Dim num_rows As Long
Dim num_cols As Long
Dim the_array() As String
Dim R As Long
Dim C As Long

    file_name = App.Path
    If Right$(file_name, 1) <> "\" Then file_name = _
        file_name & "\"
    file_name = file_name & "test.csv"

    ' Load the file.
    fnum = FreeFile
    Open file_name For Input As fnum
    whole_file = Input$(LOF(fnum), #fnum)
    Close fnum

    ' Break the file into lines.
    lines = Split(whole_file, vbCrLf)

    ' Dimension the array.
    num_rows = UBound(lines)
    one_line = Split(lines(0), ",")
    num_cols = UBound(one_line)
    ReDim the_array(num_rows, num_cols)

    ' Copy the data into the array.
    For R = 0 To num_rows
        one_line = Split(lines(R), ",")
        For C = 0 To num_cols
            the_array(R, C) = one_line(C)
        Next C
    Next R

    ' Prove we have the data loaded.
    For R = 0 To num_rows
        For C = 0 To num_cols
            Debug.Print the_array(R, C) & "|";
        Next C
        Debug.Print
    Next R
    Debug.Print "======="
End Sub

vbhelper

[Modifier]

This code will turn CSV into a DataTable, you can then use this to populate the DataGridView

   
 Public Function ReadCsvToTable(ByVal Filename As String) As DataTable
        Dim sr As StreamReader = File.OpenText(Filename)

        Dim values As New Collections.Specialized.StringCollection
        values.AddRange(sr.ReadLine.Split(","))

        Dim table As New DataTable("table")
        Dim enumerator As Collections.Specialized.StringEnumerator
        enumerator = values.GetEnumerator()
        While enumerator.MoveNext
            table.Columns.Add(New DataColumn(enumerator.Current, GetType(String)))
        End While

        Dim row As DataRow
        Dim ordinal As Byte
        Dim buffer As New StringBuilder()
        Do While sr.EndOfStream = False
            If buffer.Length > 0 Then
                buffer.Remove(0, buffer.Length - 1) 'clear the buffer
            End If
            values.Clear() 'clear the values
            buffer.Append(sr.ReadLine()) 'read a line 
            values.AddRange(buffer.ToString().Split(","))

            row = table.NewRow()
            enumerator = values.GetEnumerator()
            ordinal = 0
            While enumerator.MoveNext
                row(ordinal) = enumerator.Current
                ordinal += 1
            End While
            table.Rows.Add(row)
        Loop

        Return table
    End Function

The first row in the csv file must contain the column headings.

[opaper]

The solution shown in the above code is not what you want exactly , but you know for most things you will never exactly find 'the solution'. You need to try it out and adapt it to suit your needs. This is part of software development.

Using parser As New Microsoft.VisualBasic.FileIO.TextFieldParser("c:\temp\myFile.csv")
   parser.SetDelimiters(",")
   While Not parser.EndOfData
      'do console stuff in here
   End While
End Using

Do a search for Microsoft.VisualBasic.FileIO.TextFieldParser and you'll find an answer.

[iMacRicky]

Hi , Thanks for your suggestion

I have solved my problem using the following code

Using parser As New Microsoft.VisualBasic.FileIO.TextFieldParser("c:\temp\myFile.csv")
   parser.SetDelimiters(",")
   While Not parser.EndOfData
      Me.DataGridView1.Rows.Add(parser.ReadFields())
   End While
End Using

thanks a lot bye