[Solved] Replacing Accented Characters

[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.

[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

[B Marcelly]

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

[MrProgrammer]

Hi, and welcome to the forum.

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.

[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.

[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

[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

[osscar]

Was looking to do the same. This did it for me, thanks!

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

[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?