Replace special Characters in a string

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
DWEd
Newbie
Posts: 12
Joined: Fri Jul 15, 2016 2:54 pm

Replace special Characters in a string

Post by DWEd » Fri Jul 15, 2016 3:04 pm

I've got a script that logs into our shop system and claws out all the details needed for order shipment, it then formats that data in a suitable format for batch Fedex/DPD shipping. Everything was going well until I found the DPD and Fedex apps can't cope with special characters.

So I need a way of replacing special characters in a string.
Here is an example of an address line:
C/ VASCO NUÑEZ DE BALBOA Nº63 NAVE 5 P.I. DEL HENARES

This is currently done manually, so in this case we would replace "NUÑEZ" with "NUNEZ" and "Nº63" with "No63"

Ideally I'd like to be able to provide an array of characters and what to replace them with so it could automatically "fix" the strings.

Another option may be to just detect any invalid characters using something like regex??? and then pop a dialog up asking someone to fix it.

hagchr
Macro Veteran
Posts: 277
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: Replace special Characters in a string

Post by hagchr » Fri Jul 15, 2016 3:55 pm

Hi, many ways to solve it. One possibility below. I put it into an SRT>Replace that can be called. The name of the string/text to be checked is passed to the SRT enclosed in "" (to avoid variable resolution problems). The replacement words could be read from a file etc, here just listed at the end in a label.

Code: Select all

Let>strText1=C/ VASCO NUÑEZ DE BALBOA Nº63 NAVE 5 P.I. DEL HENARES
Let>strText2=C/ VASCO NUÑEZ DE BALBOA Nº63 NAVE 5 P.I. DEL HENARES

Gosub>Replace,"strText1"
Gosub>Replace,"strText2"

MDL>strText1
MDL>strText2


SRT>Replace
    RegEx>^"\K.+(?="$),Replace_VAR_1,0,m1,nm1,0
    
    //Read the name of the string w/o"" into variable tmpvar
    Let>tmpvar=m1_1

    //Read replacement list = RepList into variable RepList
    LabelToVar>RepList,RepList
    
    //Separate into lines = Replacement_1, Replacement_2, ...
    Separate>RepList,%CRLF%,Replacement
    
    //Loop over all replacement words you want to search for
    Let>ctr=0
    While>ctr<{%Replacement_COUNT%-1}
        Add>ctr,1
        Let>tmp=Replacement_%ctr%
        //Get the key word before the comma of current record
        RegEx>.+(?=%COMMA%),tmp,0,mA,nmA,0
        //Get the replacement word after the comma of current record
        RegEx>%COMMA%\K.+,tmp,0,mB,nmB,0
        
        //Search for key word and replace w replacement word (if exists)
        RegEx>mA_1,%tmpvar%,0,m2,nm2,1,mB_1,tmpvar
    EndWhile
    
    //put the string after all replacements back to the variable name given by the input parameter of the SRT
    Let>%m1_1%=tmpvar
END>Replace


/*
RepList:
Ñ,N
º,o
*/

hagchr
Macro Veteran
Posts: 277
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: Replace special Characters in a string

Post by hagchr » Fri Jul 15, 2016 6:21 pm

Hi, another version in case you and RegEx are not best of friends...

Code: Select all

Let>strText1=C/ VASCO NUÑEZ DE BALBOA Nº63 NAVE 5 P.I. DEL HENARES
Let>strText2=C/ VASCO NUÑEZ DE BALBOA Nº63 NAVE 5 P.I. DEL HENARES

Gosub>Replace,"strText1"
Gosub>Replace,"strText2"

MDL>strText1
MDL>strText2

SRT>Replace
    //Get the input parameter
    Let>para1=Replace_VAR_1
    
    //Remove the ""
    StringReplace>para1,",,para1
    //Assign the contents of para1 to tmpvar    
    Let>tmpvar=%para1%
        
    //Read replacement list = RepList into variable RepList
    LabelToVar>RepList,RepList
    Separate>RepList,%CRLF%,Replacement

    Let>ctr=0
    While>ctr<{%Replacement_COUNT%-1}
        Add>ctr,1
        Let>tmp=Replacement_%ctr%
        Separate>tmp,COMMA,tmp1
        StringReplace>tmpvar,tmp1_1,tmp1_2,tmpvar
    EndWhile

    Let>%para1%=tmpvar
End>Replace

/*
RepList:
Ñ,N
º,o
*/

DWEd
Newbie
Posts: 12
Joined: Fri Jul 15, 2016 2:54 pm

Re: Replace special Characters in a string

Post by DWEd » Fri Jul 15, 2016 9:44 pm

Cheers, that looks perfect!
I've not used lists before, as my list of replacements is getting a little unwieldy already can I throw it into a single (very long) line so I've not got 200+ rows of text,
so rather than
RepList:
Á,A
À,A
Â,A
Ă,A
Ā,A
Ã,A
Å,A
Ą,A

I have
RepList:
Á,A~À,A~Â,A~Ă,A~Ā,A~Ã,A~Å,A~Ą,A~

and then change "Separate>RepList,%CRLF%,Replacement" to "Separate>RepList,~,Replacement"

hagchr
Macro Veteran
Posts: 277
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: Replace special Characters in a string

Post by hagchr » Sat Jul 16, 2016 3:26 am

That should work fine. If it gets messy to maintain then you also have the option to put it into a separate file (eg simple text or excel file) and load it from there using ReadFile> or the XL functions.

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts