PC Review


Reply
Thread Tools Rate Thread

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

 
 
AccessARS
Guest
Posts: n/a
 
      8th Feb 2008
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.

 
Reply With Quote
 
 
 
 
Albert D. Kallal
Guest
Posts: n/a
 
      8th Feb 2008
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


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)


 
Reply With Quote
 
 
 
 
Ralph
Guest
Posts: n/a
 
      8th Feb 2008
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

"AccessARS" wrote:

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

 
Reply With Quote
 
Douglas J. Steele
Guest
Posts: n/a
 
      8th Feb 2008
You could, but why introduce the overhead of FSO when it's not required?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ralph" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "AccessARS" wrote:
>
>> 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.
>>



 
Reply With Quote
 
Ralph
Guest
Posts: n/a
 
      8th Feb 2008
Yes I've seen the following, however I still like the flexibility. To each
his own.

http://vbnet.mvps.org/index.html?cod...apicompare.htm

"Douglas J. Steele" wrote:

> You could, but why introduce the overhead of FSO when it's not required?
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Ralph" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > "AccessARS" wrote:
> >
> >> 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.
> >>

>
>
>

 
Reply With Quote
 
Douglas J. Steele
Guest
Posts: n/a
 
      8th Feb 2008
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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ralph" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Yes I've seen the following, however I still like the flexibility. To each
> his own.
>
> http://vbnet.mvps.org/index.html?cod...apicompare.htm
>
> "Douglas J. Steele" wrote:
>
>> You could, but why introduce the overhead of FSO when it's not required?
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Ralph" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> > "AccessARS" wrote:
>> >
>> >> 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.
>> >>

>>
>>
>>



 
Reply With Quote
 
AccessARS
Guest
Posts: n/a
 
      8th Feb 2008
Yes, it has 5000 to 10000+lines. Thank you

"Albert D. Kallal" wrote:

> 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
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
>
>
>

 
Reply With Quote
 
Ralph
Guest
Posts: n/a
 
      8th Feb 2008
I said I liked the flexibility of FSO nowhere did I say that it was more
flexible than the his solution, just an alternative.

"Douglas J. Steele" wrote:

> 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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Ralph" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Yes I've seen the following, however I still like the flexibility. To each
> > his own.
> >
> > http://vbnet.mvps.org/index.html?cod...apicompare.htm
> >
> > "Douglas J. Steele" wrote:
> >
> >> You could, but why introduce the overhead of FSO when it's not required?
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Ralph" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
> >> >
> >> > "AccessARS" wrote:
> >> >
> >> >> 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.
> >> >>
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
AccessARS
Guest
Posts: n/a
 
      8th Feb 2008
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: ...."

"Albert D. Kallal" wrote:

> 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
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
>
>
>

 
Reply With Quote
 
M.L. Sco Scofield
Guest
Posts: n/a
 
      9th Feb 2008
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" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%(E-Mail Removed)...
> 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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Ralph" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Yes I've seen the following, however I still like the flexibility. To
>> each
>> his own.
>>
>> http://vbnet.mvps.org/index.html?cod...apicompare.htm
>>
>> "Douglas J. Steele" wrote:
>>
>>> You could, but why introduce the overhead of FSO when it's not required?
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "Ralph" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>> > 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
>>> >
>>> > "AccessARS" wrote:
>>> >
>>> >> 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.
>>> >>
>>>
>>>
>>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looping through file names of a csv and then through other csv Matt P Microsoft Excel Programming 4 31st May 2009 10:56 AM
Find retrieve text from a text file through VBA AccessARS Microsoft Access VBA Modules 0 8th Feb 2008 02:38 AM
text file not created while running a batch file through access sujith.thodi Microsoft Access VBA Modules 0 9th Apr 2007 09:03 AM
Now cannot connect to internet through LAN desktop though can through wireless laptops Chris Windows XP Networking 1 12th Mar 2004 05:20 AM
I can only get a IE web page through Outlook, not through IE alone. Nick Windows XP Internet Explorer 1 8th Dec 2003 05:07 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:46 AM.