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!!!

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?

--
Regards,
Tom Ogilvy


"JM" wrote in message
...
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!!!

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!!

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.

--
Regards,
Tom Ogilvy


"JM" wrote in message
...
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!!

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.



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"JM" wrote in message
...
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 wrote:
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/de...ng03092004.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.

--