topic Re: how to edit out illegal characters in columns in large tables (>1000 files) in Data Management Questions

how to edit out illegal characters in columns in large tables (>1000 files)

LarryPearson — Thu, 15 Jun 2017 18:51:19 GMT

I'm attempting to join two tables.

The join validation report indicates several fields with illegal characters. I'm editing a large county file with over 1000 parcels. How to remove characters such as "-" or "/" for a whole column of data without doing it manually?

Re: how to edit out illegal characters in columns in large tables (>1000 files)

DanPatterson_Retired — Thu, 15 Jun 2017 20:13:20 GMT

how many illegal characters? If you can use the field calculator a simple !YourFieldName!.replace('-','') using a python parser would be a simple approach. For many replacements a small function (def) could be written

Re: how to edit out illegal characters in columns in large tables (>1000 files)

TedKowal — Sat, 11 Dec 2021 19:21:02 GMT

To add on to Dan's comment. If you have a list of special characters to remove you can do it in the field calculater inside a def with something similiar to .....

>>> import string
>>> StringThatNeedsWork = "The - Time ! was well f$$$### spent   don't, you agree? 12345% 1.234"
>>> removethesechars = ['$','#','%','-']
>>> StringThatNeedsWork.translate(None,''.join(removethesechars))


"The  Time ! was well f spent   don't, you agree? 12345 1.234"
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: how to edit out illegal characters in columns in large tables (>1000 files)

mfcallahan — Sat, 11 Dec 2021 19:21:05 GMT

If by illegal characters you mean all non-alphanumeric characters, you can use a list comprehension with str.isalnum():

testString = 'foo-bar#baz?qux@127/\\9]'
testStringCleaned = "".join(x for x in testString if x.isalnum()) # foobarbazqux1279‍‍‍‍

Found on Stack Overflow - Turn a string into a valid filename?

Re: how to edit out illegal characters in columns in large tables (>1000 files)

TedKowal — Fri, 16 Jun 2017 15:20:06 GMT

Note that using .isalnum() will strip out significant '." and spaces as well testString = " North Highway Route 2.1" ---> "NorthHighwayRoute21" if that is ok then it is a quick easy way!

Re: how to edit out illegal characters in columns in large tables (>1000 files)

mfcallahan — Fri, 16 Jun 2017 15:43:23 GMT

Sometimes I do this, and replace and illegal chars or spaces with an underscore:

testStringCleaned = "".join([x if x.isalnum() else "_" for x in testString]) ‍# foo_bar_baz_qux_127__9_‍

Re: how to edit out illegal characters in columns in large tables (>1000 files)

AbdullahAnter — Fri, 16 Jun 2017 16:40:36 GMT

Ted , What about your answer in same question? Does it work?

https://community.esri.com/message/688777-odd-special-character-in-field-value

Re: how to edit out illegal characters in columns in large tables (>1000 files)

JoshuaBixby — Sat, 11 Dec 2021 19:21:07 GMT

In additional to 7.1. string — Common string operations — Python 2.7.13 documentation , 7.2. re — Regular expression operations — Python 2.7.13 documentation can also be used:

>>> StringThatNeedsWork = "The - Time ! was well f$$$### spent   don't, you agree? 12345% 1.234"
>>> re.sub("[$,#,%,-]","", StringThatNeedsWork)
"The  Time ! was well f spent   don't you agree? 12345 1.234"
>>> ‍‍‍‍

Re: how to edit out illegal characters in columns in large tables (>1000 files)

DanPatterson_Retired — Fri, 16 Jun 2017 16:57:41 GMT

Of course, we are all missing the line in the title where the OP say '1000 files' ... which pretty much makes our field calculator expression suggestions less than satisfactory. In such cases, it is best to have the person that put them in, in the first place, remove them

Re: how to edit out illegal characters in columns in large tables (>1000 files)

TedKowal — Mon, 19 Jun 2017 14:43:44 GMT

Yes that works just fine, however, that solution is run on the database and not gis software, and specifically it removes "ALL" characters except for the ones with ASCII values between 0 and 255. So spaces and decimal values are not stripped. In this case, I am assuming the individual wanted to use the field calculate and only remove certain characters.

Re: how to edit out illegal characters in columns in large tables (>1000 files)

TedKowal — Mon, 19 Jun 2017 14:44:53 GMT

I like yours better! I have a long way to go in learning Python!

Re: how to edit out illegal characters in columns in large tables (>1000 files)

TedKowal — Mon, 19 Jun 2017 14:49:57 GMT

If they are simple text files? Python - Way to recursively find and replace string in text files - Stack Overflow

Re: how to edit out illegal characters in columns in large tables (>1000 files)

mfcallahan — Mon, 19 Jun 2017 17:12:31 GMT

I prefer the whitelist approach when cleaning up file names, ID columns, etc. Blacklisting chars you don't want like "$%&*@!" etc still would allow things like æ Ñ █ ╬ all of which are valid ascii characters, but probably not charterers you'd want in a primary key or filename.