topic Re: Remove extra spaces from string using the field calculator in Python Questions

Remove extra spaces from string using the field calculator

MattGoodman — Mon, 19 Dec 2011 15:14:14 GMT

Hi, I'm looking for an elegant python expression that I can be used within ArcMap's field calculator to find all instances of consecutive spaces within a string and replace them with a single space.

Example:
[INDENT]Let's say in a field called "Address" we have the following value (the product of poor concatenation):[/INDENT]
[INDENT]"_123_____Main__St,____Townsville,______CA_98463___"[/INDENT]

[INDENT]It should become "123 Main St, Townsville, CA 98463"[/INDENT]

I know that .strip will get rid of the leading/trailling spaces. How to I get a find/replace approach to continue looping through until all the double-spaces have been reduced to single spaces? Also, I have tried using .split, but get an error in ArcMap that "Field is not Nullable".

Any help would be appreciated.

Re: Remove extra spaces from string using the field calculator

Luke_Pinner — Sat, 11 Dec 2021 13:22:44 GMT

fieldvalue=' '.join(fieldvalue.strip().split())
#in case fieldvalue=='', change empty string to space to avoid "Field is not Nullable" error
if not fieldvalue: fieldvalue=' '

Re: Remove extra spaces from string using the field calculator

CraigPrisland — Wed, 24 Jul 2013 12:55:11 GMT

Hello,

I have been using this Python script on one field at a time using the Field Calculator and it is working very well, thank you. What I am now looking for is to see if there is a Python script that could be run that would look at all fields in a particular feature class and remove any extra spaces in any of those fields. Any assistance would be greatly appreciated.

Thank you in advance.

Re: Remove extra spaces from string using the field calculator

JakeSkinner — Sat, 11 Dec 2021 13:22:47 GMT

Hi Craig,

You could accomplish this with the following code:

fc = "Parcels"

for field in arcpy.ListFields(fc, "*", "String"):
    with arcpy.da.UpdateCursor(fc, field.name) as cursor:
        for row in cursor:            
            value = row[0]
            value = str(value).rstrip()
            row[0] = value
            cursor.updateRow(row)

del cursor

Re: Remove extra spaces from string using the field calculator

CraigPrisland — Thu, 25 Jul 2013 15:42:14 GMT

Hi Jake,

Thanks for the quick response and script. I tried using this script that you provided by replacing "parcels" with the path to my feature class. I then added this script into ArcToolbox and ran it. The ran successfully, but when I opened the feature class, the extra spaces did not appear to get removed. Is there anything else that I am missing? Thanks again!

Re: Remove extra spaces from string using the field calculator

DanPatterson_Retired — Thu, 25 Jul 2013 17:27:42 GMT

Did you reload the fc after the script? Perhaps you should be working with fc's loaded in as layers rather than running fc from a file on disk

Re: Remove extra spaces from string using the field calculator

RhettZufelt — Sat, 11 Dec 2021 13:22:50 GMT

Hi Jake,

Thanks for the quick response and script. I tried using this script that you provided by replacing "parcels" with the path to my feature class. I then added this script into ArcToolbox and ran it. The ran successfully, but when I opened the feature class, the extra spaces did not appear to get removed. Is there anything else that I am missing? Thanks again!

You said that post 2 worked for you, but only on a single feature. You can combine the two and use that same coding on the entire FC if you want. something like this:

arcpy.MakeFeatureLayer_management("C:/data.gdb/parcels", "parcels_lyr")


for field in arcpy.ListFields("parcels_lyr", "*", "String"):

        sqlFieldName = arcpy.AddFieldDelimiters("parcels_lyr", field)         
        calcSql= "' '.join( field.strip().split())"
        arcpy.CalculateField_management("parcels_lyr",field,calcSql,"PYTHON_9.3","#")

This seems to do it for me.

R_

If you wanted to use cursors, post 4 could be used, but use this calc function instead.

Re: Remove extra spaces from string using the field calculator

JakeSkinner — Thu, 25 Jul 2013 18:33:54 GMT

Hi Jake,

Thanks for the quick response and script. I tried using this script that you provided by replacing "parcels" with the path to my feature class. I then added this script into ArcToolbox and ran it. The ran successfully, but when I opened the feature class, the extra spaces did not appear to get removed. Is there anything else that I am missing? Thanks again!

Are you working with an SDE, File, or Personal Geodatabase? If you are working with an SDE feature class, you may have to refresh the version using the Refresh tool on the Versioning toolbar.

Re: Remove extra spaces from string using the field calculator

RhettZufelt — Sat, 11 Dec 2021 13:22:52 GMT

Hi Craig,

You could accomplish this with the following code:

fc = "Parcels"

for field in arcpy.ListFields(fc, "*", "String"):
    with arcpy.da.UpdateCursor(fc, field.name) as cursor:
        for row in cursor:            
            value = row[0]
            value = str(value).rstrip()
            row[0] = value
            cursor.updateRow(row)

del cursor

Am I missing something in this code? It seems like this would just "strip" the whitespace from the ends and still leave all the internal spaces?

R_

Re: Remove extra spaces from string using the field calculator

CraigPrisland — Fri, 26 Jul 2013 14:06:55 GMT

Am I missing something in this code? It seems like this would just "strip" the whitespace from the ends and still leave all the internal spaces?

R_

Yes, only the whitespace from the ends get stripped with this code and the internal spaces are still present. I am using this code against file and personal geodatabases.

Re: Remove extra spaces from string using the field calculator

RichardFairhurst — Sat, 11 Dec 2021 13:22:55 GMT

I know this is asking for a python method, but just in case it helps someone, the VB Script method to strip out all double spaces and any leading or trailing spaces is:

Output = [MyField] ' Replace MyField with the actual text field name
If Not IsNull(Output) then
  Output = Trim(Output)
  If Output > "" Then
    Do While InStr(1, Output, "  ")
      Output = Replace(Output, "  ", " ")
    Loop
  Else
    Output = " "  ' Avoid Field Is Not Nullable Error
  End If
Else
  Output = " "  ' Field allowed Nulls, use of this option replaces them with white space.
End If

Re: Remove extra spaces from string using the field calculator

ChristopherMorneau — Sat, 11 Dec 2021 13:22:58 GMT

fieldvalue=' '.join(fieldvalue.strip().split())
#in case fieldvalue=='', change empty string to space to avoid "Field is not Nullable" error
if not fieldvalue: fieldvalue=' '

I've been trying to set this up in the Field Calculator dialog, but I'm obviously missing something. Thus far, all I've achieved is a syntax error message.

My situation is very similar to what Matt Goodman originally posted on 12-19-2011. In my case, the field from which I want to remove extra spaces is entitled MailAddres. What should I enter for the Expression & Pre-Logic Script Code fields to make this work?

Maybe a more fundamental question is: is there a Python equivalent to the TRIM(text) function found in MS Excel?

Thank you,

Christopher J. Morneau

Re: Remove extra spaces from string using the field calculator

Luke_Pinner — Sat, 11 Dec 2021 13:23:01 GMT

Create a function in the pre-logic script code block e.g.:

def trim(fieldvalue):
    fieldvalue=' '.join(fieldvalue.strip().split())
    # in case fieldvalue=='', change empty string to space 
    # to avoid "Field is not Nullable" error
    if not fieldvalue: fieldvalue=' '
    return fieldvalue

[ATTACH=CONFIG]29091[/ATTACH]