Skip to main content

Removing special Characters

Hi Brian,
Love your site.
I'm writing an ideascript that takes an account name from within the ACCT_NAME field and creates a CSV file with the account name being the name of the file. An error occurs if there are special characters in the account name. How would you go about replacing all special charaters in the name with the underscore charater?  Keeping in mind that there may be more than one invalid charater in one account name. A name could be  A/P: Client-Data for example. In that case i would like the result to be  A_P_Client_Data
I hope my question is clear enough.
Thanks Brian 
Marc

Brian Element Wed, 03/19/2014 - 21:33

Hi Marc and welcome to the site.

One option is to create a virtual field to change the name of the field and replace all the special characters with an underscore.  I created a custom function that does just that, right now the custom function only allows for apha characters but it can be modified to allow for other characters.
As you are dealing with a script you could also adapt the function in the custom function so that you pass the name of the file through the function to replace all the special characters prior to saving the file.

Anyway, let me know if this is what you are working for.  Depending on what you want to do you can add this to your script.  So let me know how I can help out.

Brian Element Thu, 03/20/2014 - 06:54

Hi Marc, this is the code from the custom function. As you are using a script you can easily place it in there and run your file names through it prior to exporting them as a CSV file.

What the function does is it looks at the ASCII value of each letter (for ease I made all the characters uppercase so I only need to test for uppercase letters).  The ASCII codes 65 to 90 represent the letters A to Z, so if the character is not alpha then it is changed to an underscore, if it is an alpha it is not changed.  I wasn't sure if you wanted numbers also but that can be easily fixed just be expanding the if statement.

Option Explicit
Function replace_special(field As String) As String

	Dim iLen As Integer
	Dim sChar As String
	Dim sString As String
	Dim i As Integer
	
	iLen = Len(field)
	
	For i = 1 To iLen
		sChar = Mid(field, i, 1)
		If Asc(UCase(sChar)) < 65 Or Asc(UCase(sChar)) > 90 Then
			sString = sString + "_"
		Else
			sString = sSTring + sChar
		End If
	Next i

	replace_special = sString
End Function

 

meb135 Thu, 03/20/2014 - 08:31

Hi Brian,
For my purposes, creating a vitual field with cleaned-up records is the way to go. That being said, how would i adapt your script to accomplish this if my initial field name is ACCT_NAME and the virtual field name is ACCTNAME?
thanks.
Marc  

meb135 Thu, 03/20/2014 - 10:07

Brian,
I was able to adapt your script to my situation. Works great!!
Thank you.
Marc

meb135 Fri, 03/21/2014 - 08:12

Hi,
For some reason, the code was not working. I was receiving an error message when the character was a space and when a special charater was found, the code would place give me an error message and replace everything with a 0. Not sure why that was happening. So i modified the code to this:
iAcctName = ""
iLen = Len(iAcctName)
          For i = 1 to iLen
                    schar = mid(iAcctName, i, 1)
                    if sChar = " " then
                              sString = sString + " "
                    Elseif sChar = "/" or sChar = "\" or sChar = "*" or etc.....(all 13 charaters not recognized by IDEA) then
                               sString = sString + "_"
                    End if
                     sString = sString +sChar
          Next i
NewAcctName = sString

meb135 Mon, 03/24/2014 - 15:30

This is what it says:
Note: <?XML:NAMESPACE PREFIX = MadCap NS = "http://www.madcapsoftware.com/Schemas/MadCap.xsd" ?>
The IDEA ASCII version that you currently have installed will only read ASCII files, not Unicode files.

Brian Element Mon, 03/24/2014 - 19:19

Never seen that one before.  Also as I am using an ASCII version so I am not sure why it would give an error.

haimico2 Tue, 11/15/2022 - 09:54

Hey Brian!
I was just looking at this thread, how about replacing all special characters not on the field name but on all the content of a field. Lets say I have a field called description with many special characters on different rows and want them to dissapear. Could you help me with that please?
Thanks a lot!