Basic CSV Reader

[BenJones]

Hi,
This is a small class I made to learn about strings and collections it will allow you to load a basic csv file and view the field values. This is my first try so any comments are welcome.

At the moment it does not support quoted string as i am not sure how to do this but i will try and work on it this week, but for now here is what I made so far, Hope the code maybe of use to someone.

Start vb and add a new class call it bCsv then add the code

'Bens CSV Reader v1.0
'Created Sunday 28/3/2010 19:23

'This stores all the rows
Private csvLines As Collection
'File to process
Private mFilename As String
'Number of rows
Private mRows As Long
'Number of fields
Private mFields As Long
'Delimiter
Private mDelimiter As String

Private Sub Class_Terminate()
    Set csvLines = Nothing
End Sub

Public Property Get Filename() As String
    Filename = mFilename
End Property

Public Property Let Filename(ByVal NewFilename As String)
Dim fp As Long
Dim sLine As String
Dim fd() As String
Dim sPos As Integer

   'This sub is used to load the csv file
   
    'Add default delimiter if not found
    Delimiter = IIf(Len(Delimiter) = 0, ",", Delimiter)

    'Create new collection object
    Set csvLines = New Collection
    'Get free file
    fp = FreeFile
    'Open the file for reading
    Open NewFilename For Input As #fp
        Do While Not EOF(fp)
            'Read in one line
            Line Input #fp, sLine
            'Trim down the line
            sLine = Trim$(sLine)
            'Check for Delimiters
            sPos = InStr(1, sLine, Delimiter, vbBinaryCompare)
            'Check that we have a row
            If Len(sLine) And (sPos > 0) Then
                'Add row to collection
                Call csvLines.Add(sLine)
                'Check if we have the first row
                If (csvLines.Count = 1) Then
                    'Find out the number of fields
                    fd = Split(sLine, Delimiter)
                    'Store count
                    mFields = UBound(fd)
                End If
            End If
        Loop
    Close #fp
    
    'Clear up
    Erase fd
    sLine = vbNullString
End Property

Public Property Get Rows() As Long
    'This returns the number of rows
    Rows = csvLines.Count
End Property

Public Property Get Fields() As Long
    'This returns the number of fields
    Fields = mFields
End Property

Public Property Get Delimiter() As String
    Delimiter = mDelimiter
End Property

Public Property Let Delimiter(ByVal NewDelimiter As String)
    mDelimiter = NewDelimiter
End Property

Public Property Get GetCell(ByVal Row As Long, ByVal Cell As Long) As String
Dim fdItems() As String

    'Test that we not going over row count
    If (Row > Rows) Or (Row < 1) Then
       Call Err.Raise(9)
       Exit Property
    ElseIf (Cell < 1) Or (Cell > Fields + 1) Then
        Call Err.Raise(9)
        Exit Property
    Else
        'Split up the fields
        fdItems = Split(csvLines(Row), Delimiter)
        'Return the value from the fields
        GetCell = fdItems(Cell - 1)
    End If
    'Clear up time
    Erase fdItems
    
End Property

Here is an example of the csv file i used for testing.

Red,Blue,Green
Visual Basic,Delphi
Cats,Dogs,Mice
Test1,Test2,VB Rocks

Save the above file to c:\test.csv then add this code to command button.

Dim Count As Integer
Dim mycsv As New bCsv
    With mycsv
        'Delimiter to split fields with
        .Delimiter = ","
        'Filename to load
        .Filename = "C:\test.csv"
        'Loop tho the records and show the first field value
        For Count = 1 To .Rows
            MsgBox .GetCell(Count, 1)
        Next Count
    End With

[BenJones]

hi This is the second update of my CSV loader, I now made it so the csv file loads into a listview control, I also added Add and Edit and Save.
I try adding more features this week.