VBScript to count lines

I have a vbscript program which I run to collect data from a program on my PC and inputs the data into a csv file. That works nicely. Problem is that the customer wants it so that there is only 10,500 lines of data in each csv file as whatever they do with the file needs that number.

So its not so much the number of entries that I am counting, but the actual number of lines.

Is there a command I can use to make this work? After it reaches 10500, it then starts over on a brand new file leaving the one it just created available.
LVL 2
ademboAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
adembo,This is probably going to be easy to implement.  It would be best if you posted your current script.Patrick
0
ademboAuthor Commented:
Thanks for the input. I appreciate any assistance. Code is below:

'Grab Date and Time
'Constant Value Description
'vbGeneralDate                0              Display a Date In format mm/dd/yy. If the Date parameter Is Now(), it will also return the Time, after the Date
'vbLongDate                       1              Display a Date using the long Date format: Weekday, Month Day, Year
'vbShortDate     2              Display a Date using the short Date format: like the default (mm/dd/yy)
'vbLongTime                      3              Display a Time using the Time format: hh:mm:ss PM/AM
'vbShortTime     4              Display a Time using the 24-Hour format: hh:mm
If ((SmartTags("Sta2030.DnStrm.ForceTest.NOK")) Or (SmartTags("Sta2030.UpStrm.ForceTest.NOK")))= True Then
Dim DT, TT
DT = FormatDateTime(Date, 0)
TT = FormatDateTime(Time, 3)

'Open File and Write Data
'
'Define Variables
Dim myFile, oFile
Const ForReading = 1, ForWriting = 2, ForAppending = 8

'Create VBScript Object
Set myFile = CreateObject("Scripting.FileSystemObject")

'Check if File exists or if we need to recreate
If myFile.FileExists("C:\Logs\2030DataLog1.csv") = True Then
                'Open the existing file for appending
                Set oFile = myFile.OpenTextFile("C:\Logs\2030DataLog1.csv", ForAppending, True, 0)
Else
                'Create the new file
                Set oFile = myFile.CreateTextFile("C:\Logs\2030DataLog1.csv", True)
                'Write the Header Line
                oFile.WriteLine "Date,Time,2030 DS Force Min,2030 DS Force Max,2030 US Force Min,2030 US Force Max,2030 Pallet#, 2030 Pallet Index#, 2030 UpStream BallSize, 2030 DownStream BallSize"
End If

'Write my Data
oFile.WriteLine DT & "," & TT & "," & SmartTags("Sta2030.DnStrm.ForceTest.Min_0") & "," & SmartTags("Sta2030.DnStrm.ForceTest.Max_0") & "," & SmartTags("Sta2030.UpStrm.ForceTest.Min_0") & "," & SmartTags("Sta2030.UpStrm.ForceTest.Max_0") & "," & SmartTags("Sta2030.RFID_Data.Pallet.PalletNumber_0")& "," & SmartTags("Sta2030.RFID_Data.Pallet.PartArrayIndexNum_0")& "," & SmartTags("Sta2030.PartTypeData.UpStrmBallSize") & "," & SmartTags("Sta2030.PartTypeData.DnStrmBallSize")
oFile.Close

'Cleanup my files
Set oFile = Nothing
Set myFile = Nothing
Else
End If
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Bill PrewCommented:
Hmm, this is a little more involved than it sounded.  Basically you are only writing one line to the file each time this VBS runs.  So you would need to first open the prior file, count all the lines, and take action when it hits the limit.

But the challenge will be how to know that you have started a new file when this script runs again.  You might have to write another file with that info, or I suppose you could look for the "last" file matching the base name that you use.

How will the files be named when you need to create more than one?

And will they at some point be "processed" by another application, and therefore removed, so the next run needs to start fresh again?

~bp
0
RobSampsonCommented:
Hi there,

It looks like this script only writes one line of data each time it is called, so I have added a section that will count the lines in the file before writing that data.

It will count the lines in
C:\Logs\2030DataLog1.csv

and increment the number by 1 if that file has intMaxLines in it.  If it doesn't have that many yet, it will append to that file, otherwise it will check
C:\Logs\2030DataLog2.csv and C:\Logs\2030DataLog3.csv and so on until it finds a name it can use.

Take out this line
      MsgBox "Using " & strLogFile

if you don't want it to tell you which file it's writing to.

Regards,

Rob.
'Grab Date and Time
'Constant Value Description 
'vbGeneralDate                0              Display a Date In format mm/dd/yy. If the Date parameter Is Now(), it will also return the Time, after the Date 
'vbLongDate                       1              Display a Date using the long Date format: Weekday, Month Day, Year 
'vbShortDate     2              Display a Date using the short Date format: like the default (mm/dd/yy) 
'vbLongTime                      3              Display a Time using the Time format: hh:mm:ss PM/AM 
'vbShortTime     4              Display a Time using the 24-Hour format: hh:mm 
If ((SmartTags("Sta2030.DnStrm.ForceTest.NOK")) Or (SmartTags("Sta2030.UpStrm.ForceTest.NOK")))= True Then
	Dim DT, TT
	DT = FormatDateTime(Date, 0)
	TT = FormatDateTime(Time, 3)
	
	'Open File and Write Data
	'
	'Define Variables
	Dim myFile, oFile
	Const ForReading = 1, ForWriting = 2, ForAppending = 8
	
	'Create VBScript Object
	Set myFile = CreateObject("Scripting.FileSystemObject")
	
	'Check if File exists or if we need to recreate
	
	intCurrentLineCount = 0
	intMaxLines = 10500
	intNum = 0
	boolUnderMax = False
	Do
		intNum = intNum + 1
		strLogFile = "C:\Logs\2030DataLog" & intNum & ".csv"
		If myFile.FileExists(strLogFile) = True Then
			Set objCount = myFile.OpenTextFile(strLogFile, ForReading, False)
			If Not objCount.AtEndOfStream Then
				If UBound(Split(objCount.ReadAll, VbCrLf)) + 1 < intMaxLines Then boolUnderMax = True
			Else
				boolUnderMax = True
			End If
			objCount.Close
		Else
			boolUnderMax = True
		End If
	Loop Until boolUnderMax = True
	
	MsgBox "Using " & strLogFile
	
	If myFile.FileExists(strLogFile) = True Then
		'Open the existing file for appending
		Set oFile = myFile.OpenTextFile(strLogFile, ForAppending, True, 0)
	Else
		'Create the new file
		Set oFile = myFile.CreateTextFile(strLogFile, True)
		'Write the Header Line
		oFile.WriteLine "Date,Time,2030 DS Force Min,2030 DS Force Max,2030 US Force Min,2030 US Force Max,2030 Pallet#, 2030 Pallet Index#, 2030 UpStream BallSize, 2030 DownStream BallSize"
	End If
	
	'Write my Data
	oFile.WriteLine DT & "," & TT & "," & SmartTags("Sta2030.DnStrm.ForceTest.Min_0") & "," & SmartTags("Sta2030.DnStrm.ForceTest.Max_0") & "," & SmartTags("Sta2030.UpStrm.ForceTest.Min_0") & "," & SmartTags("Sta2030.UpStrm.ForceTest.Max_0") & "," & SmartTags("Sta2030.RFID_Data.Pallet.PalletNumber_0")& "," & SmartTags("Sta2030.RFID_Data.Pallet.PartArrayIndexNum_0")& "," & SmartTags("Sta2030.PartTypeData.UpStrmBallSize") & "," & SmartTags("Sta2030.PartTypeData.DnStrmBallSize")
	oFile.Close
	
	'Cleanup my files
	Set oFile = Nothing
	Set myFile = Nothing
Else
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ademboAuthor Commented:
Thanks for the replies. I have taken the code listed from RobSampson and will be testing today. I will update the status after testing is complete.

Thanks.
0
RobSampsonCommented:
Hi Adembo,

Did you have any luck testing out the possible solutions?

Regards,

Rob.
0
ademboAuthor Commented:
Sorry, I should have followed up sooner. I have not been at the site where I needed this script. I will be there on Tuesday and will be testing. I will update the question with my results.

Thanks again.
0
RobSampsonCommented:
Hi, just a check to see how you're going....

Rob.
0
ademboAuthor Commented:
Thanks for your assistance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.