Replacing non printable characters

[Petermgr]

Hi gang,

I work with various address files that I recieve from clients for cleansing.
These can be text delimited, Excel, etc files. I import these files into
Access.

What I am looking for is a find/replace procedure that will find non
printable characters and replace them with a single space. I want to target
ASCII codes 1 to 31 and likely other codes such as #127.

Any tips would be much appreciated. BTW, I know a little bit about working
with modules and creating simple functions for query use on specific fields.
But I'm not very bright with modules outside simple function use . . .

Thanks

Peter

 

[Bob Quintal]

=?Utf-8?B?UGV0ZXJtZ3I=?= <[email protected]>
wrote in

Hi gang,

I work with various address files that I recieve from clients
for cleansing. These can be text delimited, Excel, etc files.
I import these files into Access.

What I am looking for is a find/replace procedure that will
find non printable characters and replace them with a single
space. I want to target ASCII codes 1 to 31 and likely other
codes such as #127.

Any tips would be much appreciated. BTW, I know a little bit
about working with modules and creating simple functions for
query use on specific fields. But I'm not very bright with
modules outside simple function use . . .

Thanks

Peter

Here is what I use. Note the funky (undocumented) use of mid().
It works in '97, 2000, 2003. I don't know about 2007.

Public Function CleanCtrl(byval str2clean as string) as string
dim lCurPos as long

for lCurPos = 1 to len(str2clean)
if asc(mid(str2clean,lCurPos,1))<32 then
mid(str2clean,lCurPos,1) = chr(32)
end if
next lCurPos
CleanCtrl = str2clean
end function

 

[Stefan Hoffmann]

hi Peter,

What I am looking for is a find/replace procedure that will find non
printable characters and replace them with a single space. I want to target
ASCII codes 1 to 31 and likely other codes such as #127.

You can use

a) Replace() for each character replace

or

b) something like

Public Function myReplace(AString As String) As String

Dim Count As Long

myReplace = AString
For Count = 1 To Len(myReplace)
Select Case Asc(Mid(myReplace, Count, 1))
Case 1 To 31, 127
Mid(myReplace, Count, 1) = " "
End Select
Next Count

End Function


mfG
--> stefan <--

 

[Bob Quintal]

=?Utf-8?B?UGV0ZXJtZ3I=?= <[email protected]>
wrote in

Thanks for the response Bob and Stefan, much appreciated!

Now here is a very stupid question:

The only way I know how to use these modules is by running an
update query on each field individually i.e.
"myReplace([Address_1]". However, since I deal with numerous
Tables each day, with clients providing various number of
fields, is there a way that I can apply the update to an
entire table, regardless of the field names or number of
fields? For example, if I simply renamed the table I am
working with to "Working", can I create and run a routine that
would search and replace all the cells in the "Working" table?

Hope that made sense . . .

Thanks again,

Peter

You could write code to open the table in a recordset, loop
through each record, and loop through the fields, testing if
it's a text type, and do the update if it is,

Debug this carefully before trusting on real data.

Private Sub FixCTRLchars()
'*********************************************
' Prompt user for the table name.
'process each field in each record to remove the
'control characters.
'*********************************************
Dim MyDB As Database, MyData As Recordset
Dim i As Integer, Msg As String, Title As String
Dim Defvalue As String, Answer As String

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Msg = "Enter table name."
Title = "Table Name?"
Defvalue = "Table1"
Answer = InputBox$(Msg, Title, Defvalue)
If Answer = "" Then Exit Sub
Set MyData = MyDB.OpenRecordset(Answer, dbOpenDynaset)
Do Until MyData.EOF
For i = 0 To MyData.Fields.Count - 1
If MyData.Fields(i).Type = dbText Then
MyData.Fields(i).Value = _
CleanCtrl(MyData.Fields(i).Value)
End If
Next i
Loop

MyData.Close
Set MyData = Nothing
Set MyDB = Nothing
End Sub

 

[Petermgr]

Thanks for the response Bob and Stefan, much appreciated!

Now here is a very stupid question:

The only way I know how to use these modules is by running an update query
on each field individually i.e. "myReplace([Address_1]". However, since I
deal with numerous Tables each day, with clients providing various number of
fields, is there a way that I can apply the update to an entire table,
regardless of the field names or number of fields? For example, if I simply
renamed the table I am working with to "Working", can I create and run a
routine that would search and replace all the cells in the "Working" table?

Hope that made sense . . .

Thanks again,

Peter

 

[Petermgr]

Thanks Bob

I created the module and ran it.

I was asked for the table name, select OK, then received the following error:

Run-time error '3020': Update or CancelUpdate without AddNew or Edit


Selecting Debug gives me these highlighted lines:

MyData.Fields(i).Value = _
myReplace(MyData.Fields(i).Value)

BTW, I am using 'myReplace' since it allows me to easily add additional
characters for search and replace . . .

I'm wondering as well if I don't have the right Declaration. I simply have
the default "Option Compare Database". That was the default Declaration when
I selected New for modules.



Thanks again, this is really helping solving a major headache . . .

Peter

 

[Bob Quintal]

=?Utf-8?B?UGV0ZXJtZ3I=?= <[email protected]>
wrote in

Thanks Bob

I created the module and ran it.

I was asked for the table name, select OK, then received the
following error:

Run-time error '3020': Update or CancelUpdate without AddNew
or Edit


Selecting Debug gives me these highlighted lines:

MyData.Fields(i).Value = _
myReplace(MyData.Fields(i).Value)

me bad. I adapted the code from a slightly different procedure
that didn't write back to the table, and forgot to include two
statements in the code. Without them the table is read-only.

Do Until MyData.EOF
myData.Edit '<----- was missing
For i = 0 To MyData.Fields.Count - 1
If MyData.Fields(i).Type = dbText Then
MyData.Fields(i).Value = _
CleanCtrl(MyData.Fields(i).Value)
End If
MyData.update '<----- was missing

Next i
Loop

BTW, I am using 'myReplace' since it allows me to easily add
additional characters for search and replace . . .

I'm wondering as well if I don't have the right Declaration. I
simply have the default "Option Compare Database". That was
the default Declaration when I selected New for modules.

That's a good one. You should also turn on the Option Explicit
From the VB window, Tools->Optons->Editor->Require Variable
declarations.

Thanks again, this is really helping solving a major headache
. . .

Peter

 

[Petermgr]

Thanks again Bob,

I did some research and did find information on the ***.Edit and ***.Update
lines. Thanks for confirming their requirement. Howevever, with the following
now entered:

----------------------------------------------------------------------------
Do Until MyData.EOF
MyData.Edit
For i = 0 To MyData.Fields.Count - 1
If MyData.Fields(i).Type = dbText Then
MyData.Fields(i).Value = _
myReplace(MyData.Fields(i).Value)
End If
Next i
MyData.Update
Loop
----------------------------------------------------------------------------

I get the following error when running the routine:

Run-time error '94':

Invalid use of Null

Debug hightlight:

MyData.Fields(i).Value = _
myReplace(MyData.Fields(i).Value)


Hate pestering you about this, but the solution seems so close at hand .
.. . Appreciate your time on this

Peter

 

[John W. Vinson]

Run-time error '94':

Invalid use of Null

Sounds like the MyReplace function does not accept Null values. Check to see
if the field is null before trying to update it.

John W. Vinson [MVP]

 

[Petermgr]

Hi John,

The fields are not null. There are about 5,000 records. Not all the field
cells are populated, but every field has at least one cell with information .
.. .

Thanks . . .

Peter

 

[John W. Vinson]

The fields are not null. There are about 5,000 records. Not all the field
cells are populated, but every field has at least one cell with information .

I'm sorry, I don't understand your jargon here. Fields are atomic; they don't
consist of "cells". If there are Access fields within the record which are not
populated, they are by definition NULL and your code will throw an error when
it tries to call the function with a NULL argument.

Try:

Do Until MyData.EOF
MyData.Edit
For i = 0 To MyData.Fields.Count - 1
If MyData.Fields(i).Type = dbText Then
If Not IsNull(MyData.Fields(i)) Then
MyData.Fields(i)= _
myReplace(MyData.Fields(i))
End If
End If
Next i
MyData.Update
Loop

John W. Vinson [MVP]

 

[Petermgr]

I'm sorry, I don't understand your jargon here. Fields are atomic; they don't

consist of "cells". If there are Access fields within the record which are not
populated, they are by definition NULL and your code will throw an error when
it tries to call the function with a NULL argument.

John W. Vinson [MVP]

Thanks John,

Yes there are some records where a Field may be null. My jargon was not clear.

I tried your suggestion, thank you.

So . . . . I run the code by selecting >Run>Run Sub/Userform. I'm prompted
for the table name and select okay. What I get now is an all white panel with
the title bar reading "Microsoft Visual Basic - Ver
07147B[running]-[1-t(Code)]. This panel does not disappear. I ran this last
night and the white panel is still showing. BTW, for testing I only have 3
records in Table1.

Selecting CTRL-ALT-DELETE shows 1 instance of Microsoft Visual Basic - Ver
07147B[running]-[1-t(Code)] and 1 instance of Microsoft Access. Both showing
as Not Responding. I then select End Task on each.

I also tried running the routine with a simple Function in place of
"myReplace". I got the same results. The simple Function I called on would
replace any letter "o" with a pipe "|" (just for testing). I would get the
same "white panel". After I closed down Access I reopened the database and
took a look at Table1. I can see that in the first record that all "o"'s were
replaced by a "|". But the second and third records were not touched.

Thinking there may be a problem with looping to the next record, I created a
new Table1 with only one record. The same problem occurred with the "white
panel" showing and not disappearing. And again, I checked the single record
and the "o"'s were replaced with the pipes ("|") in all fields.

Sorry for the lengthy response, but figured it may help . . .

Peter

Thanks again and looking forward to

 

[Bob Quintal]

add the statement mydata.movenext to the code on a new line
between

MyData.Update
Loop

Without it, the code just kept running the function on the first
record over and over.




=?Utf-8?B?UGV0ZXJtZ3I=?= <[email protected]>
wrote in

I'm sorry, I don't understand your jargon here. Fields are
atomic; they don't consist of "cells". If there are Access
fields within the record which are not populated, they are by
definition NULL and your code will throw an error when it
tries to call the function with a NULL argument.

John W. Vinson [MVP]

Thanks John,

Yes there are some records where a Field may be null. My
jargon was not clear.

I tried your suggestion, thank you.

So . . . . I run the code by selecting >Run>Run Sub/Userform.
I'm prompted for the table name and select okay. What I get
now is an all white panel with the title bar reading
"Microsoft Visual Basic - Ver 07147B[running]-[1-t(Code)].
This panel does not disappear. I ran this last night and the
white panel is still showing. BTW, for testing I only have 3
records in Table1.

Selecting CTRL-ALT-DELETE shows 1 instance of Microsoft Visual
Basic - Ver 07147B[running]-[1-t(Code)] and 1 instance of
Microsoft Access. Both showing as Not Responding. I then
select End Task on each.

I also tried running the routine with a simple Function in
place of "myReplace". I got the same results. The simple
Function I called on would replace any letter "o" with a pipe
"|" (just for testing). I would get the same "white panel".
After I closed down Access I reopened the database and took a
look at Table1. I can see that in the first record that all
"o"'s were replaced by a "|". But the second and third records
were not touched.

Thinking there may be a problem with looping to the next
record, I created a new Table1 with only one record. The same
problem occurred with the "white panel" showing and not
disappearing. And again, I checked the single record and the
"o"'s were replaced with the pipes ("|") in all fields.

Sorry for the lengthy response, but figured it may help . . .

Peter

Thanks again and looking forward to

 

[Petermgr]

Thanks so much Bob, Stefan and John!

Really appreciate all the help and advice!

Below is the final module that makes it all happen.

Without pushing the generosity you all have shown, is it possible to access
the routine by using a Macro? I have created many Macros that point to
various Functions, but I cannot find a way to access the "Private Sub
FixCTRLchars()" and run it with a Macro. I have about 30 Macros doing various
functions on my tool bar and I would like to add this "Character Cleansing
one" as well. I have done some searching on the web but have had no success
in running this module from a Marco . . .

Again, Thanks . . . Peter

------------------------------------------------------------------
Final Module
------------------------------------------------------------------

Option Compare Database

Private Sub FixCTRLchars()
'*********************************************
' Prompt user for the table name.
'process each field in each record to remove the
'control characters.
'*********************************************
Dim MyDB As Database, MyData As Recordset
Dim i As Integer, Msg As String, Title As String
Dim Defvalue As String, Answer As String

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Msg = "Enter table name."
Title = "Table Name?"
Defvalue = "Table1"
Answer = InputBox$(Msg, Title, Defvalue)
If Answer = "" Then Exit Sub
Set MyData = MyDB.OpenRecordset(Answer, dbOpenDynaset)
Do Until MyData.EOF
MyData.Edit
For i = 0 To MyData.Fields.Count - 1
If MyData.Fields(i).Type = dbText Then
If Not IsNull(MyData.Fields(i)) Then
MyData.Fields(i) = _
myReplace(MyData.Fields(i))
End If
End If
Next i
MyData.Update
MyData.MoveNext
Loop

MyData.Close
Set MyData = Nothing
Set MyDB = Nothing
End Sub

 

[Bob Quintal]

=?Utf-8?B?UGV0ZXJtZ3I=?= <[email protected]>
wrote in

Thanks so much Bob, Stefan and John!

Really appreciate all the help and advice!

Below is the final module that makes it all happen.

Without pushing the generosity you all have shown, is it
possible to access the routine by using a Macro? I have
created many Macros that point to various Functions, but I
cannot find a way to access the "Private Sub FixCTRLchars()"
and run it with a Macro. I have about 30 Macros doing various
functions on my tool bar and I would like to add this
"Character Cleansing one" as well. I have done some searching
on the web but have had no success in running this module from
a Marco . . .

Again, Thanks . . . Peter

change the procedure to a public function:
From: Private Sub FixCTRLchars()
to: Public Function FixCTRLchars() as boolean
Create a macro and call the runCode action, and enter
FixCTRLchars() as the function to run.

Q

-------------------------------------------------------------- -
--- Final Module
-------------------------------------------------------------- -
---

Option Compare Database

Private Sub FixCTRLchars()
'*********************************************
' Prompt user for the table name.
'process each field in each record to remove the
'control characters.
'*********************************************
Dim MyDB As Database, MyData As Recordset
Dim i As Integer, Msg As String, Title As String
Dim Defvalue As String, Answer As String

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Msg = "Enter table name."
Title = "Table Name?"
Defvalue = "Table1"
Answer = InputBox$(Msg, Title, Defvalue)
If Answer = "" Then Exit Sub
Set MyData = MyDB.OpenRecordset(Answer, dbOpenDynaset)
Do Until MyData.EOF
MyData.Edit
For i = 0 To MyData.Fields.Count - 1
If MyData.Fields(i).Type = dbText Then
If Not IsNull(MyData.Fields(i)) Then
MyData.Fields(i) = _
myReplace(MyData.Fields(i))
End If
End If
Next i
MyData.Update
MyData.MoveNext
Loop

MyData.Close
Set MyData = Nothing
Set MyDB = Nothing
End Sub

 

[Stefan Hoffmann]

hi Peter,

------------------------------------------------------------------
Final Module
------------------------------------------------------------------

Option Compare Database

Private Sub FixCTRLchars()
'*********************************************

Stop! There is an inevitable Option Explicit missing.

http://allenbrowne.com/ser-30.html


mfG
--> stefan <--

 

[Petermgr]

change the procedure to a public function:
From: Private Sub FixCTRLchars()
to: Public Function FixCTRLchars() as boolean
Create a macro and call the runCode action, and enter
FixCTRLchars() as the function to run.

Q

Bob Quintal

PA is y I've altered my email address.

Thanks Bob! . . . this works great!

Peter