-
CSV Calculations
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?
-
Re: CSV Calculations
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
Code:
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
-
Re: CSV Calculations
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:
Code:
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.
-
Re: CSV Calculations
This doesn't actually store the numbers from col3 in and array but we could do that if you want.
Code:
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)