FileSystemObject to get last 10 lines of text file.

  • Thread starter Thread starter john
  • Start date Start date
J

john

Hi there,

I am interested in finding out how to read the last 10 lines (or so) of a
text file using the FileSystemObject function, rather than looping through a
text file line-by-line until the end is reached then working backwards.

I may be looking at reading the last 10 lines of a large text file almost
every second, and would be interested in the fastest method available.

If anyone can explain how this could be done, I'd be very grateful!

Thank you.
 
Hi Dave,

Thanks for the link.

I gave the code a quick go (Excel) and it fell flat on it's face. Before I
go asking how to make it work, were you pointing to this code as a 'how to'
within VBA, or an indication as to the results using that approach?

If there are no HUGE benefits from the FileSystemObject, then the File Line
# method is probably the quickest/appropriate direction, yes? (Although I
stepped through a procedure and watched it go over and over till it got to
the last line:(

To explain a little further (Hi Tom): The file in question is a logfile that
updates by adding a few extra lines of data each time an action/change is
made within a -browser based- application (which I cannot gain access to or
control). The section (Say 10 lines detailing Log-off time) I'm interested
in catching, is appended when the application closes/logs off.

I have looked into detecting the Findwindow (title) to detect when the
application isn't in use, as a bases to either check the logfile, or record
a time when the application was stopped... But that was a no go, as the
application is itself appears to be running within an application and whats
worse, the window title doesn't change even when a different role is being
performed within that application... Thats right, it's a horrible situation!

So I'm back to looking at the end of the log-file every time a change occurs
within the application, which funnily enough, I can detect via the FileDate
change function to detect file properties.

Now that I've wrote out all the negatives, I bet I've also convinced you I'm
on a looser!

Thanks again!
 
Hi Tom,

Thank you for the insight.

I expect a Sequential read, would be most appropriate to the file, as it has
random bits throughout and a header line... (the file is at work, that's why
no sample supplied sorry)

When you say "you can read the 1 to n bytes into a string and then loop
backwards from the eof" ...would you mind explaining how would this be
achieved?
 
If you look at Dave's link, the code by Gurgen Alaverdian is doing that.

allrecords = RegFile.Read(x.Size)

reads all the bytes into the variable allrecords as a huge text string.

this steps back through the string:

Y = 1
Do Until Y = nLines + 2
m = InStrRev(allrecords, vbCrLf, m - 1)
Y = Y + 1
Loop

It uses instrrev which is only available in VBA6 (xl2000 and later).

----------
Since you say it is appending to a single file, I would just keep a record
of the file size the last time you read it, then go to that point and start
reading. I don't think the filesystemobject offers any advantage for doing
that if you are doing it from Excel.

This is a good reference for fileio in Excel/VBA.
http://support.microsoft.com/support/excel/content/fileio/fileio.asp
File Access with Visual Basic® for Applications



Regards,
Tom Ogilvy
 
Does the last line mean wscript.echo? If yes, then this is the equivalent of a
msgbox. It was there to show the that the data was retrieved--not to be used in
real life?

If you're checking lots of times just to see if there are changes, then maybe
you could use an API that watches for changes.

Randy Birch's VBNet site has some code that uses API calls to watch a folder.
Try http://www.mvps.org/vbnet

look for: FindChangeNotification
 
Using a Reg Dump file like in the cited thread:

353958 records

8.30018749999726 Seconds!

But it looked like a good idea.

Regards,
Tom Ogilvy


Dana DeLouis said:
Hi Dave. If interested in making it faster, you need a special trick.
This took 1.4715 Seconds! with 200,000 lines. HTH.

Sub Read_Last_10_Records()
'// = = = = = = = = = = = = = = = = =
'// By Dana DeLouis
'// = = = = = = = = = = = = = = = = =
Dim fso
Dim f
Dim t As Double 'Timer
Dim j As Long 'Loop counter
Dim s As String 'Dummy String
Dim NumberOfRecords As Long

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const nLines = 10
Const File = "C:\Temp.tp"

t = Timer
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(File, ForAppending)
NumberOfRecords = f.Line - 1
f.Close

Set f = fso.OpenTextFile(File, ForReading)
For j = 1 To NumberOfRecords - nLines
s = f.SkipLine
Next

'You could put the rest into an Array
'v = Split((f.readall), vbLf)

For j = 1 To nLines
Debug.Print f.ReadLine
Next

f.Close
Set f = Nothing
Set fso = Nothing

Debug.Print
Debug.Print Timer - t & " Seconds!"
End Sub

The above code returned:

This is line 199991
This is line 199992
This is line 199993
This is line 199994
This is line 199995
This is line 199996
This is line 199997
This is line 199998
This is line 199999
This is line 200000

1.47153124999932 Seconds!

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Dave Peterson said:
I searched google in the *scripting* newsgroup and found this:

http://groups.google.com/groups?threadm=e9x3kPjPCHA.2512@tkmsftngp10

The last post in the thread contained this:

This code takes 4 seconds to get last 10 lines out of 221,000 Lines regdmp
File.
Click to expand...
of
Click to expand...
 
John,

I've been looking at the variuous posts here and there are some great
ideas. In the past, I've used the one with the Skip method posted by Dana.
Are you writing the log? If so, have you considered making your log a fixed
record length and using vb random file access. You could do a direct seek
to the 10th record from the end. Random access would be the fastest way to
go. Another possibily is to log to a DB table instead of a file.

Thanks,

Jim

Jim Vita
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi Dana,

Your code is cracking stuff!

I'm getting strange return times, but it's always between fast and very
fast, so no problem!

If I don't find any other ways, this should do the trick.

Thank you.

Dana DeLouis said:
Hi Dave. If interested in making it faster, you need a special trick.
This took 1.4715 Seconds! with 200,000 lines. HTH.

Sub Read_Last_10_Records()
'// = = = = = = = = = = = = = = = = =
'// By Dana DeLouis
'// = = = = = = = = = = = = = = = = =
Dim fso
Dim f
Dim t As Double 'Timer
Dim j As Long 'Loop counter
Dim s As String 'Dummy String
Dim NumberOfRecords As Long

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const nLines = 10
Const File = "C:\Temp.tp"

t = Timer
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(File, ForAppending)
NumberOfRecords = f.Line - 1
f.Close

Set f = fso.OpenTextFile(File, ForReading)
For j = 1 To NumberOfRecords - nLines
s = f.SkipLine
Next

'You could put the rest into an Array
'v = Split((f.readall), vbLf)

For j = 1 To nLines
Debug.Print f.ReadLine
Next

f.Close
Set f = Nothing
Set fso = Nothing

Debug.Print
Debug.Print Timer - t & " Seconds!"
End Sub

The above code returned:

This is line 199991
This is line 199992
This is line 199993
This is line 199994
This is line 199995
This is line 199996
This is line 199997
This is line 199998
This is line 199999
This is line 200000

1.47153124999932 Seconds!

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Dave Peterson said:
I searched google in the *scripting* newsgroup and found this:

http://groups.google.com/groups?threadm=e9x3kPjPCHA.2512@tkmsftngp10

The last post in the thread contained this:

This code takes 4 seconds to get last 10 lines out of 221,000 Lines regdmp
File.
Click to expand...
of
Click to expand...
 
Hi Jim,

You are right, the suggestion and feedback has been tremendous, don't get me
wrong. I'm grateful! I was just looking for the quickest method... As to
your question, unfortunately I'm not creating the log file as such, at least
I don't have the control to do as you suggest. Thanks for pointing that out
tho!

It made me think that maybe I could Link this information somehow? The
linking facility in Excel, returns an icon (rather than the text file
contents), which is about as useful as something not very useful!

Maybe Access has more power to deal with such requests. I'm not a big fan of
Access but it could probably handle the same objective, maybe better seeing
as it's Data, not calculations...

I'll go poke the Access.groups then.

Thanks
 
John,

I've been doing a little more poking on your problem. I know your're doing
VB but C++ has the abiliity manipulate file pointer in a way that VB can't.
I've been playing with some C++ code that is really fast. I created a
500,000 record / 80 character file. The time to read the last 10 records is
unmeasurable (it calculates to 0.00000000). The trick in this code is that
you can't specify a number of records from the end. You have to specify a
file offset from the end in bytes. However, it automatically syncs itself
to the first full record following the offset point you select. Now, you
can't use C++ directly but you could write a standard DLL wrapper around
the function and include it in VB with a Declare Function. You could also
create a COM wrapper around it. Would you like me to post it?

Thanks,

Jim

Jim Vita
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top