Solved

VBScript to count lines

Posted on 2010-08-23
10
695 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:adembo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33504676
adembo,This is probably going to be easy to implement.  It would be best if you posted your current script.Patrick
0
 
LVL 2

Author Comment

by:adembo
ID: 33504739
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
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 
LVL 56

Expert Comment

by:Bill Prew
ID: 33506591
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
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 33506850
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
 
LVL 2

Author Comment

by:adembo
ID: 33510335
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 33552336
Hi Adembo,

Did you have any luck testing out the possible solutions?

Regards,

Rob.
0
 
LVL 2

Author Comment

by:adembo
ID: 33553294
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 33670887
Hi, just a check to see how you're going....

Rob.
0
 
LVL 2

Author Comment

by:adembo
ID: 33671089
Thanks for your assistance.
0

Featured Post

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

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

Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

624 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