Delete all, but the last 23 lines of an external text file

[Charlotte E.]

Hi Guys,


I have a workbook that write a new line to an external (text file) log each
day.
This works fine no problem here...

But we really only need to be able to view the lines for the past month - in
fact even only the last 23 lines in the log file, since there are never more
than 23 workdays in a month.

So, how to delete all but the last 23 rows in an external text files, using
VBA?


TIA,

CE

 

[Harald Staff]

Hi Charlotte

One way, simplified to three lines and without a saving procedure:

Sub test()
Dim iFnum As Integer
Dim Linje As String
Dim S1 As String, S2 As String, S3 As String
iFnum = FreeFile
Open "C:\Temp\Test.txt" For Input As #iFnum
While Not EOF(iFnum)
Line Input #iFnum, Linje
S1 = S2
S2 = S3
S3 = Linje
Wend
Close #iFnum
' save to original file instead of
MsgBox S1 & vbNewLine & S2 & vbNewLine & S3
End Sub

Beste hilsen Harald

 

[Charlotte E.]

Bingo!
Working

Thanks, Harald


CE

Hi Charlotte

One way, simplified to three lines and without a saving procedure:

Sub test()
Dim iFnum As Integer
Dim Linje As String
Dim S1 As String, S2 As String, S3 As String
iFnum = FreeFile
Open "C:\Temp\Test.txt" For Input As #iFnum
While Not EOF(iFnum)
Line Input #iFnum, Linje
S1 = S2
S2 = S3
S3 = Linje
Wend
Close #iFnum
' save to original file instead of
MsgBox S1 & vbNewLine & S2 & vbNewLine & S3
End Sub

Beste hilsen Harald

 

[GS]

Another way that doesn't pad with empty lines...

Sub TrimLinesFromFile()
Dim vText, n&
vText = Split(ReadTextFileContents(sFile), vbCrLf)
If UBound(vText) < 23 Then Exit Sub

Const sFile$ = "C:\Temp\Test.txt" '//edit to suit
For n = 0 To UBound(vText) - 22
vText(n) = "~"
Next 'n
vText = Filter(vText, "~", False)
WriteTextFileContents Join(vText, vbCrLf), sFile
End Sub

...which uses the following reusable helper routines to read from and
write back to text files the entire contents in one shot. The loop
assumes your file gets the daily input 'appended' to existing content
and so removes the oldest entries (at the top of the file). If
UBound(vText) does not exceed 22 then there's nothing to do so the code
below that line will only run when the file contains more than 23
lines.

*It's very important that there are no empty lines at the end of the
file for this to work! Also, as written there will never be more than
23 lines in the file.*


<Helper routines>

WriteTextFileContents does not insert a line feed so your file contains
no blank lines at the end.

Function ReadTextFileContents(Filename As String) As String
' Reads large amounts of data from a text file in one single step.
Dim iNum As Integer
On Error GoTo ErrHandler
iNum = FreeFile(): Open Filename For Input As #iNum
ReadTextFileContents = Space$(LOF(iNum))
ReadTextFileContents = Input(LOF(iNum), iNum)

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Function 'ReadTextFileContents()

Sub WriteTextFileContents(TextOut As String, _
Filename As String, _
Optional AppendMode As Boolean = False)
' Reusable procedure that Writes/Overwrites or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**
Dim iNum As Integer
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then
Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut;
Else
Open Filename For Output As #iNum: Print #iNum, TextOut;
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFileContents()

</Helper routines>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Auric__]

Function ReadTextFileContents(Filename As String) As String [snip]
Sub WriteTextFileContents(TextOut As String, _

Y'know, upon seeing your code, it occurs to me that I never thought to move
that code into functions myself. Sigh.

 

[Auric__]

Another way that doesn't pad with empty lines...

Sub TrimLinesFromFile()
Dim vText, n&
vText = Split(ReadTextFileContents(sFile), vbCrLf)
If UBound(vText) < 23 Then Exit Sub

Const sFile$ = "C:\Temp\Test.txt" '//edit to suit
For n = 0 To UBound(vText) - 22
vText(n) = "~"
Next 'n
vText = Filter(vText, "~", False)
WriteTextFileContents Join(vText, vbCrLf), sFile
End Sub

*It's very important that there are no empty lines at the end of the
file for this to work! Also, as written there will never be more than
23 lines in the file.*

This version ignores empty lines at the end of the file, makes it easier to
change the number of lines that are kept, and fixes a couple bugs. ;-)

Sub TrimLinesFromFile()
Dim vText, n&, fnum&
Const sFile$ = "C:\Temp\Test.txt" '//edit to suit
Const maxLines = 23
vText = Split(ReadTextFileContents(sFile), vbCrLf)
For n& = UBound(vText) To 0 Step -1
If "" <> vText(n&) Then
If n& < UBound(vText) Then ReDim Preserve vText(n&)
Exit For
End If
Next
If n& < 1 Then Exit Sub 'text file only contains blank lines
If UBound(vText) < maxLines Then Exit Sub 'text file is < 23 lines long

For n& = 0 To UBound(vText) - maxLines
vText(n&) = "~"
Next 'n
vText = Filter(vText, "~", False)
WriteTextFileContents Join(vText, vbCrLf), sFile
End Sub

 

[GS]

Function ReadTextFileContents(Filename As String) As String [snip]
Sub WriteTextFileContents(TextOut As String, _

Y'know, upon seeing your code, it occurs to me that I never thought
to move that code into functions myself. Sigh.

Well, I use these in every app because I don't use the Registry to
store anything. This keeps my stuff 'fully portable' in that all app
settings/configs are stored in text files. I suspect you do same for
routines you keep using over and over. I just drop modules containing
what I call 'default' or 'core' routines into VBA projects when I first
create them. It's unfortunate that VBA components don't ref their
source files as do VB components, huh!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Auric__]

Function ReadTextFileContents(Filename As String) As String [snip]
Sub WriteTextFileContents(TextOut As String, _

Y'know, upon seeing your code, it occurs to me that I never thought
to move that code into functions myself. Sigh.

Well, I use these in every app because I don't use the Registry to
store anything. This keeps my stuff 'fully portable' in that all app
settings/configs are stored in text files.

AFA settings are concerned, I use standard .ini files, read from & written to
using the standard Get/WritePrivateProfileString WinAPI calls. ;-)

I suspect you do same for
routines you keep using over and over. I just drop modules containing
what I call 'default' or 'core' routines into VBA projects when I first
create them. It's unfortunate that VBA components don't ref their
source files as do VB components, huh!

I meant *specifically* reading & writing to text files in their entirety. I
*always* retype that code without thinking about it. (Yeah, yeah, I know,
that's what functions are for.)

 

[GS]

Uh.., couple of things worth mentioning here.<g>

<1st>
I'm not sure why you use the type symbol everywhere the var is used:

Since
Dim n&, fnum&

is the same as

Dim n As Long, fnum As Long

we wouldn't write...

For n As Long =...
or
vText(n As Long)
</1st>

<2nd>
I had occasion to strip trailing blank lines from 1000s of CNC program
files and I did same way using Filter(), only starting with UBound
using Step -1 until I got an element with Len(). CNC prog files are
never empty, but every time a controller writes back edited files the
default line feed was added. That's why my write sub prepends a vbCrLf
to the output string if AppendMode. I never thought to use Redim
Preserve as you do here. I'm definitely going to review that old code
now, after reading your approach!<g>
</2nd>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

AFA settings are concerned, I use standard .ini files, read from &

written to
using the standard Get/WritePrivateProfileString WinAPI calls.

I use ini files too and so also the PrivateProfile functions via a
wrapper module I got from Rob Bovey way back in my early days of VBA
development. I 'mostly' use INIs for default app settings only, and
only write to INI during update/upgrade installs. The apps read INI at
startup.

I was referring, though, to user-defined settings/configs stored in
UDTs during runtime. I use a separate file for each group, and use
'Get_FromTextFile' function and 'Put_InTextFile' sub because these are
binary files. (When there's only a few values to store I may just use
the INI if inclined to do so!<g>)

I also store my app license profiles in 2 different encrypted 'dat'
files. These apps use an automated instance instantiated by a VB6
frontloader EXE, which validates licensing before doing anything else.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Auric__]

Uh.., couple of things worth mentioning here.<g>

<1st>
I'm not sure why you use the type symbol everywhere the var is used:

Since
Dim n&, fnum&

is the same as

Dim n As Long, fnum As Long

we wouldn't write...

For n As Long =...
or
vText(n As Long)
</1st>

Because I've used BASICs where n& and n are *not* the same variable. (I can't
think of which ones offhand, sorry.) I try to be consistent: everything that
uses a type symbol *anywhere*, uses it *everywhere*. (Of course, I don't
*usually* use type symbols on variables, preferring to Dim As type. And of
course, I missed the $ at the declaration of sFile, or else I would've
changed all occurrences to sFile$.)

<2nd>
I had occasion to strip trailing blank lines from 1000s of CNC program
files and I did same way using Filter(), only starting with UBound
using Step -1 until I got an element with Len(). CNC prog files are
never empty, but every time a controller writes back edited files the
default line feed was added. That's why my write sub prepends a vbCrLf
to the output string if AppendMode. I never thought to use Redim
Preserve as you do here. I'm definitely going to review that old code
now, after reading your approach!<g>
</2nd>

One thing I noticed in your code that I wouldn't do myself is this:

For n& = 0 To UBound(vText) - maxLines
vText(n&) = "~"
Next 'n
vText = Filter(vText, "~", False)
WriteTextFileContents Join(vText, vbCrLf), sFile

ISTM that going through the array, changing everything except what you need,
is a waste of CPU cycles (especially with strings). Instead, I would've just
pulled out the elements I actually needed and ignored the rest:

Open outputfile$ For Output As fnum&
For n& = (UBound(vText) - (maxLines - 1)) To UBound(vText)
Print #funm&, vText(n&)
Next
Close fnum&

This could be even moved to a subroutine, something like this:

Sub array1DSliceToFile(filepath As String, arr As Variant, _
startEl As Variant, endEl As Variant)
Dim fnum As Long, L0 As Variant
'some input sanitation...
If VarType(arr) >= vbArray Then
If VarType(startEl) = 0 Then startEl = LBound(arr)
If VarType(endEl) = 0 Then endEl = UBound(arr)
If startEl > endEl Then Exit Sub
If startEl > UBound(arr) Then Exit Sub
If startEl < LBound(arr) Then startEl = LBound(arr)
If endEl < LBound(arr) Then Exit Sub
If endEl > UBound(arr) Then endEl = UBound(arr)
End If
If Len(filepath) < 1 Then Exit Sub
'end sanitation
fnum = FreeFile
Open filepath For Output As fnum
If VarType(arr) < vbArray Then
Print #fnum, arr
Else
For L0 = startEl To endEl
Print #fnum, arr(L0)
Next
End If
Close fnum
End Sub

(I can't believe I just spent 45 minutes on this.)

....called like so...

array1DSliceToFile sFile$, vText, (UBound(vText) - maxLines) + 1, _
UBound(vText)

 

[GS]

One thing I noticed in your code that I wouldn't do myself is this:

For n& = 0 To UBound(vText) - maxLines
vText(n&) = "~"
Next 'n
vText = Filter(vText, "~", False)
WriteTextFileContents Join(vText, vbCrLf), sFile

ISTM that going through the array, changing everything except what
you need,
is a waste of CPU cycles (especially with strings). Instead, I
would've just
pulled out the elements I actually needed and ignored the rest:

<1>
I have never found writing a text file one line at a time to be very
efficient at best. I realize this depends on the number of lines in the
file, but since this will always be unknown at design time I'd opt for
using more a efficient approach.

<2>
The point of replacing the elements to be removed by the Filter()
function is because it needs that to work for using a common 'Find'
string in all elements to be filtered.

<3>
The way I did this requires way less code and is highly efficient
regardless of file length. (IMO)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Because I've used BASICs where n& and n are *not* the same variable.

(I can't
think of which ones offhand, sorry.) I try to be consistent:
everything that
uses a type symbol *anywhere*, uses it *everywhere*. (Of course, I
don't
*usually* use type symbols on variables, preferring to Dim As type.
And of
course, I missed the $ at the declaration of sFile, or else I
would've
changed all occurrences to sFile$.)

I thought this deserved a separate reply...

The type symbols are attritional as a result of team development. I
fought for 'As <type>' long and hard because it a better
'self-documenting' way to program. Habits are hard to break, though,
and use of the symbols has evolved into a habit for me now. I'm
comfortable using them but I worry that OPs and other readers may not
understand them and thus an element of unclarity (is that a word?) in
the code offerings I post.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion