Removing special Characters

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.

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

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  

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

Hi Marc, that is great to hear.  So what was your final solution?

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

Marc,  just a thought, are you using the Unicode IDEA?  That might be what the problem is.

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.

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

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!