VBScript Function to Count Rows in a CSV File

I was working with an old third party tool that only supported VBScript for programming.  I needed to email a file if the file had more than one row.  All the CSV (Comma Separated Value) files created had the CSV headers on the first line. Basically, this was an error/exception report, so I only wanted to send an email if there was more than one line in the file. The following basically counts the number of Carriage-Return/Line-Feed characters.

set fso=CreateObject("Scripting.FileSystemObject")

fa = "E:\MESSAGES\ACER\PROD\Batch\Out\database_failed_POs_20150916_140035.csv"
countRows = CountRowsInFile(fa)
msgbox "File with Error(s) Only countRows=" & countRows 

fa = "E:\MESSAGES\ACER\PROD\Batch\Out\database_failed_POs_20150916_150009.csv"
countRows = CountRowsInFile(fa)
msgbox "Header Only countRows=" & countRows 

Function CountRowsInFile(filename)
  Dim oFso, oReg, sData, lCount
  Const ForReading = 1
  Set oReg = New RegExp
  sData = Fso.OpenTextFile(filename, ForReading).ReadAll
  With oReg
      .Global = True
      .Pattern = "\r\n" 'vbCrLf
      '.Pattern = "\n" ' vbLf, UTF-8 encoded text file?
      lCount = .Execute(sData).Count 
  End With
  Set oReg = Nothing
  CountRowsInFile = lCount 
End Function 


Above was modeled after post on StackOverflow (basically turned it into a Function).


Leave a Reply