Link to home
Start Free TrialLog in
Avatar of deer777
deer777Flag for United States of America

asked on

MS Access 2003: Find character in Text File and Replace with another character

I need to find (2 spaces) and replace with (1 space) in a text file before using a TransferText on it through code or queries.  How would I do this?
Avatar of aesmike
aesmike

Sounds like a familiar situation.  Do you have situations where there are exactly two spaces and you want to strip it down to one?  Or are you trying to shrink down multiple spaces to only one space?  
If it's the former, you can create a query that uses the REPLACE function for each field you ware ultimately exporting.
e.g. SELECT CustomerID, REPLACE([CustomerName],"  "," ") as CName INTO <NEW TABLE> etc, etc, etc.

Run this query, create the table, then do the transfertext command that you need to do.
you can use the getobject function open word with a macro and do a search and replace then save the file back out as a text file
here's a snipit of code for the getobject function
' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName as String, _
                    ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd as Long,ByVal wMsg as Long, _
                    ByVal wParam as Long, _
                    ByVal lParam As Long) As Long

Sub GetWord()
    Dim Mydoc As Object    ' Variable to hold reference
                                ' to Microsoft Word.
    Dim WordWasNotRunning As Boolean    ' Flag for final release.

' Test to see if there is a copy of Microsoft Word already running.
    On Error Resume Next    ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
    Set Mydoc = Getobject(, "Word.Application")
    If Err.Number <> 0 Then WordWasNotRunning = True
    Err.Clear    ' Clear Err object in case error occurred.

' Check for Microsoft Word. If Microsoft Word is running,
' enter it into the Running Object table.
    DetectWord

' Set the object variable to reference the file you want to see.
    Set Mydoc = Getobject("c:\vb4\MYTEST.doc")

' Show Microsoft Word through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the Mydoc object reference.
    Mydoc.Application.Visible = True
    Mydoc.Parent.Windows(1).Visible = True
     Do manipulations of your  file here.
    ' ...
' If this copy of Microsoft Word was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Word, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
    If WordWasNotRunning = True Then
        Mydoc.Application.Quit
    End IF

    Set Mydoc = Nothing    ' Release reference to the
                                ' application and spreadsheet.
End Sub

Sub DetectWord()
' Procedure dectects a running Word and registers it.
    Const WM_USER = 1024
    Dim hWnd As Long
' If Word is running this API call returns its handle.
    hWnd = FindWindow("docMAIN", 0)
    If hWnd = 0 Then    ' 0 means Word not running.
        Exit Sub
    Else                
    ' Word is running so use the SendMessage API
    ' function to enter it in the Running Object Table.
        SendMessage hWnd, WM_USER + 18, 0, 0
    End If
End Sub


Build a series of loops that walk through the text file character by character comparing the previous to characters to two spaces, if the condition is true, then replace with one space, and advance to the next character until EOL or EOF condition is true.
ASKER CERTIFIED SOLUTION
Avatar of aesmike
aesmike

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Patrick Matthews
Another way, using Regular Expressions:
Option Explicit 
Sub CleanUpFile() 
    Dim fso As Object, ts As Object, WholeFile As String
    
    Const filepath As String = "c:\folder\subfolder\file.txt"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile(filepath)
    WholeFile = ts.ReadAll
    ts.Close
    
    Set ts = fso.CreateTextFile(filepath, True)
    ts.Write RegExpReplace(WholeFile, " {2,}")
    ts.Close
    
    Set ts = Nothing
    Set fso = Nothing
    
    MsgBox "Done"
    
End Sub 
Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True) 
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
    ' whether all instances of the matched string are replaced (True) or just the first instance (False)
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    Dim RegX As Object
    
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    
    Set RegX = Nothing
    
End Function

Open in new window

Or, if you prefer line by line:
Option Explicit 
Sub CleanUpFile() 
    Dim fso As Object, ts1 As Object, ts2 As Object
    
    Const InputFile As String = "c:\folder\subfolder\file.txt"
    Const OutputFile As String = "c:\folder\subfolder\clean_file.txt"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts1 = fso.OpenTextFile(InputFile)
    Set ts2 = fso.CreateTextFile(OutputFile)
    
    Do Until ts1.AtEndOfStream
        ts2.WriteLine RegExpReplace(ts1.ReadLine, " {2,")
    Loop
    
    ts1.Close
    ts2.Close
    
    Set ts1 = Nothing
    Set ts2 = Nothing
    Set fso = Nothing
    
    MsgBox "Done"
    
End Sub 
Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True) 
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
    ' whether all instances of the matched string are replaced (True) or just the first instance (False)
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    Dim RegX As Object
    
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    
    Set RegX = Nothing
    
End Function

Open in new window

Avatar of deer777

ASKER

MatthewsPatrick:

I am getting a "file not found" error message on

     set ts1 = fso.OpenTextFile(Inputfile)
deer777,

You need to edit the constant values to show the original file path and name (InputFile), and where
you want the results saved (OutputFile).  Did you do that?

Regards,

Patrick
Avatar of deer777

ASKER

I did that and now I am getting the error:

Application-defined or object-defined error:

RegExpReplace = RegX.Replace(Lookin, ReplaceWith)
I've had problems with getting the Regular Expression thing to work with FSO.
my solution loops through and simply replaces two spaces for one.  Simple, but effective.
Avatar of deer777

ASKER

AESMIKE:

I tried your sample earlier and received this error:

Compile error:
Variable not defined

Handle
Avatar of deer777

ASKER

Way to Go!!  Good Answer!!
Another way to deal with this problem is very simple.  (First, as always, copy your table!)  Open the table in which the field you are referencing is housed.  Put your cursor in the beginning of the first record of the field you need cleaned up.  Go to Edit, Find/Replace.  Click on Replace.  In the "Find" field, type two spaces with the space bar.  In the "Replace with" field, type one space.  Make sure that you change the Match field to "Any part of field".  Try it out with the Search and Replace keys or go ahead and hit Replace All.  You will get a count of how many replacements were made at the end.  This is the fastest and simplest way to take care of this problem.