[Solved] Replacing Accented Characters

Creating a macro - Writing a Script - Using the API

[Solved] Replacing Accented Characters

Postby GavinButler » Wed Feb 17, 2016 7:31 pm

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.
Last edited by Hagar Delest on Sat Feb 20, 2016 1:53 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.2 with MacOS 10.11.3
GavinButler
 
Posts: 3
Joined: Wed Feb 17, 2016 7:24 pm

Re: Replacing Accented Characters

Postby musikai » Wed Feb 17, 2016 9:10 pm

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
Win7 Pro, Lubuntu 15.10, LO 4.4.7, OO 4.1.3
Free Project: LibreOffice Songbook Architect (LOSA)
http://struckkai.blogspot.de/2015/04/libreofficesongbookarchitect.html
musikai
Volunteer
 
Posts: 254
Joined: Wed Nov 11, 2015 12:19 am

Re: Replacing Accented Characters

Postby B Marcelly » Wed Feb 17, 2016 10:16 pm

When importing your csv file into Calc, select the correct encoding, e.g. Eastern Europe (ISO-8859-10).
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Replacing Accented Characters

Postby MrProgrammer » Thu Feb 18, 2016 2:54 am

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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3900
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Replacing Accented Characters

Postby GavinButler » Fri Feb 19, 2016 10:49 pm

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.
OpenOffice 4.1.2 with MacOS 10.11.3
GavinButler
 
Posts: 3
Joined: Wed Feb 17, 2016 7:24 pm

Re: Replacing Accented Characters

Postby musikai » Fri Feb 19, 2016 11:25 pm

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
Win7 Pro, Lubuntu 15.10, LO 4.4.7, OO 4.1.3
Free Project: LibreOffice Songbook Architect (LOSA)
http://struckkai.blogspot.de/2015/04/libreofficesongbookarchitect.html
musikai
Volunteer
 
Posts: 254
Joined: Wed Nov 11, 2015 12:19 am

Re: Replacing Accented Characters

Postby GavinButler » Sat Feb 20, 2016 12:59 am

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
OpenOffice 4.1.2 with MacOS 10.11.3
GavinButler
 
Posts: 3
Joined: Wed Feb 17, 2016 7:24 pm

Re: Replacing Accented Characters

Postby osscar » Tue Sep 10, 2019 11:14 pm

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
OpenOffice 4.1.6 on MacOS
osscar
 
Posts: 1
Joined: Tue Sep 10, 2019 10:39 pm

Re: [Solved] Replacing Accented Characters

Postby BuckoA51 » Sun Oct 06, 2019 12:47 pm

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?
LibreOffice Version: 6.2.7.1 (x64) Windows 10 X64
BuckoA51
 
Posts: 1
Joined: Sun Oct 06, 2019 12:44 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests