Acquire line count of text file

JRL · Post by **JRL** » Wed Jul 19, 2006 2:25 pm

Hello all,

Does anyone have a method of acquiring the line count of a text file that will work rapidly with large files? Typically I use readfile> then separate> by CRLF and I can get the line count.

ReadFile>C:\path\file.txt,file
Separate>file,%CRLF%,line
MDL>C:\path\file.txt contains %line_count% lines

I'm currently working with a file that is 250,000+ lines and the ReadFile/Separate method is taking waaaaaay too long. I'd be happy with anything that took less than a minute. I'm really hoping there might be a libfunc> method that works in the blink of an eye. In this case I won't/can't accept anything that requires a third party program but if you have one you might mention it for future reference. Actually there is one in scripts and tips DOS / Windows Utility - llc - Count lines in a Text (ASCII) that requires a third party executable.

Thanks,
Dick

Bob Hansen · Post by **Bob Hansen** » Wed Jul 19, 2006 2:55 pm

The old DOS "FIND" command may work for you.

Try this from a command line:
FIND "qqq" c:\path\file.txt /v /c

You will get two lines back with a count of all the lines that do not contain "qqq" on the second line.

If that works for you then you can run that with Run Program command:

Run Program>command.com /c FIND "qqq" C:\path\file.txt /v /c > c:\path\lines.txt
ReadLn>c:\path\lines.txt,2,result
//You can use other lines to strip out everything to the left of the colon in %result%
MessageModal>Line count = %result%

JRL · Post by **JRL** » Wed Jul 19, 2006 3:10 pm

Thanks Bob,
That's what I needed. Done in a few seconds.

I was using find but for some stupid reason I was using find with a /v and /n and making a file with line numbers in it so I could read the last line number. I was taking my 250,000 line file and recreating it which took several minutes then running to the end of the newly created file to see the count which took a couple more minutes.

I think I need a vacation....

Thank you,
Dick

Bob Hansen · Post by **Bob Hansen** » Wed Jul 19, 2006 3:33 pm

Gee, I never noticed that the request was from you Dick.

Yes, at times we just need to take a step back. By all means, take a vacation. Your contributions here have more than earned that for you. Glad I could return the favor.

JRL · Post by **JRL** » Wed Jul 19, 2006 3:38 pm

I have a ERP users conference in Houston the first weekend in August. They pamper us nicely. That will have to suffice for now.

Thanks again Bob.

Me_again · Post by **Me_again** » Wed Jul 19, 2006 5:05 pm

I don't have such a big file to test it with, but a bit of hacked together FileSystemObject VB* seems to be faster on a 40K line file:

Let>filetest=C:\test\lines.txt
Iffileexists>%filetest%,continue,theend
Label>continue
VBStart
Function linecount (filespec)
Dim FSO, f, num
Const ForReading = 1
Set FSO = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set f = FSO.OpenTextFile(filespec, ForReading, False)
Do While f.AtEndOfStream = False
num = num + CInt(f.ReadLine)
Loop
linecount = f.line - 1
End Function
VBEnd

VBEval>linecount("%filetest%"),linecount
MessageModal>%linecount%
label>theend

*Based on some code posted a while ago by BlackWisdom

Update: I tested with a 400,000 line file and it runs in about 4 seconds, much faster than dos. I corrected linecount to equal f.line - 1

Post by **Marcus Tettmar** » Wed Jul 19, 2006 6:59 pm

JRL wrote:I have a ERP users conference in Houston the first weekend in August. They pamper us nicely. That will have to suffice for now.

Don't forget to evangelise Macro Scheduler at the conference and tell everyone about it!! In return for affiliate commission of course!

JRL · Post by **JRL** » Wed Jul 19, 2006 8:45 pm

Marcus,
No problem. I will be happy to promote a product I strongly believe in.

Anyone who is interested,
I wrote a little script to test DOS vs VB. As in other speed testing I've done pitting VBScript against DOS the conclusion is.... it depends.

On some files VB wins on others DOS wins. I don't see why but in some cases there are big differences. I have a 20,000 line text file on my hard drive that VB evaluates in 3 seconds and DOS takes 7. I created a 500,000 line text file and put it on the network and DOS reports the line count in 15 seconds and VB takes 45.

Here's the script, test for yourself:

Thank you Bob and Me_again I appreciate your time, efforts and general good will.

Hope this is useful,
Dick

Code: Select all

Let>file1=%TEMP_DIR%~FileTempTest~.txt
Let>testbug=~1!1~

Dialog>Dialog1
   Caption=Time to find total number of lines in a text file: DOS vs VB
   Width=458
   Height=186
   Top=CENTER
   Left=CENTER
   Close=0
   Label=Select a file for last line number acquisition,12,6
   Label= ,8,70
   Label= ,8,98
   Edit=msEdit1,8,24,385,
   Button=Use DOS,8,120,75,25,3
   Button=Use VB,112,120,75,25,4
   Button=Cancel,368,120,75,25,2
   Button=Browse,400,24,41,25,0
   FileBrowse=Browse,msEdit1,Text Files|*.txt|All Files|*.*,open
EndDialog>Dialog1

Show>dialog1
Label>start
gda>dialog1,r1
If>r1=4,ProcessVB
If>r1=3,ProcessDOS
If>r1=2,Exit
Wait>0.01
Goto>start
Label>Exit

SRT>ProcessDOS
Gettime>start1
Let>RP_WAIT=1
Let>RP_WINDOWMODE=2
Run>cmd /c find "%testbug%" /v /c "%dialog1.msedit1%" > "%file1%"
ReadFile>%file1%,bugwatch
Separate>%bugwatch%,%CRLF%,var
Separate>var_2,:,var
Let>line_count=%var_3%
Gettime>end1
Let>Dialog1.mslabel1=%line_count% with DOS took from %start1% to %end1%
RDA>Dialog1
END>ProcessDOS

SRT>ProcessVB
Gettime>start2
Let>filetest=%dialog1.msedit1%
Iffileexists>%filetest%,continue,theend
Label>continue
VBStart
Function linecount (filespec)
Dim FSO, f, num
Const ForReading = 1
Set FSO = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set f = FSO.OpenTextFile(filespec, ForReading, False)
Do While f.AtEndOfStream = False
num = num + CInt(f.ReadLine)
Loop
linecount = f.line
End Function
VBEnd

VBEval>linecount("%filetest%"),linecount
Gettime>end2
Let>Dialog1.mslabel2=%linecount% with VB took from %start2% to %end2%
label>theend
RDA>dialog1
END>ProcessVB

pgriffin · Post by **pgriffin** » Thu Jul 20, 2006 1:50 pm

(this is no help, just a thought)....

Guys,

at what point is it time to move from text file to database? I mean even a couple thousand text file records and I'm thinking it's a big win to remove this problem (counting lines) by going straight to SQLServer or even Access....just about any database would work, I think.

may be that a text file is unavoidable, but....

jpuziano · Post by **jpuziano** » Thu Apr 02, 2009 7:45 pm

Hi All,

For more on this topic and some fast ways to count the number of lines in a file or a memo field... check the following post: [Bounty Won] Text Blob Line Counter Speed Challenge

Take care