Link to home
Start Free TrialLog in
Avatar of simonwait
simonwaitFlag for United Kingdom of Great Britain and Northern Ireland

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?

      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

Open in new window

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Something like this should give you the line count. The following is just an example as won't run as such.

Dim StrLines() As String

strLines = Split(MyCSV-File, ",")

line count = UBound(strLines) + 1

Open in new window

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.
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.
Avatar of simonwait

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

           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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial