simonwait
asked on
Count how many lines in a CSV using VB
I have a section of code below which will loop through a csv to find certain values. Im trying to make a progress bar so I want to get the total number of lines in the csv so that I can split the bar up. At present there is a section to initially loop through the csv counting each line and then the intention was to go back to the top and start looping for the values. I have two problems now. One is im not sure how to get back to the top of the csv (i tried .MoveFirst but that doesnt work) The other is that the CSV's are huge so this really slows everything down.
Is there a better solution to this?
Is there a better solution to this?
If Right(filea, 7) = "HK1.csv" Or Right(filea, 7) = "HK2.csv" Or Right(filea, 7) = "HK3.csv" Then
frmProgress.update , , 0
'housekeeping output file
If txtOutput.Text = "" Then
txtOutput.Text = "----------------" & Left(filea, 10) & "----------------" & vbNewLine
Else
txtOutput.Text = txtOutput.Text & vbNewLine & "----------------" & Left(filea, 10) & "----------------" & vbNewLine
End If
m_logdate = Left(filea, 10)
nFileNumber = FreeFile
Open txtDirectory.Text & "\" & filea For Input As #nFileNumber
frmProgress.update , , 1
'Do While Not EOF(1)
csvlinecount = 0
thisline = 0
Do While Not EOF(nFileNumber)
csvlinecount = csvlinecount + 1
Loop
thisline = thisline + 1
frmProgress.update , , 1 + (thisline * (6 / csvlinecount))
Do While Not EOF(nFileNumber)
Line Input #nFileNumber, strLine
'find last stop button press
If InStr(1, strLine, "PBSTAT_STOP") Then
strTimeStampEnd = Split(strLine, ";")
End If
'find first move
If InStr(1, strLine, "PBSTAT_START") Then
strTimeStamp = Split(strLine, ";")
movesstarted = True
On Error Resume Next
m_FinishTime = strTimeStampEnd(1)
On Error GoTo 0
If firststart = True Then
m_StartTime = strTimeStamp(1)
txtOutput.Text = txtOutput.Text & vbNewLine & "1st move started at: " & strTimeStamp(1)
DurationStart = Format(strTimeStamp(1), "hh:mm:ss")
firststart = False
End If
End If
End If
When you say these text files are "huge", what are the sizes in megabytes or gigabytes?
The VB statement you need for repositioning the 'cursor' within a file is SEEK.
The VB statement you need for repositioning the 'cursor' within a file is SEEK.
If you have the RAM, I think Martin's code is the simplest. You can iterate the items in the resulting array and do your split with the semicolon delimiter.
ASKER
So I tried inserting your suggestion as seen below (i changed the strlines to csvcount as strlines is already a variable i use). I get a type mismatch. The file sizes are gigabytes in size. Am I confused but I thought split would split the actual words "txtDirectory.Text & "\" & filea - file"?
Thanks for your help so far
Thanks for your help so far
If txtOutput.Text = "" Then
txtOutput.Text = "----------------" & Left(filea, 10) & "----------------" & vbNewLine
Else
txtOutput.Text = txtOutput.Text & vbNewLine & "----------------" & Left(filea, 10) & "----------------" & vbNewLine
End If
m_logdate = Left(filea, 10)
Dim CSVcount() As String
CSVcount = Split(txtDirectory.Text & "\" & filea - file, vbNewLine)
csvlinecount = UBound(StrLines7) + 1
nFileNumber = FreeFile
Open txtDirectory.Text & "\" & filea For Input As #nFileNumber
frmProgress.update , , 1
thisline = thisline + 1
frmProgress.update , , 1 + (thisline * (6 / csvlinecount))
Do While Not EOF(nFileNumber)
Line Input #nFileNumber, strLine
'find last stop button press
If InStr(1, strLine, "PBSTAT_STOP") Then
strTimeStampEnd = Split(strLine, ";")
End If
'find first move
If InStr(1, strLine, "PBSTAT_START") Then
strTimeStamp = Split(strLine, ";")
movesstarted = True
On Error Resume Next
m_FinishTime = strTimeStampEnd(1)
On Error GoTo 0
If firststart = True Then
m_StartTime = strTimeStamp(1)
txtOutput.Text = txtOutput.Text & vbNewLine & "1st move started at: " & strTimeStamp(1)
DurationStart = Format(strTimeStamp(1), "hh:mm:ss")
firststart = False
End If
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window