Parse through a TXT file in reverse order

[JM]

Hi!

I need to parse through a text file in reverse order using VBA. Any ideas
on how to do this? The text file can grow very large (>10GB), so starting
at the beginning and inputting all the way to the end is not an attractive
option.

I'm trying to find the most recent ten occurrences of a certain message in
the log (which would be the last ten occurrences of said message in the
log).

I can handle the instr to find the message, and an array to store the
results, it's the backwards stepping that I can't figure out.

Thanks!!!

 

[Tom Ogilvy]

How are you reading your data in? (code example perhaps)

how do you determine if it is a certain message

is a message all on one line?

 

[JM]

Hi Tom,

I didn't want to post code because I'm not tied to any one approach, and
didn't want to limit anyone's creativity. I typically use the Open for
Input approach, but am open to FSO too.

If parsing a file forwards, I typically would do something like this:

Do Until EOF(in)
Line Input #in, sLine
If Instr(1,sLine,"critical") > 0 then
'(print the record to a worksheet or another text file)
End If
Loop

This particular log file is too big for this approach. I thought about
setting the seek point equal to the filelen, but I don't know how long a
line is, and where to set it for the prior line, and then to the line prior
to that, and so on.

Thanks!!

 

[Tom Ogilvy]

Dim vArr() as String
ReDim vArr(1 to 1000)
Dim i as Long, j as Long

i = 1
Do Until EOF(in)
Line Input #in, sLine
If Instr(1,sLine,"critical") > 0 then
varr(i) = sLine
i = i + 1
End If
Loop

for j = i-1 to j-10 step -1
debug.print print varr(j)
Next


if your lines are fixed length, then you might be able to handle it as a
random access file. If not, then the sequential method above is what I can
think about. You might be able to approach it as an ADO record set and
then query it as a database, but I haven't done any work in that area.

 

[JM]

It looks like your code is starting at the beginning and is moving in
forward order..? These log files get so big that I was hoping to start at
the end and move backwards.

 

[Jamie Collins]

You might be able to approach it as an ADO record set and
then query it as a database, but I haven't done any work in that

area.

See:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp

Note that a row in a text file cannot be amended (UPDATE SQL DML) or
deleted (DELETE) using SQL/ADO, but can be queried (SELECT) and new
rows appended to the end of the file (INSERT INTO).

Jamie.

--