[Solved] Replacing Accented Characters
-
- Posts: 3
- Joined: Wed Feb 17, 2016 7:24 pm
[Solved] Replacing Accented Characters
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.
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].
Reason: tagged [Solved].
OpenOffice 4.1.2 with MacOS 10.11.3
Re: Replacing Accented Characters
Just an idea:
Does your system get on with unicode like ?
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
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
Free Project: LibreOffice Songbook Architect (LOSA)
http://struckkai.blogspot.de/2015/04/li ... itect.html
-
- Volunteer
- Posts: 1160
- Joined: Mon Oct 08, 2007 1:26 am
- Location: France, Paris area
Re: Replacing Accented Characters
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
OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Replacing Accented Characters
Hi, and welcome to the forum.
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:
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: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.
Code: Select all
tr 'ÁÀÂÅÄÃáàâåäãÉÈÊËéèêëÍÌÎÏíìîïÑñÓÒÔÖÕóòôöõÚÙÛÜúùûüÝŸýÿ' 'AAAAAAaaaaaaEEEEeeeeIIIIiiiiNnOOOOOoooooUUUUuuuuYYyy'
tr '…' '…' <InFileName >OutFileNameIf 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).
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).
-
- Posts: 3
- Joined: Wed Feb 17, 2016 7:24 pm
Re: Replacing Accented Characters
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.
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
Re: Replacing Accented Characters
Here's at least a basic function for you to use in a macro.
Edit the oldliste and newliste as you need.
Here's is a thread with Calc macros:
viewtopic.php?f=21&t=2437
So perhaps this modified might do it:
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
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
Free Project: LibreOffice Songbook Architect (LOSA)
http://struckkai.blogspot.de/2015/04/li ... itect.html
-
- Posts: 3
- Joined: Wed Feb 17, 2016 7:24 pm
Re: Replacing Accented Characters
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.
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
Re: Replacing Accented Characters
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
Re: [Solved] Replacing Accented Characters
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?
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