Hex 0D 0A 1A at end of imported file

D

Dorian

I need to import a fixed-width file into an Access database.
The file is generated from a mainframe system that appends special
carriage-return-linefeed characters (hex 0D 0A 1A) to the file. When the
import is done, an extra row is placed at the end of the imported table.
This forces me to specify the first field as 'text' otherwise I get a type
conversion error.
Is there any way to tell the Access import to ignore these characters?
I see you can specify a 'code page' in the import spec but I dont know what
effect this has.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
K

kc-mass

Hi Dorian

You might want to preprocess the text file and strip off the offending
characters.
Then use the new file for import.

Try something like:

Function ReadWriteTextFile()
Const ForReading = 1, ForWriting = 2
Dim fso As Object
Dim FileIn As Object
Dim FileOut As Object
Dim strCurrentLine As String
Dim strOffending As String
'put the real offending characters here
strOffending = "X"
Set fso = CreateObject("Scripting.FileSystemObject")
Set FileOut = fso.CreateTextFile("c:\OutPut.txt", ForWriting, True)
Set FileIn = fso_OPENTextFile("c:\InPut.txt", ForReading)
Do While Not FileIn.AtEndOfStream
'Read a line of data
strCurrentLine = FileIn.ReadLine
'Strip of the offending characters
strCurrentLine = Left(strCurrentLine, InStr(strCurrentLine,
strOffending) - 1)
'write out the clean line to the new file
FileOut.WriteLine strCurrentLine
Loop
FileIn.Close
FileOut.Close
End Function


If the offending characters always appear in columns 78 to 80 use the len
finction instead.
Regards

Kevin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top