search and replace special characters in a text file 1

[Lhuffst]

I have a table with a text field in which I want to search and replace (or skip) special characters writing out the cleaned up file to a new text file.

The steps I'm doing are:
1. read text file
2. replace characters
3. write to new text file

I can read and write fine but the skipping or replacing with a null, isn't working the way I expect. Here is my code:

Function ReadStraightText()

Const AccChars As String = "Ñ^¢][Ñ{}~\!-ÆÔê;"
Const RegChars As String = "                 "

Dim myControl As Recordset
Dim i As Long, j As Long
Dim tempString As String
Dim currentCharacter As String
Dim found As Boolean
Dim foundPosition As Long
Dim Chari As Integer


Set myDB = CurrentDb()
Set myControl = myDB.OpenRecordset("tblcontrol")
myControl.MoveFirst

InputFileName = myControl("billingoutbound")
OutputFileName = myControl("routemapinbound")



RowCounter = 1

'clean the file first
Open InputFileName For Input As #1
Open OutputFileName For Output As #2

Do While Not EOF(1)

    Line Input #1, lineoftext
     
    tempString = lineoftext   ' loop through the shorter string


Select Case True
Case Len(AccChars) <= Len(lineoftext)
' accent character list is shorter (or same)
' loop through accent character string
		
For i = 1 To Len(AccChars)         		' get next accent character
currentCharacter = Mid$(AccChars, i, 1)         ' replace with corresponding character in "regular" array


If InStr(tempString, currentCharacter) > 0 Then
tempString = Replace(tempString, currentCharacter, Mid$(RegChars, i, 1))
End If
 'Debug.Print currentCharacter
 'Debug.Print Asc(currentCharacter)
 'Debug.Print tempString
 
 

 If currentCharacter = Chr(26) Then
   
  tempString = Replace(tempString, currentCharacter, Chr(32))
 End If

Next i
Case Len(AccChars) > Len(lineoftext)
' input string is shorter
' loop through input string

For i = 1 To Len(lineoftext)
' grab current character from input string and
' determine if it is a special char

currentCharacter = Mid$(lineoftext, i, 1)
'if can't match on special characters tried the reverse but it didn't work
'If (currentCharacter >= Chr(48) And currentCharacter >= Chr(57)) Or _
'    (currentCharacter >= Chr(65) And currentCharacter >= Chr(90)) Then
'     currentCharacter = currentCharacter
'Else
'    currentCharacter = vbNullString
'End If
found = (InStr(AccChars, currentCharacter) > 0)

If found Then           ' find position of special character in special array
foundPosition = InStr(AccChars, currentCharacter)
' replace with corresponding character in "regular" array
tempString = Replace(tempString, currentCharacter, Mid$(RegChars, foundPosition, 1))

End If

 Next i
End Select
    Write #2, lineoftext 'should it be tempstring?
Loop
Close #1
Close #2


End Function

One of the unprintable letters that I'm getting (original file is from mainframe), is chr(26). The code recognizes it but it truncates the line instead of skipping the character.
Any help would be greatly appreciated.
Thanks
lhuffst6

 

[Andrzejek]

How about just this:

Do While Not EOF(1)

    Line Input #1, lineoftext
     
    For i = 1 To Len(AccChars)
        lineoftext = Replace(lineoftext, Mid$(AccChars, i, 1), " ")
    Next i
    lineoftext = Replace(lineoftext, Chr(26), " ")
    Print #2, lineoftext 
Loop

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Lhuffst]

That worked great. Thank you. Unfortunately, I just discovered that when it is being read in, the file is getting truncated when it see the chr(26) so what appeared to be the real issue wasn't the real issue. Is there a way to replace all of those characters with a space before I even read it? I need to do this in code because the user will have to process these files multiple times. It is a download from the mainframe. Thank you again. lhuffst

 

[Andrzejek]

You may get some ideas of how to remove chr(26) here: how to replace the special characters from file using shell script

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.