How Do I: Find and Replace...

[EricD]

I have a document that is being created by MacroScheduler. During the creation of the file I am reading lines from one text file and writing them to another after a little massage.

I need to remove 10 punctuation characters from the entire document. It will be any !@#$%^&*(). There may be some or none. They may be anywhere in the document.

I have tried launching an editor then opening the document and finally running a series of search and replace. This method is crude and sloppy. There are many possible failures that can happen using this method.

I would like to do the whole search and replace from MacroScheduler.

Any Ideas??? I can post my whole script if it will help. It is only in need of some replace logic. Let me know if I have provided enough information to answer this.

Thanks
Eric Dye

[Bob Hansen]

Hi Eric.......

Have you looked at the topic "Searching huge text file" (or something close to that) from 11/27/02?

I seem to recall that was a problem similar to yours. It might not be exact, but could be a good starting point. The solution might be right there.....

Also keep in mind using the ASC value of characters to be eliminated. You might be able to search for all values above/below some values, or some range of values. This might be an easy method to do if you only want to restrict content to real alpha letters/numbers and exclude all other characters.

Good luck.....

[Ernest]

Hi,
check a combination of Position>, WriteLN> and MidStr>.

1. Read total line, set line variable
2. Identify Position of character to replace in line var.
3. Read line till character you wanna replace, ConCat the new character, write to dest.file
4. Read line from pos. right after character you've replaced, use as new line variable

next check, back to 2.

Just an idea

Ernest

[support]

Use the VBScript Replace function to quickly replace the chars in the line read from the file:

VBSTART
VBEND
..
..
..
ReadLn>yourfile,linenum,line
VBEval>Replace("%line%","@",""),line
VBEval>Replace("%line%","!",""),line
VBEval>Replace("%line%","#",""),line
VBEval>Replace("%line%","$",""),line
etc
etc

[Bob Hansen]

Thanks Support
Your solution:

VBSTART
VBEND

Beautiful, nice and simple. Sometimes the most obvious tools are right in front of you and still invisible. I only thought of using VB when creating new functions. Never thought about just entering VBSTART/VBEND to acrtivate VB and use existing VB functions.

I can't believe I never tried this before!
Once again, "what a dummy, me"

[EricD]

I will try it.

[Eric D]

Here is a stripped out version of my code.

I am gettting a syntax error when I run this. I am expecting to get a result of Test.Test

Can anyone see anything that isnt right?

Thanks
Eric

Let>xline=!@#$%^&*Test...{}[]|\MMMMLLLLTestNNNNYZYZ

VBSTART
VBEND

VBEval>Replace("%xline%","!",""),xline
VBEval>Replace("%xline%","#",""),xline
VBEval>Replace("%xline%","$",""),xline
VBEval>Replace("%xline%","%",""),xline
VBEval>Replace("%xline%","^",""),xline
VBEval>Replace("%xline%","&",""),xline
VBEval>Replace("%xline%","*",""),xline
VBEval>Replace("%xline%","_",""),xline
VBEval>Replace("%xline%","=",""),xline
VBEval>Replace("%xline%","[",""),xline
VBEval>Replace("%xline%","]",""),xline
VBEval>Replace("%xline%","{",""),xline
VBEval>Replace("%xline%","}",""),xline
VBEval>Replace("%xline%","|",""),xline
VBEval>Replace("%xline%","\",""),xline
VBEval>Replace("%xline%","~",""),xline
VBEval>Replace("%xline%",":",""),xline
VBEval>Replace("%xline%",";",""),xline
VBEval>Replace("%xline%","Replace("%xline%",">",""),xline
VBEval>Replace("%xline%","AMI:",""),xline
VBEval>Replace("%xline%","LLLL",""),xline
VBEval>Replace("%xline%","MAC ",""),xline
VBEval>Replace("%xline%","MMMM",""),xline
VBEval>Replace("%xline%","NNNN",""),xline
VBEval>Replace("%xline%","QTA",""),xline
VBEval>Replace("%xline%","VVVV",""),xline
VBEval>Replace("%xline%","YZYZ",""),xline
VBEval>Replace("%xline%","ZCZC",""),xline
VBEval>Replace("%xline%","..",""),xline

MessageModal>%xline%

[support]

I get @Test.Test because you have missed out a replace line for the '@' character. Otherwise it works perfectly.

What syntax error do you get? Does your version of Macro Scheduler support VBScript (all versions since 7.1 support it - previous versions had to be the VBScript edition).

[Eric D]

If there is a " character in the text I am running the replace on then it sees it as a text qualifier and kills the rest of the operation. How do I replace a " ?

Thanks

Eric

[Ernest]

Hi,
have a try to use it as a variable:

Let>pattern="
VBEval>Replace("%xline%","%pattern%",""),xline

Rgds,
Ernest

[Ernest]

Ooops,
hasn't worked - ERROR 1002.
I'm sorry.

[Eric D]

Ernest,
Good thought... however it is in the %xline% variable there is a " . It is then passed to the VBSCRIPT as a literal string thus reading the " too early. So that is presenting a whole different issue.

Your idea of putting the searched character into a variable will work but now what do I do about the source %xline% having a " in it?

Sheeesh....

Thanks for your help,
Eric

[Bob Hansen]

This was interesting but I think I may have the solution.

I determined that the error comes from the xline, not from the search for the character " to be replaced.

I tried using chr(34) as a replacement, but that did not work.
I tried using Asc, but no way to do that.
I tried a few other items that also failed, then I got it!

Here is the concept, code should not be too difficult:

1. Search xline for a missing unusual character (~?) that will be inserted later, replacing ". (Using ~ here because is uncommon, but perhaps a non-printing character might be used).
2. Get length of xline to use in MidStr calculations later
3. Locate position of " in xline
4. Use MidStr to send characters on left side of " to variable left. MidStr(xline,1,position-1,left)
5. Use MidStr to send characters on right side of " to variable right . MidStr(xline,position+1,length-position,right)
6. Concat left + ~ +right to make new xlinetemp
7. May have to repeat on xline more than once "right" string until all " have been replaced with ~

Example of process:
xline_original = abcdef"prstxyz
length = 14
look for "special character.....~ does not exist, so it will be used.
position of " = 7
left of " = abcdef
right of " =prstxyz
concat result = abcdef~prstxyz
xlinetemp = concat result
Do Replace(xlinetemp,.......) of characters as desired, (including ~ if desired)

=====================================
No time to do actual code right now, but I think this is pretty straightforward. Do this process to xline before you start the Replace functions. Hope this does it for you.......

[armsys]

Hi Eric,

Your goal of removing punctuation marks can be achieved in no time by using Multi-Edit (http://www.multiedit.com) with regular expression.

Find: [!@#$%^&*(). ]
Replace with:

This will take only seconds regardless of the file size. FYI, VBScript also supports Regular Expression with a RegExp object. In terms of time saving, Multi-Edit is a definite winner. You can compose a Macro Scheduler script to "guide" Multi-Edit to process multiple large-size documents. Multi-Edit alone is completely programmable but requires a long learning curve.

[Eric D]

Thanks to everyone that helped us get through the punctuation removal. Here is the final script so you can see how we ended up accomplishing the punctuation removal.

We used 2 separate VB Scripts the first script looks at the whole file and removes the ".

The second searches for specific lines in the file and removes the specific symbols and punctuation.

Begin Wanda script:

'Program Name: Wanda File Analyzer
'Version: 1.1
'Author: Eric Dye
'Date: 11/25/2002
'Last Revision 01/10/2003

'12/24/2002 Added Special Charater removal code using Search and Replace
'12/30/2002 Enhanced Special Character removal code by adding VB Script
'01/10/2003 Added " removal in file before Character removal begins

'//Path Variables
Let>PathOutFinal=l:\apps\mbmsnet\wanda\out\
Let>PathInUniForm=l:\apps\mbmsnet\wanda\in\
Let>PathOut=c:\apps\mbmsnet\wanda\out\
Let>PathIn=c:\apps\mbmsnet\wanda\in\
Let>TempFile=c:\apps\mbmsnet\wanda\in\wires.tmp
Let>LogFile=l:\apps\mbmsnet\wanda\logs\Wandalog.txt

'//Zero Out all counter variables
Let>LoanCounter=0
Let>ArchCount=0


'//Set shutdown time. Must be 2 digit between 01 - 11
'//This variable is seen as PM
Let>EndTime=08



'//This writes startup information to the log File
GetDate>Date
GetTime>Time
WriteLn>LogFile,result,%Date% %Time% Wanda Engine has started.


'//Checks for the existance of old temp files to delete
'//Checks for the arrival of a Wanda File
Label>Top
Let>LoanCounter=0
IfFileExists>TempFile,DeleteTemp
IfFileExists>%PathInUniForm%wanda.txt,BeginProcess


'//Shuts Down the program at Specified time
GetTime>TIME
MidStr>TIME,1,2,HH
MidStr>TIME,10,11,AMPM
ConCat>TIME_MIN,AMPM
If>AMPM=AM,TimeChk
IF>HH=12,TimeChk
If>HH>%EndTime%,Program_END

Label>TimeChk
Wait>10
Goto>Top


'//Starts the Formatting Process
Label>BeginProcess
MoveFile>%PathInUniform%wanda.txt,PathIn

'//This writes specified information to the log File
GetDate>Date
GetTime>Time
WriteLn>LogFile,result,%Date% %Time% Wanda file found. Preparing to run format program.


Let>k=0
Label>start
Let>k=k+1
ReadLn>%PathIn%wanda.txt,k,line
If>line=##EOF##,CounterForFooter
MidStr>line,1,3,USD_Check

If>%USD_Check%=USD,BeginFormat
Goto>start


Label>BeginFormat
Let>LoanCounter=LoanCounter+1

MidStr>%line%,5,12,WireAmount
MidStr>WireAmount,1,1,TestChar1
MidStr>WireAmount,3,1,TestChar3
MidStr>WireAmount,4,1,TestChar4
MidStr>WireAmount,5,1,TestChar5
MidStr>WireAmount,7,1,TestChar7

If>TestChar1 ,1sp
If>TestChar3 ,3sp
If>TestChar4 ,4sp
If>TestChar5 ,5sp
If>TestChar7 ,7sp

Label>7sp
Midstr>WireAmount,7,6,FinalWire
Goto>DoneCheck

Label>5sp
Midstr>WireAmount,5,8,FinalWire
Goto>DoneCheck


Label>4sp
Midstr>WireAmount,4,9,FinalWire
Goto>DoneCheck

Label>3sp
Midstr>WireAmount,3,10,FinalWire
Goto>DoneCheck

Label>1sp
Midstr>WireAmount,1,12,FinalWire
Goto>DoneCheck

Label>DoneCheck

WriteLn>%TempFile%,result,%FinalWire%

Goto>start


'********* This Section Counts and Formats the Number of Loans in the File for use in the footer ********

Label>CounterForFooter

Label>DoneCount
If>%LoanCounter%=0,ZeroFill6
If>%LoanCounter%%LoanCounter%%LoanCounter%%LoanCounter%%LoanCounter%%LoanCounter%Error

Label>ZeroFill6
Let>ZeroFill=000000
Goto>DoneZeroFill

Label>ZeroFill5
Let>ZeroFill=00000
Goto>DoneZeroFill

Label>ZeroFill4
Let>ZeroFill=0000
Goto>DoneZeroFill

Label>ZeroFill3
Let>ZeroFill=000
Goto>DoneZeroFill

Label>ZeroFill2
Let>ZeroFill=00
Goto>DoneZeroFill

Label>ZeroFill1
Let>ZeroFill=0
Goto>DoneZeroFill

Label>NoZeroFill
Let>ZeroFill=

Label>DoneZeroFill

ConCat>ZeroFill,LoanCounter
Let>NumLoans=ZeroFill




'*********** This section performs the adding of all the wire amounts.********************

Let>Total=0
Let>m=0

Label>start3

Let>m=m+1
ReadLn>%PathIn%wanda.txt,m,mline

If>mline=##EOF##,DoneSum
MidStr>mline,1,3,USD_Check2
If>%USD_Check2%=USD,SumIt
Goto>start3

Label>SumIt
MidStr>mline,17,12,WireNoPunct
Let>Total=Total+WireNoPunct

Goto>start3

Label>DoneSum

Concat>Total,
MidStr>%Total%,16,1,TotalFormat16
MidStr>%Total%,15,1,TotalFormat15
MidStr>%Total%,14,1,TotalFormat14
MidStr>%Total%,13,1,TotalFormat13
MidStr>%Total%,12,1,TotalFormat12
MidStr>%Total%,11,1,TotalFormat11
MidStr>%Total%,10,1,TotalFormat10
MidStr>%Total%,9,1,TotalFormat9
MidStr>%Total%,8,1,TotalFormat8
MidStr>%Total%,7,1,TotalFormat7


If>%TotalFormat16% ,Format16
If>%TotalFormat15% ,Format15
If>%TotalFormat14% ,Format14
If>%TotalFormat13% ,Format13
If>%TotalFormat12% ,Format12
If>%TotalFormat11% ,Format11
If>%TotalFormat10% ,Format10
If>%TotalFormat9% ,Format9
If>%TotalFormat8% ,Format8
If>%TotalFormat7% ,Format7

Label>Format16
Let>FinalTotal=%Total%
Goto>DoneFormatTotal

Label>Format15
Let>FinalTotal=0
Concat>FinalTotal,%Total%
Goto>DoneFormatTotal

Label>Format14
Let>FinalTotal=00
Concat>FinalTotal,%Total%
Goto>DoneFormatTotal

Label>Format13
Let>FinalTotal=000
Concat>FinalTotal,%Total%
Goto>DoneFormatTotal

Label>Format12
Let>FinalTotal=0000
Concat>FinalTotal,%Total%
Goto>DoneFormatTotal

Label>Format11
Let>FinalTotal=00000
Concat>FinalTotal,%Total%
Goto>DoneFormatTotal

Label>Format10
Let>FinalTotal=000000
Concat>FinalTotal,%Total%
Goto>DoneFormatTotal

Label>Format9
Let>FinalTotal=0000000
Concat>FinalTotal,Total
Goto>DoneFormatTotal

Label>Format8
Let>FinalTotal=00000000
Concat>FinalTotal,Total
Goto>DoneFormatTotal

Label>Format7
Let>FinalTotal=000000000
Concat>FinalTotal,Total
Goto>DoneFormatTotal


Label>DoneFormatTotal
MidStr>FinalTotal,1,16,FinalTotal
Goto>BuildHeader



'//When the archive directories get more than 99 files this process further archives the files
'//to a permanent storage location.
Label>CleanUpArch
MoveFile>%PathInUniForm%Archive\*.txt,%PathInUniForm%Archive\Arch_old_in\
MoveFile>%PathOutFinal%*.txt,%PathInUniForm%Archive\Arch_old_out\
GetDate>Date
GetTime>Time
WriteLn>LogFile,result,%Date% %Time% Cleanup of Old Files Performed.


'//This will build the header and store it in a Var called header
'//for use when the file is created
Label>BuildHeader
Let>ArchCount=0
CountFiles>%PathInUniForm%archive\*.txt,ArchCount,0
If>ArchCount>89,CleanUpArch
Let>ArchCount=%ArchCount%+10

GetDate>Today
MidStr>Today,1,2,MM
MidStr>Today,4,2,DD
MidStr>Today,9,2,YY

ConCat>MM,DD
ConCat>MM,YY

Let>Header=WANDA_%ArchCount%.TXT
Concat>Header,%MM%
Concat>Header,ArchCount
WriteLn>%PathOut%wanda_%ArchCount%.txt,result,%Header%


'//This builds the newly formatted file line by line
'//It also inserts correctly formatted Loan amount

Label>CreateFile


//VBScript to first remove any " from file.
VBSTART
Sub ReadEntireFile1
'reading file and storing it in ReadFile
Dim fs, f, ReadFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("c:\apps\mbmsnet\wanda\in\wanda.txt", 1,0)
ReadFile = f.ReadAll
f.Close

dim result
dim QuoteChar
QuoteChar = chr(34)
result = replace(ReadFile,QuoteChar," ")
Set f = fs.OpenTextFile("c:\apps\mbmsnet\wanda\in\wanda.txt", 2, True)
f.Write result
End Sub
VBEND
VBRun>ReadEntireFile1


Let>x=0
Let>WireCount=0
Label>start4

Let>x=x+1
ReadLn>%PathIn%wanda.txt,x,xline
'If>xline=##EOF##,BuildFooter
MidStr>xline,1,3,USD_Check4
If>%USD_Check4%USD,WriteIt
Goto>CreateWireAmount

Label>WriteIt
If>%USD_Check4%=:57,StripLine
WriteLn>%PathOut%wanda_%ArchCount%.txt,result,xline

Goto>start4



Label>CreateWireAmount
Let>WireWrite=USD
Let>WireCount=WireCount+1
ReadLn>%TempFile%,WireCount,FinalWire
Concat>WireWrite,FinalWire
WriteLn>%PathOut%wanda_%ArchCount%.txt,result,%WireWrite%
Goto>start4


Label>StripLine
WriteLn>%PathOut%wanda_%ArchCount%.txt,result,xline
Let>x=x+1
ReadLn>%PathIn%wanda.txt,x,xline
MidStr>xline,1,3,USD_Check4
If>USD_Check4=:72,StripLine
If>USD_Check4=*EN,start4
If>xline=##EOF##,BuildFooter

'remove punct in xline
VBSTART
VBEND
VBEval>Replace("%xline%","!"," "),xline
VBEval>Replace("%xline%","@"," "),xline
VBEval>Replace("%xline%","#"," "),xline
VBEval>Replace("%xline%","$"," "),xline
VBEval>Replace("%xline%","%"," "),xline
VBEval>Replace("%xline%","^"," "),xline
VBEval>Replace("%xline%","&"," "),xline
VBEval>Replace("%xline%","*"," "),xline
VBEval>Replace("%xline%","_"," "),xline
VBEval>Replace("%xline%","="," "),xline
VBEval>Replace("%xline%","["," "),xline
VBEval>Replace("%xline%","]"," "),xline
VBEval>Replace("%xline%","{"," "),xline
VBEval>Replace("%xline%","}"," "),xline
VBEval>Replace("%xline%","|"," "),xline
VBEval>Replace("%xline%","\"," "),xline
VBEval>Replace("%xline%","~"," "),xline
VBEval>Replace("%xline%",":"," "),xline
VBEval>Replace("%xline%",";"," "),xline
VBEval>Replace("%xline%","Replace("%xline%",">"," "),xline
VBEval>Replace("%xline%","AMI:"," "),xline
VBEval>Replace("%xline%","LLLL"," "),xline
VBEval>Replace("%xline%","MAC "," "),xline
VBEval>Replace("%xline%","MMMM"," "),xline
VBEval>Replace("%xline%","NNNN"," "),xline
VBEval>Replace("%xline%","QTA"," "),xline
VBEval>Replace("%xline%","VVVV"," "),xline
VBEval>Replace("%xline%","YZYZ"," "),xline
VBEval>Replace("%xline%","ZCZC"," "),xline
VBEval>Replace("%xline%",".."," "),xline
Goto>StripLine


'//This will build the Footer and store it in a Var called Footer
'//for use when the file is created
Label>BuildFooter
Let>Footer=*ENDFILE*
Concat>Footer,%NumLoans%
Concat>Footer,FinalTotal
WriteLn>%PathOut%wanda_%ArchCount%.txt,result,*ENDMSG*
WriteLn>%PathOut%wanda_%ArchCount%.txt,result,%Footer%

'This goto may be changed to goto a section to check file integrity. For now it justs ends with out such checking.
Goto>End

Label>Error
WriteLn>LogFile,result,%Date% %Time% An Error has occured and all processing will end.
Goto>Program_END

Label>DeleteTemp
Wait>3
DeleteFile>%TempFile%
Goto>Top


Label>End
MoveFile>%PathIn%wanda.txt,%PathInUniForm%archive\wanda_%ArchCount%.Txt
MoveFile>%PathOut%*.txt,PathOutFinal

'This writes specified information to the log File
GetDate>Date
GetTime>Time
WriteLn>LogFile,result,%Date% %Time% File Processed
Message>File Processed Successfully at %Time% on %Date%%CRLF% File Name is wanda%ArchCount%.Txt
Goto>Top

Label>Program_END
GetDate>Date
GetTime>Time
Message>Wanda Engine has shutdown.
WriteLn>LogFile,result,%Date% %Time% Wanda Engine has successfully shutdown.
WriteLn>LogFile,result,
WriteLn>LogFile,result,
WriteLn>LogFile,result,

End Wanda Script



Here is a sample of the output file it creates.

WANDA_58.TXT 010903 58
*ENDMSG*
FROM:/NA-XXXX
MORTGAGE CORP.
SUITE 1000
CALIFORNIA
TO: /NA-TMTA
BANK OF AMERICA
SAN FRANCISCO, CA
DATE:030103

::200 BANK TRANSFER FOR OUR ACCOUNT
PLEASE PAY

:15 TEST KEY:

:20 SENDERS REF:0000000000
:30 VALUE DATE:030103
:32 AMOUNT:
USD 199,700.00

:53D REIMBURSEMENT:/DD-00000000
MORTGAGE CORP.
CA
:57D PAY THRU:/FW-
:72 RECEIVER INFO:
/ACC/
//ORDER BRKAPPLFEE
FROM:/NA-XXXX
MORTGAGE CORP.
SUITE 1000
CALIFORNIA
TO: /NA-TMTA
BANK OF AMERICA
SAN FRANCISCO, CA
DATE:030103

::200 BANK TRANSFER FOR OUR ACCOUNT
PLEASE PAY

:15 TEST KEY:

:20 SENDERS REF:0000000000
:30 VALUE DATE:030103
:32 AMOUNT:
USD 201,555.20

:53D REIMBURSEMENT:/DD-000000000
MORTGAGE CORP.
CA
:57D PAY THRU:/FW-00000000
BANK OF THE WEST
601 S FIGUEROA ST W817 1 2
LOS ANGELES CA 900171 1
:72 RECEIVER INFO:
/ACC/FIDELITY NATIONAL TITLE
//ACCOUNT NO 000000000 1 2
//ORDER 000000000 HARRIMAN
*ENDMSG*
*ENDFILE* 000002 0000000040125520