VBA code to replace nulls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone have a VBA routine that will read each character in a text file,
and if it is a null character change it to a space character?
Please keep it simple as I am new to VBA programming
 
What kind of text file are you dealing with?
The problem is that you will not find a Null within a string of text. For
example
X= "ABC" & Null & "DEF" becomes "ABCDEF" and Len(X) will return 6.
So, a meomory varialble or a table/query field will either be a string
value, a numeric value, or Null. They will not be mixed.

Also be aware the only memory variable that can contain Null is a Variant.
There is a function to test for a null value and return a replacement value,
the Nz function (Null or Zero). There is also a function to determine if a
variable or field is null, the IsNull function. It returns True (-1) if it
is null or False (0) if it is not.

Dim varSomeVariable as Variant

'The following two examples do the same thing

varSomeVariable = Null
If IsNull(varSomeVariable) Then
varSomeVariable = " "
End If

varSomeVariable = Nz(varSomeVariable, " ")

If you need more help, post back with some particulars on how you are
getting the text in.
 
What I meant is that I am trying to strip all null characters (or actually
replace them with a space character) in a .txt file, prior to importing the
..txt file into an Access database as a fixed length table. I know how the
data should look and I have the import specifications written, it's the null
characters that are causing the problems.
 
You can't find a Null in a string of text. The text first has to be broken
down into fields or variables based on the delimiter being used. What kind
of error are you getting? If your import spec is correctly identifiying the
delimiters, then you should end up with a Null value in one of the imported
fields. That should not be a problem unless you have the receiving field's
Allow Zero Length and Required propertys set incorrectly.

Please post more detail, Thanks.
 
You can find a Null in a string of text, if you use the ASCII character codes
and use the hex code of "00" to find it. See the ASCII character codes. Dec.
= 0, Hex. = 00.
 
Sorry, Mike, but you are absolutely wrong. Hex 00 is 0 which is not Null.
Look as VB Help for the Hex function and the Asc function. If you try to use
the Asc on a Null character, you get an error (94 - Invalid use of Null),
Hex(Null) returns Null.
IsNull(Hex("0") ) returns False. IsNull(Hex("0") ) also returns False

There are 256 codes (0 - 255) in the ASCII character set, none of which
evalutes to Null

I know you would like there to be a Null in a string, wish I could help.

Work it out yourself. You would obviously rather argue than work on a
solution.
 
The MSDN ASCII Character Code chart shows The Hex 00 as being Nul.
It works in the following:
strOldFile = "C:\Text\TYCOText\RawFiles\H5213.txt"
strNewFile = "C:\Text\TYCOText\RawFiles\5213.txt"

Set fs = CreateObject("Scripting.FileSystemObject")

If (fs.FileExists(strOldFile)) Then
Open strOldFile For Binary As #1
fileLength = LOF(1)
ReDim charArray(fileLength) As Byte
Get #1, , charArray
Close #1
For i = 1 To fileLength
If (charArray(i) = &H0) Then
charArray(i) = &H20
End If
Next i
Open strNewFile For Binary As #1
Put #1, , charArray
Close #1
Else
MsgBox "I do not have a valid path for the input file that was
downloaded from the mainframe. Please provide a valid path!"
End If
The reason I can not use this routine is the in some cases the string length
is too long.
That is why I am looking for something to read and change each character in
the entire file as needed.
 
Sorry, Mike, I don't have a good idea on this one. Is it possible you could
use the ReadLine method, or are there no crlf characters in the file? I have
to admit a lack of experience reading binary text directly.
 
I needed to do the same thing. This seems to do the trick:

Sub StripNulls(strFileIn As String, strFileOut As String)

Dim iFileIn As Integer
Dim iFileOut As Integer
Dim strChar As String

iFileIn = FreeFile
Open strFileIn For Binary As iFileIn

iFileOut = FreeFile
Open strFileOut For Binary As iFileOut

Do Until EOF(iFileIn)
strChar = Input(1, #iFileIn)
If strChar = Chr(0) Then
strChar = " "
End If
Put #iFileOut, , strChar
Loop

Close iFileIn
Close iFileOut

End Sub


I think if you replaced your If statement with the following, yours
might work:

Dim byt as Byte

If (fs.FileExists(strOldFile)) Then
Open strOldFile For Binary As #1
fileLength = LOF(1)
Open strNewFile For Binary As #2

For i = 1 To fileLength
Get #1, i, byt
If (byt = &H0) Then
byt = &H20
End If
Put #2, , byt
Next i
Close #1
Close #2

Else
MsgBox "I do not have a valid path for the input file that was
downloaded from the mainframe. Please provide a valid path!"
End If
 
Back
Top