[Solved] Replacing Accented Characters

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
GavinButler
Posts: 3
Joined: Wed Feb 17, 2016 7:24 pm

[Solved] Replacing Accented Characters

Post 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.
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
musikai
Volunteer
Posts: 294
Joined: Wed Nov 11, 2015 12:19 am

Re: Replacing Accented Characters

Post by musikai »

Just an idea:
Does your system get on with unicode like

Code: Select all

ó
?

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:Son ... enreferenz
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/li ... itect.html
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Replacing Accented Characters

Post by B Marcelly »

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
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Replacing Accented Characters

Post 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

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, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
GavinButler
Posts: 3
Joined: Wed Feb 17, 2016 7:24 pm

Re: Replacing Accented Characters

Post 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.
OpenOffice 4.1.2 with MacOS 10.11.3
musikai
Volunteer
Posts: 294
Joined: Wed Nov 11, 2015 12:19 am

Re: Replacing Accented Characters

Post 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

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:
viewtopic.php?f=21&t=2437

So perhaps this modified might do it:

Code: Select all

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/li ... itect.html
GavinButler
Posts: 3
Joined: Wed Feb 17, 2016 7:24 pm

Re: Replacing Accented Characters

Post 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

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

Re: Replacing Accented Characters

Post 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

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
BuckoA51
Posts: 1
Joined: Sun Oct 06, 2019 12:44 pm

Re: [Solved] Replacing Accented Characters

Post 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?
LibreOffice Version: 6.2.7.1 (x64) Windows 10 X64
Post Reply