Strip attributes of entire table

[2Quiker]

I have a table that is generated else where. the problem i have is that the majority of the field attributes have different amount of spaces before and after the attribute. for example "____Blackhawk Sub No 1 _______". I have been manual using field calculator doing the !myfield!.lstrip, !myfield!.rstrip !myfield!.strip for each field and that sucks.

Is there a why to strip all the spaces infron/begining and at the end?

This table has both number and string.

I have been trying with the code below but i get an error on line 11. so i am thinking my expression is incorrect?

ERROR 000622: Failed to execute (Calculate Field). Parameters are not valid.

import arcpy
from datetime import datetime as d
startTime = d.now()

arcpy.MakeTableView_management("C:\Temp\ParAdminTable.dbf", "parAdmin")

for field in arcpy.ListFields("parAdmin", "*", "String"):  
      
            sqlFieldName = arcpy.AddFieldDelimiters("parAdmin", field)           
            calcSql= "' '.join( field.strip().split())"  
            arcpy.CalculateField_management("parAdmin",field,calcSql,"PYTHON_9.3","#")  

arcpy.TableToTable_conversion("parAdmin", "C:\Temp" , "ParAdmin_Test.dbf")

try:   
    print '(Elapsed time: ' + str(d.now() - startTime)[:-3] + ')'

except Exception, e:
    # If an error occurred, print line number and error message
    import traceback, sys
    tb = sys.exc_info()[2]
    print "Line %i" % tb.tb_lineno
    print e.message

[Zeke]

I believe you're supposed to pass in the character you want to remove to the strip() function. In your case, field.strip(' '). Note that this won't work on numeric fields, but they won't have blank spaces anyway.

[2Quiker]

I have changed the line

calcSql= "' '.join( field.strip().split())" 

to

calcSql= "' '.join( field.strip(' ').split())" 

But i am still getting the same error on the same line.

[FreddieGibson · Accepted solution]

You have two problems. 1) ListFields returns field objects and you're attempting to leverage these as strings when you need to leverage the name property of the field object. 2) your expression is missing the delimiters for the field calculation.

I've pasted a corrected copy of your code below along with a couple of alternatives that I thought of off the top of my head.

import arcpy


table = r"C:\Temp\ParAdminTable.dbf"


for field in arcpy.ListFields(table, "*", "String"):
    calcSql = "' '.join(!{0}!.strip().split())".format(field.name) 
    arcpy.management.CalculateField(table, field.name, calcSql, "PYTHON")
    
arcpy.conversion.TableToTable(table, r"C:\Temp", "ParAdmin_Test.dbf")


# Option 2 : Only removes extra spacing at begining and end
for field in [f.name for f in arcpy.Describe(table).fields if f.type == "String"]:
    exp = "!{0}!.strip()"
    arcpy.management.CalculateField(table, field, exp.format(field), "PYTHON")


# Option 3 : Remove all extra spaces
for field in [f.name for f in arcpy.Describe(table).fields if f.type == "String"]:
    exp = "' '.join([t for t in !{0}!.split(' ') if t not in ('', ' ', None)])"
    arcpy.management.CalculateField(table, field, exp.format(field), "PYTHON")

[2Quiker]

Code works awesome. only alter the output table?

[2Quiker]

my previous post didn't make much sense.

Current code alters the original and new tables

Is there a way to not alter the original table, only alter the exported one?

[FreddieGibson]

Export the table first and then execute the calculations against the newly generated table. In your original code this was how you were implementing your business logic.

[CCWeedcontrol]

TableToTable