Page 1 of 1

[Solved] Replacing Accented Characters

PostPosted: Wed Feb 17, 2016 7:31 pm
by GavinButler
I'm currently downloading a shipping list as a csv file from our store which needs to then be amended and imported into our Royal Mail Despatch manager.

We have an increasing amount of sales to Poland and other countries that use accented characters. The system we're using does not get on with these special characters when importing the cvs file and at the moment i'm manually changing the characters using find/replace. I'm an amateur at best when it comes to spread sheets, so I was wondering if any one could help me out with a macro or function or tool that will replace charactesr such as ó with o and ą with a for example.

Re: Replacing Accented Characters

PostPosted: Wed Feb 17, 2016 9:10 pm
by musikai
Just an idea:
Does your system get on with unicode like
Code: Select all   Expand viewCollapse view
ó
?

If your system supports unicode it would be easy to convert the whole strings to unicode by macro.
The whole string because I have no idea how to filter only "foreign" characters.

Here's a nice overview of different encodings of characters (didn't find an english website):
https://de.wikipedia.org/wiki/Hilfe:Sonderzeichenreferenz

Re: Replacing Accented Characters

PostPosted: Wed Feb 17, 2016 10:16 pm
by B Marcelly
When importing your csv file into Calc, select the correct encoding, e.g. Eastern Europe (ISO-8859-10).

Re: Replacing Accented Characters

PostPosted: Thu Feb 18, 2016 2:54 am
by MrProgrammer
Hi, and welcome to the forum.

GavinButler wrote:I'm currently downloading a shipping list as a csv file from our store …. I was wondering if any one could help me out with a macro or function or tool that will replace charactesr such as ó with o and ą with a for example.
Your CSV file is just a text file. It is easy to convert characters in a text file using the Unix command tr. (Your Mac runs a Unix operating system.) Open terminal and use a translate command like:
Code: Select all   Expand viewCollapse view
tr 'ÁÀÂÅÄÃáàâåäãÉÈÊËéèêëÍÌÎÏíìîïÑñÓÒÔÖÕóòôöõÚÙÛÜúùûüÝŸýÿ' 'AAAAAAaaaaaaEEEEeeeeIIIIiiiiNnOOOOOoooooUUUUuuuuYYyy'

You will need to add all of the accented characters which you'll encounter in the download from your store to the tr operands. I would have no idea what those might be. Specify the input and output files (they should not be the same file) using standard Unix shell syntax:
   tr '…' '…' <InFileName >OutFileName


If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Re: Replacing Accented Characters

PostPosted: Fri Feb 19, 2016 10:49 pm
by GavinButler
Hi all.

Sorry about the late reply. I've just talked to the people at Royal Mail and someone in their great wisdom decided that accented characters can't be used in the address column but they can be used in a separate lot of address columns marked as "Localised Addresses". This means heavy manipulation of the CSV file by creating separate columns to copy the address over. Needless to say it's absolutely bizarre why a world wide shipping company would run the system like this, especially when there are roads in the UK with accented characters not to mention people living here.

The file has to be unicode UTF-8. However they won't accept & # ; into the address fields.

The person who's going to be using this daily isn't particularly well versed in spreadsheets, so the only way I think I can get around this to make it as easy as possible is to make some sort of macro that replaces the accented files with an english equivalent. Is there something that can do the same as what MrProgrammer has suggested within a macro?

I appreciate your time and help.

Re: Replacing Accented Characters

PostPosted: Fri Feb 19, 2016 11:25 pm
by musikai
Here's at least a basic function for you to use in a macro.
Edit the oldliste and newliste as you need.

Code: Select all   Expand viewCollapse view
function convertcharacters(instring)
oldliste = array("Á","À","Â","Å","Ä","Ã","á","à","â","å","ä","ã","É","È","Ê","Ë","é","è","ê","ë","Í","Ì","Î","Ï","í","ì","î","ï","Ñ","ñ","Ó","Ò","Ô","Ö","Õ","ó","ò","ô","ö","õ","Ú","Ù","Û","Ü","ú","ù","û","ü","Ý","Ÿ","ý","ÿ")
newliste = array("A","A","A","A","A","A","a","a","a","a","a","a","E","E","E","E","e","e","e","e","I","I","I","I","i","i","i","i","N","n","O","O","O","O","O","o","o","o","o","o","U","U","U","U","u","u","u","u","Y","Y","y","y")
dostring = instring
for i=0 to uBound(oldliste)
searchtext = oldliste(i)
replacetext = newliste(i)
dostring = replace(dostring,searchtext, replacetext)
next i
convertcharacters = dostring
end function


Here's is a thread with Calc macros:
https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=2437

So perhaps this modified might do it:
Code: Select all   Expand viewCollapse view
Sub replacecharacters_Calc
Dim oDoc,aFind,aReplace,aRayCount,FandR,oSheet
oDoc = ThisComponent
aFind = Array("Á","À","Â","Å","Ä","Ã","á","à","â","å","ä","ã","É","È","Ê","Ë","é","è","ê","ë","Í","Ì","Î","Ï","í","ì","î","ï","Ñ","ñ","Ó","Ò","Ô","Ö","Õ","ó","ò","ô","ö","õ","Ú","Ù","Û","Ü","ú","ù","û","ü","Ý","Ÿ","ý","ÿ")
aReplace = Array("A","A","A","A","A","A","a","a","a","a","a","a","E","E","E","E","e","e","e","e","I","I","I","I","i","i","i","i","N","n","O","O","O","O","O","o","o","o","o","o","U","U","U","U","u","u","u","u","Y","Y","y","y")
aRayCount = 0
osheet = odoc.CurrentSelection.Spreadsheet
FandR = oSheet.createReplaceDescriptor
FandR.SearchCaseSensitive = true
FandR.SearchWords = false ' 1 to A AND 11 to AA
FandR.SearchRegularExpression = true
While aRayCount <= uBound(aFind)
FandR.setSearchString(aFind(aRayCount))
FandR.setReplaceString(aReplace(aRayCount))
aRayCount = aRayCount + 1
oSheet.ReplaceAll(FandR)
Wend
End Sub

Re: Replacing Accented Characters

PostPosted: Sat Feb 20, 2016 12:59 am
by GavinButler
Thanks for that last reply. I had no idea you could code macros like that. Only thought you could record actions you make. Like a said I'm not the most clued up on spreadsheets. Managed to write a little macro that checks the entire sheet and replaces all the foreign characters (of which there are way more than I thought there were!)

Thanks for your times guys. Code below if anyone else needs it.

Code: Select all   Expand viewCollapse view
sub ReplaceForiegnChars

Dim Doc As Object
Dim Sheet As Object
Dim ReplaceDescriptor As Object
Dim I As Integer
Dim I2 as Integer

Dim AccentedCharacters As String
Dim EnglishCharacters As String
Dim AccentedSearch as string
dim EnglishSearch as string
Dim CharLength As integer

AccentedCharacters = "ĄÂÃÄÀÁÅÆĆÇĘÈÉÊËÌÍÎÏŁÐŃÑÒÓÔÕÖØÙÚÛÜŚÝÞŹŻßąàáâãäåæćçęèéêëìíîïłðńñóòóôõöøùúûüśýþÿźżäöüúůýžÚŮÝŽéëïóöüÉËÏÓÖÜùûüÿàâæçéèêÙÛÜŸÀÂÆ"
EnglishCharacters =  "AAAAAAAACCEEEEEIIIILDNNOOOOOOUUUUSYpZZbaaaaaaaacceeeeeiiiilonnooooooouuuusybyzzaouuuyzUUYZeeioouEEIOOUuuuyaaaceeeUUUYAAA"

CharLength = Len(AccentedCharacters)

Doc = ThisComponent
Sheet = Doc.Sheets(0)
ReplaceDescriptor = Sheet.createReplaceDescriptor()


For I2 = 1 to CharLength



   AccentedSearch = mid(AccentedCharacters, I2, 1)
   EnglishSearch =  mid(EnglishCharacters, I2, 1)
   
   ReplaceDescriptor.SearchString = AccentedSearch
   ReplaceDescriptor.ReplaceString = EnglishSearch
   
      For I = 0 to Doc.Sheets.Count - 1
         Sheet = Doc.Sheets(I)
         Sheet.ReplaceAll(ReplaceDescriptor)
      Next I

Next I2



end sub

Re: Replacing Accented Characters

PostPosted: Tue Sep 10, 2019 11:14 pm
by osscar
Was looking to do the same. This did it for me, thanks!

musikai wrote:Here's at least a basic function for you to use in a macro.

So perhaps this modified might do it:
Code: Select all   Expand viewCollapse view
Sub replacecharacters_Calc
Dim oDoc,aFind,aReplace,aRayCount,FandR,oSheet
oDoc = ThisComponent
aFind = Array("Á","À","Â","Å","Ä","Ã","á","à","â","å","ä","ã","É","È","Ê","Ë","é","è","ê","ë","Í","Ì","Î","Ï","í","ì","î","ï","Ñ","ñ","Ó","Ò","Ô","Ö","Õ","ó","ò","ô","ö","õ","Ú","Ù","Û","Ü","ú","ù","û","ü","Ý","Ÿ","ý","ÿ")
aReplace = Array("A","A","A","A","A","A","a","a","a","a","a","a","E","E","E","E","e","e","e","e","I","I","I","I","i","i","i","i","N","n","O","O","O","O","O","o","o","o","o","o","U","U","U","U","u","u","u","u","Y","Y","y","y")
aRayCount = 0
osheet = odoc.CurrentSelection.Spreadsheet
FandR = oSheet.createReplaceDescriptor
FandR.SearchCaseSensitive = true
FandR.SearchWords = false ' 1 to A AND 11 to AA
FandR.SearchRegularExpression = true
While aRayCount <= uBound(aFind)
FandR.setSearchString(aFind(aRayCount))
FandR.setReplaceString(aReplace(aRayCount))
aRayCount = aRayCount + 1
oSheet.ReplaceAll(FandR)
Wend
End Sub

Re: [Solved] Replacing Accented Characters

PostPosted: Sun Oct 06, 2019 12:47 pm
by BuckoA51
Hi, there seems to be a bug in the above macro and I can't figure it out :(

Whenever I run it, anywhere where I get two "s" characters next to each other (just the standard s, no diacritics or anything), this is replaced by the letter "b".

Anyone any ideas?