EOF indicator from .txt importing into Access2003 tables

[Guest]

I am importing a Monarch created .txt file into Access2003. The table is now
showing the EOF indicator, whereas in Access97 it did not. We have just
converted to Access2003. How do I turn off or get rid of this indicator? It
is now popping up in alot of my databases.

 

[John Nurick]

Hi Nancy,

The last time someone asked this question there was a lot of confusion
about what they meant by "EOF indicator". Please explain exactly what
you mean by it, and someone will be able to help get rid of it.

 

[Guest]

What I am calling a EOF indicator or end of file indicator is a character, or
a square zero, sometime it is a carriage return indicator. It is being
placed on the next line at the end of the .txt file.

 

[John Nurick]

Here's a VBScript that will probably fix it. Paste it into an empty text
file and save it as "FixEOF.vbs". You can

-use it from the command line, e.g.
Cscript "C:\Folder\FixEOF.vbs" "C:\Other folder\My text file.txt"

-drag a text file from Windows Explorer and drop it onto the FixEOF.vbs
icon in a folder (or put a shortcut on your desktop and drag and drop to
that)

-modify the code to use as a procedure in your Access VBA code.

 

[John Nurick]

(forgot to paste the code in the first time!)

Here's a VBScript that will probably fix it. Paste it into an empty text
file and save it as "FixEOF.vbs". You can

-use it from the command line, e.g.
Cscript "C:\Folder\FixEOF.vbs" "C:\Other folder\My text file.txt"

-drag a text file from Windows Explorer and drop it onto the FixEOF.vbs
icon in a folder (or put a shortcut on your desktop and drag and drop to
that)

-modify the code to use as a procedure in your Access VBA code.

'-----------START OF CODE------------------
Option Explicit
'FixEOF.vbs
'If a text file ends with ctrl-Z or ctrl-D, drop it.
'If the result ends with a blank line, dump the line.
'Ctrl-Z: DOS EOF marker
'Ctrl-D: PostScript EOF marker

Const BAK_EXT = ".bak"
Dim fso 'FileSystemObject
Dim fF 'File
Dim fIn, fOut 'Textstreams
Dim strLine 'String
Dim blNotFirstLine 'Boolean

If WScript.Arguments.Count = 1 Then
Set fso = CreateObject("Scripting.FileSystemObject")
Set fF = fso.GetFile(WScript.Arguments(0))

'If backup file aready exists, delete it
If fso.FileExists(fF.Path & BAK_EXT) Then
fso.DeleteFile fF.Path & BAK_EXT
End If

fF.Name = fF.Name & BAK_EXT
Set fIn = fF.OpenAsTextStream(1) 'ForReading
Set fOut = fso.CreateTextFile(WScript.Arguments(0))

strLine = ""
blNotFirstLine = 0

Do Until fIn.AtEndOfStream 'Read line by line
If blNotFirstLine Then
fOut.WriteLine strLine
End If
blNotFirstLine = -1
strLine = fIn.ReadLine
Loop

'We now have the last line of the file in strLine
Select Case Right(strLine, 1)
Case Chr(4), Chr(26) 'ctrl-D, ctrl-Z
strLine = Left(strLine, Len(strLine) - 1)
End Select
If Len(strLine) > 0 Then
fOut.WriteLine strLine
'this leaves a CrLf at the end of the file.
'to omit it, change WriteLine to Write.
End If

'Tidy up
fIn.Close
fOut.Close
Else
MsgBox "This script removes EOF marker from a text file. " _
& vbcrlf & "Drag a file onto the icon to process it, or use the" _
& vbcrlf & "command line. " & vbcrlf _
& vbcrlf & "Original file will be renamed with " _
& ".bak extension", 0, "FixEOF.VBS"
End If