Counting lines in CSV

[Fherrera]

Hi, I have a couple of huge CSV files and I want to count the number of lines/rows in the file.

Each CSV file is preeetty huge, so even opening them in Notepad takes 30 seconds to a few minutes depending..

Also, there are a few. And for the sake of data integrity i'd like to count the number of rows and make sure they are the same as I have in access (which i'm importing them too)

Since there ARE so many rows I couldn't use excel.

So, does anyone have idea's? I was HOPING there might be some property or else it would be just opening it up, reading a line at a time increment the counter until the EOF.

Any suggestions?

Thanks.

 

[PHV]

Take a look at the FileSystemObject for Files collection, OpenAsTextStream and ReadLine methods.
You may consider the Split, InStr or Replace functions if you're interested in counting the fields in each row.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dilettante]

One quick way to hack this is to use the Jet OLEDB Provider's ability to manipulate text files. It is highly optimized for this, though I've never tried it on a file of hundreds of megabytes.

RecordCount.wsf

<job>
  <reference object="ADODB.Recordset"/>
  <object id="rsData" progid="ADODB.Recordset"/>
  <script language="vbscript">
    Option Explicit

    Const strDataName = "big.txt"
    Dim strNotInFile
    Dim strConn

    Function ScriptPath()
      Dim strSFN

      strSFN = WScript.ScriptFullName
      ScriptPath = Left(strSFN, InStrRev(strSFN, "\") - 1)
    End Function

    strNotInFile = vbFormFeed 'Chr(12) a.k.a. Chr(&H0C)

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                  & "Data Source=" & ScriptPath & ";" _
                  & "Extended Properties=""Text;HDR=NO;FMT=Delimited(" _
                      & strNotInFile & ")"""

    rsData.Open strDataName, strConn, _
                adOpenStatic, adLockReadOnly, adCmdTable
    MsgBox CStr(rsData.RecordCount)
    rsData.Close
  </script>
</job>

Setting the field delimiter to some value not present in the file results in each line being treated as a single field. This lets nearly any text file be quickly "record counted."

This script counted records in a 516KB text file I had handy in about 3 seconds.