Low Values - import problems

[thebadger]

I am attempting to import a .txt file which has been exported from a mainframe dataset.

I am able to read the data and parse it correctly and in most cases I can then insert the details onto access tables.

I am having problems with some rows however - vba reports an error saying that the length of the data to be inserted is too big for the field. As I know the lengths are correct the problem is to do with the data. When I open the .txt file in notepad I can see just spaces however in textpad I can see a black line - attempts to copy/cut this item fail - reports that Null values cannot be copied.

I think the problem is that the mainframe file contains 'low value' hex 00. This for some reason is causing access to think that the item to be inserted is too big.

I was wondering can I use the replace command to change these 'characters' to spaces - if so does anyone know what I would be searching for because if I use " " then it will take forewer. The files has over 9000 rows with strings up to 10000 bytes and there are only a few examples of these 'characters'

 

[thebadger]

Answer

Can't seem to be able to do it with VBA. Managed to get round it by using a text editor that allowed find and replace for Hex

 

[tkarpf]

I'm afraid I only have part of the solution, but it should work.

What you need to do is read through your file in chunks and feed it through this function. You're probably going to want to pass vLeaveCRLF as TRUE.

I don't have an example of the code that calls this. I vaguely remember that I opened the file for input as Binary, but I can't find my sample code.

Anyway, this will clear the junk out of the pieces of file that you pass to it. Just write the resulting file out to a new file.



Function StripLowValues(ByVal sInputString As String, Optional vLeaveCRLF As Variant) As String

'Converts Low Value characters (chr(0) to chr(31)) to spaces within a string
Dim iPos As Integer
Dim icount As Integer
Dim sCharToFind

If IsMissing(vLeaveCRLF) Then
'also convert CarriageReturn and LineFeed
For icount = 0 To 31
sCharToFind = Chr(icount)
iPos = InStr(sInputString, sCharToFind)
While iPos > 0
sInputString = Left(sInputString, iPos - 1) & " " & Mid(sInputString, iPos + 1)
iPos = InStr(sInputString, sCharToFind)
Wend
Next icount
Else
'keep CarriageReturn and LineFeed characters in place
For icount = 0 To 31
'We want to keep CarriageReturn and LineFeed in place
If icount = 10 Or icount = 13 Then
icount = icount + 1
End If
sCharToFind = Chr(icount)
'This is probably not the best way of doing this, but it works.
iPos = InStr(sInputString, sCharToFind)
While iPos > 0
sInputString = Left(sInputString, iPos - 1) & " " & Mid(sInputString, iPos + 1)
iPos = InStr(sInputString, sCharToFind)
Wend
Next icount
End If

StripLowValues = sInputString

End Function