CSV Calculations

[L0stAngel]

Hello, I would like to be able to import a csv file into my vb program, and have it do math on the numbers in the CSV. First, the csv can have between 1 column and 40 columns...First, how can I have it simply display the column headers (i.e. first row of the csv) in a listbox...secondly...how can I for example have it add up all of the rows for column 3...and give the average?

[MartinLiss]

If you want to work with columns you don't want to use a listbox. Here's a better way. Add a listview and a command button to the form and

Option Explicit

Private Sub Command1_Click()

    Dim lngIndex As Long
    Dim lngTotal As Long
    
    For lngIndex = 1 To ListView1.ListItems.Count - 1
        lngTotal = lngTotal + ListView1.ListItems(lngIndex).SubItems(2)
    Next
    
    MsgBox "Average of column3 is " & lngTotal / ListView1.ListItems.Count
End Sub

Private Sub Form_Load()

    Dim strParts() As String
    Dim FF As Integer
    Dim strLine As String
    Dim lngIndex As Long
    Dim bFirst As Boolean

    ListView1.View = lvwReport
    
    FF = FreeFile
    Open "C:\temp\test.txt" For Input As FF
    
    bFirst = True
    Do While Not EOF(FF)
        Line Input #FF, strLine
        strParts = Split(strLine, ",")
        For lngIndex = 0 To UBound(strParts)
            If bFirst Then
                ListView1.ColumnHeaders.Add , , strParts(lngIndex), 500
            Else
                If lngIndex = 0 Then
                    ListView1.ListItems.Add , , strParts(lngIndex)
                Else
                    ListView1.ListItems(ListView1.ListItems.Count).SubItems(lngIndex) = strParts(lngIndex)
                End If
            End If
        Next
        bFirst = False
    Loop
    
    Close FF
End Sub

[L0stAngel]

Martin, thanks for your post...but I think I may have not explained it well. I would simply like the column names added to a listbox (which I did modify your code to the following:

Private Sub cmdGetLoggedData_Click()
    Dim strParts() As String
    Dim FF As Integer
    Dim strLine As String
    Dim lngIndex As Long
    Dim bFirst As Boolean
    
    FF = FreeFile
    Open "C:\LogFiles\4thlog.csv" For Input As FF
    
    bFirst = True
    Do While Not EOF(FF)
        Line Input #FF, strLine
        strParts = Split(strLine, ",")
        For lngIndex = 0 To UBound(strParts)
            If bFirst Then
                'ListView1.ColumnHeaders.Add , , strParts(lngIndex), 500
                List1.AddItem strParts(lngIndex)
            End If
        Next
        bFirst = False
    Loop
    
    Close FF
End Sub

That worked fine, is there a way I could easily do the calculations you posted in an array instead? Wouldn't that be faster? Also, mind you the first two rows of the CSV are NOT numbers.

[MartinLiss]

This doesn't actually store the numbers from col3 in and array but we could do that if you want.

    Dim strParts() As String
    Dim FF As Integer
    Dim strLine As String
    Dim lngIndex As Long
    Dim intRowCount As Integer
    Dim lngTotal As Long
    
    FF = FreeFile
    Open "C:\temp\test.txt" For Input As FF
    
    Do While Not EOF(FF)
        Line Input #FF, strLine
        strParts = Split(strLine, ",")
        For lngIndex = 0 To UBound(strParts)
            If intRowCount < 2 Then
                List1.AddItem strParts(lngIndex)
            Else
                lngTotal = lngTotal + strParts(lngIndex)
            End If
        Next
        intRowCount = intRowCount + 1
    Loop
    
    Close FF
    MsgBox "Average of column3 is " & lngTotal / (intRowCount - 2)