Replace all foreign chars by UK Chars

I wanted to replace all foreign chars in excel sheet by UK chars. Please suggest me the way/help.

What logic I have to use .

Regards,
Shirish

I am trying to write formula in excel by using write cell but it is giving error as End of expression needed even after adding double quotes. The formula which i am writing is big one i.e. Substitute formula. Please help

Hi @Shirish

You can achieve this with a simple assign activity, I used the idea from here:

See the attached workflow for solution :slight_smile:
NormalizeStringDiacritics.zip (2.0 KB)

All you have to do afterwards is to load your data into UiPath from Excel, process it with the above-mentioned method and then save back to Excel :slight_smile:

@Shirish I gave you a sample.xaml in the other post you made !!

And it worked great, dont know why you dont use it.

Kind regards,
Pablo

It is giving error while opening xaml. I think, u have prepared the solution in 2018.3 and I am using 2016.3. How we can resolve the issue

See here for the solution in screenshots :slight_smile:

The expression :slight_smile:

System.Text.Encoding.UTF8.GetString(System.Text.Encoding.GetEncoding("ISO-8859-8").GetBytes(sourceString))
4 Likes

“system.text.encoding can not be indexed because it done have any value” it is giving this error. Have u created Source and output string as string ot anything else ?

When I have assigned outputstring = System.Text.Encoding.UTF8.GetString(System.Text.Encoding.GetEncoding(“ISO-8859-8”).GetBytes(sourceString))

then it is giving error system.text.encoding can not be indexed because it done have any value

@Pablo_Sanchez : I am using it and will update you.

Any update on same . I am facing the issue

We are looking into it. Could you post a similar screenshot of your version from the Help menu?
image

You said that you work on 2016.3, but such version was never released (and we would need to know the version to debug the issue).

Capture
Please find the attached screen shot. Kindly tell what datatype of input and output needs to be selected during exccution.

Regards,
Shirish

Both should be strings.

ok. I selected the both as string but still facing the issue. I am waiting for ur reply.

It was internally tested on your exact version and it works with the below string.
Could you provide a sample input you are using? You can try with this string as input, just to confirm that this isn’t an issue:
ąłóńłŁŁÓĘ

RemoveChars_Encoding.xaml (7.0 KB)

I have taken outputstring as byte of string / string but still facinf many challenges. Kindly have a look into attached xaml file and assist me for resolving the issue.

Actually I have to read each excel row then if contains any foreign chars from below then replace them to UK chars from below list.

Foreign Characters UK replacement
á, ã, â, ä, ã, ầ, à, ậ, ả, ạ, ạ, å a
æ ae
ç, Č, č c
đ d
é, ê, é, è, ę, ế, ế, é, ế, ể, ệ, ë, ě, ě e
Ġ, ğ, Ġ, ġ g
ħ, h
í, î, ï, I, ì, í, ĩ i
ł l
ń, ñ, ň, ň n
ó, ö, ổ, ô, ợ, ồ, ø, ō o
ř r
ş, ś, š s
ß ss
ú, ü, ư, ứ, ù u
ý, ỳ y
ż, ž z

Regards,
Shirish

For that, I would suggest a simple Regex expression, like this one:
image

System.Text.RegularExpressions.Regex.Replace(inputString,"á|ã|â|ä|ã|ầ|à|ậ|ả|ạ|a|å","a")

If you have your table in Excel format, you can load it in and iterate through all rows using For Each Row activity, replacing all characters from that row with each iteration. The expression to use within the For Each Row would look like that:

System.Text.RegularExpressions.Regex.Replace(inputString,row.Item("Foreign Characters").ToString,row.Item("UK replacement").ToString)

@loginerror Thanks a lot for your help and assistance. I am able do it by ur help.

One thing I wanted to remove carriage return from cell of excel . Please find attached input and output sheet. How we can do the same

.Carriage return.xlsx (8.8 KB)

This simple .Replace should do the trick:

yourString.Replace(vbLf,"")

@loginerror

I have tried the above one but still not able to remove carriage return char. How we can check whether carrige return present in excel cell ?