nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Remove extra spaces from string using the field calculator

MattGoodman

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.

Vind meer berichten getagd met

Accepted answers

All comments

Luke_Pinner

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]

calculator.png

CMorneau

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

RichardFairhurst

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

CraigPrisland

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.

rzufelt

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_

jskinner

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.

rzufelt

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.

DanPatterson_Retired

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

CraigPrisland

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!

jskinner

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

CraigPrisland

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.

Luke_Pinner

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

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.