Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
I searched google in the *scripting* newsgroup and found this:
http://groups.google.com/groups?thre...%40tkmsftngp10 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. john wrote: 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. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
I guess that is about 3 second too long <g
Regards, Tom Ogilvy Dave Peterson wrote in message ... I searched google in the *scripting* newsgroup and found this: http://groups.google.com/groups?thre...%40tkmsftngp10 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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! Dave Peterson wrote in message ... I searched google in the *scripting* newsgroup and found this: http://groups.google.com/groups?thre...%40tkmsftngp10 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. john wrote: 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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? Tom Ogilvy wrote in message ... What do you know about the file. Are all lines exactly the same length? There is no magic to reading a file - it is a sequential read in most cases. A file is one to n bytes. The concept of a line is defined in Windows by encountering a crlf combination. Anything that would be faster would involve calculating where to put the file pointer and if your lines aren't equal length, the alternative is to read the data and count the lines. That said, you can read the 1 to n bytes into a string and then loop backwards from the eof. Look at the objects/properties/methods of textstream in the scripting runtime and you will see there aren't any special methods beyond what I have described. Regards, Tom Ogilvy john wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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...eio/fileio.asp File Access with Visual Basic® for Applications Regards, Tom Ogilvy john wrote in message ... 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? Tom Ogilvy wrote in message ... What do you know about the file. Are all lines exactly the same length? There is no magic to reading a file - it is a sequential read in most cases. A file is one to n bytes. The concept of a line is defined in Windows by encountering a crlf combination. Anything that would be faster would involve calculating where to put the file pointer and if your lines aren't equal length, the alternative is to read the data and count the lines. That said, you can read the 1 to n bytes into a string and then loop backwards from the eof. Look at the objects/properties/methods of textstream in the scripting runtime and you will see there aren't any special methods beyond what I have described. Regards, Tom Ogilvy john wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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 john wrote: 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! Dave Peterson wrote in message ... I searched google in the *scripting* newsgroup and found this: http://groups.google.com/groups?thre...%40tkmsftngp10 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. john wrote: 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. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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" wrote in message ... 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" wrote in message ... I searched google in the *scripting* newsgroup and found this: http://groups.google.com/groups?thre...%40tkmsftngp10 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. john wrote: 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. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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 wrote in message ... 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" wrote in message ... I searched google in the *scripting* newsgroup and found this: http://groups.google.com/groups?thre...%40tkmsftngp10 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. john wrote: 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. -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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 Jim Vita wrote in message ... 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSystemObject to get last 10 lines of text file.
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to read all lines from a text file into cells C10:Cxxx ? | Excel Discussion (Misc queries) | |||
Copying certain lines from a text file | Excel Discussion (Misc queries) | |||
text box insert in Excel - text box lines print on second copy | Excel Discussion (Misc queries) | |||
Some lines of text in Excel field do not wrap text. Please help. | Excel Discussion (Misc queries) | |||
Scripting.FileSystemObject | Excel Discussion (Misc queries) |