Find/retrieve text from a .txt file through Access/VBA

[AccessARS]

We have a proprietary system that generates daily processing logs in text
files into a specific folder. These log files have a systematic naming
convention i.e “Test_20080206 LOG.TXTâ€, “Test_20080207 LOG.TXT†depending on
the date. I am building a system in Access/VBA that will search for a line
with a specific error message within the most current log file thereafter
store that line item into a table for reporting purposes in Access.

Example of error line in log text file:
“09:51:37 I Processing stopped by user.â€

I can identify the most recent file and point to the location on a daily
basis in vba but can’t seem to find any solid information on opening,
searching and pulling text from the said text file. I’ve heard that there
are methods for going through a text file line by line but have no clue where
to start. If someone can give me a little direction I should be able to pull
things together from there.
Thank you in advance for your assistance.

 

[Albert D. Kallal]

Does the file have "many" lines of text, or just one line of text??

You can open a text file as follows:

Sub ReadTextFile

Dim strFile As String
Dim intF As Integer
Dim strLineBuf As String
Dim lngLines As Long
Dim lngBlank As Long

strFile = "c:\my data\MyData.txt"

intF = FreeFile()
Open strFile For Input As #intF

Do While EOF(intF) = False
Line Input #intF, strLineBuf
If Trim(strLineBuf) = "" Then
lngBlank = lngBlank + 1
Else
lngLines = lngLines + 1
End If
Loop
Close intF

End If

MsgBox "Number non blank lines = " & lngLines & vbCrLf & _
"Blank lines = " & lngBlank & vbCrLf & _
"Total = " & lngBlank + lngLines

End Function

You can also use the split() command to parse out the one line if it is
delimonted...

And, you could simply add to the above loop code add the one record to a
file


dim rstData as dao.RecordSet

set rstData = currentdb.OpenRecordSet("tblMyData")
rstData.AddNew
rstData!FristName = "some text"
rstData!LastName = "some more text"
rstData.Update
rstData.Close

 

[Ralph]

You could also use FileSystemObject

sub readTxtFile
dim fso
dim f
dim ts
dim strLine as string

set fso=CreateObject("Scripting.FileSystemObject")
set f=fso.getfile("c:\temp\Test_20080206 LOG.TXT")
set ts=f.OpenAsTextStream

'loop through each line in text file
do while not ts.AtEndfOfLine
strLine=ts.readline
'test for the line your looking for here
debug.print strline
loop

ts.close
set fso=nothing
set f=nothing
set ts=nothing
end sub

 

[Douglas J. Steele]

You could, but why introduce the overhead of FSO when it's not required?

 

[Ralph]

Yes I've seen the following, however I still like the flexibility. To each
his own.

http://vbnet.mvps.org/index.html?code/fileapi/fsoapicompare.htm

 

[Douglas J. Steele]

Why do you say your approach is more flexible than the one Albert proposed?

Just as yours does, his code reads one line at a time until reaching the end
of the file. The only difference I see is that he's using only built-in VBA
functionality.

 

[AccessARS]

Yes, it has 5000 to 10000+lines. Thank you

 

[Ralph]

I said I liked the flexibility of FSO nowhere did I say that it was more
flexible than the his solution, just an alternative.

 

[AccessARS]

Thank you Albert... this is exactly the lead I needed.

Once I find the line that I am searching for I need to also retrieve the
information from another line which is usually
"09:51:04 I Processing index line: ...."

 

[M.L. Sco Scofield]

Personally, I like the code on the website Ralph referenced.

Why in the world would you use built-in VBA functionality when you can have
the complexity of Windows API calls or have the dependency on external
objects like the FSO?

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
This email made with 100% recycled electrons!
Miscellaneous Access "stuff" at www.ScoBiz.com

 

[Douglas J. Steele]

You forgot the smiley, Sco! <g>

See near the bottom of http://my.advisor.com/articles.nsf/aid/16279

"As you can see, in finding 5491 files on my hard drive, the API approach
was about 10 times faster than the Dir function (.191 seconds vs. 1.222
seconds), while the Dir function was about 10 times faster than the FSO
approaches (1.222 seconds vs. 10.285 or 10.295 seconds). When I modified the
routines to look for more than simply MP3 files, this relationship was
repeated."

 

[M.L. Sco Scofield]

Smiley? I was serious Doug. I bill by the hour. The more code I write, the
longer it takes. The longer it takes, the more I bill. API calls take the
most code hence maximizing billings.

The downside of API calls is that they reduce library dependences. Trouble
shooting missing references has always been a great billing padder.

<g,d,&r>

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
This email made with 100% recycled electrons!
Miscellaneous Access "stuff" at www.ScoBiz.com

 

[Douglas J. Steele]

Not sure why I'd want to talk to you, since you never answered my email
telling you I was passing through Denver! <g>

 

[M.L. Sco Scofield]

You mean the one from 11/14/06 where you said you were passing through and
wouldn't be here long enough to even call? It's in my "Important -
Follow-up" folder. Was going to answer it after I finished a class. Dang,
time flies when you're havin' fun...

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
This email made with 100% recycled electrons!
Miscellaneous Access "stuff" at www.ScoBiz.com