?
Solved

Count how many lines in a CSV using VB

Posted on 2012-08-30
5
Medium Priority
?
222 Views
Last Modified: 2012-09-05
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

0
Comment
Question by:simonwait
  • 3
5 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38352483
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

0
 
LVL 46

Expert Comment

by:aikimark
ID: 38352751
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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38352754
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.
0
 
LVL 1

Author Comment

by:simonwait
ID: 38353211
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

0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 38354203
strings variables and textboxes can only contain 2.14G.  You will not be able to do a split.

From a performance and sanity perspective, report your progress as the ratio of the SEEK() function to the LOF() function.  SEEK() is the current position and LOF is the length of the file.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month15 days, 20 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question