Change Nulls to Blanks

JamesSmith7 · ‎07-30-2013

I need to change Nulls(<Null>) to Blanks("") for a field in an attribute table. The field is a string named RMS.

fieldList = arcpy.ListFields(fc, "", "String")  for field in fieldList:   updateRows = arcpy.da.UpdateCursor(fc, RMS + " IS NULL", "", RMS)  for updateRow in updateRows:   updateRow.setValue(RMS, "")   updateRows.updateRow(updateRow)  del row, cursor

RhettZufelt · ‎07-31-2013

The values entered are returned. I entered 1 for RMS and F for Classification.

The initial whereClause runs fine, until a null value is encountered. I believe the issue is still with the UpdateCursor. I have gone back and attempted to work with the previous suggestions, but to not avail. The results are still the same, no errors, and no results. I know how to fix the null vs blank problem using field calculator, but not with a script. Other suggestions?

import arcpy  RMS = arcpy.GetParameterAsText(0) Classification = arcpy.GetParameterAsText(1)  mxd = arcpy.mapping.MapDocument("CURRENT") df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0] fc = arcpy.mapping.ListLayers(mxd, "Region", df)[0]  arcpy.AddMessage(str(RMS)) arcpy.AddMessage(str(Classification))   #Logic try:  whereClause = ''' "RMS" = '{0}' AND "Classification" = '{1}' '''.format(RMS, Classification)  arcpy.SelectLayerByAttribute_management(fc, "NEW_SELECTION", whereClause)  df.extent = fc.getSelectedExtent()  df.scale = df.scale*1.1  arcpy.SelectLayerByAttribute_management(fc, "CLEAR_SELECTION", whereClause)   sql = "{0}".format(arcpy.AddFieldDelimiters(Region, RMS, Classification)) + " IS NULL"  # not sure what the output of this is,is it valid sql?  in either case, Region variable is not defined          cursor = arcpy.da.UpdateCursor(fc, "RMS", sql)  # the region layer has been set to variable fc.  for row in cursor:   row[0] = ""   cursor.updateRow(row)  del row, cursor  except:  print arcpy.GetMessages()

If it were me, I'd make a copy of my data and try to get the updateCursor working WITHOUT the where clause ( cursor = arcpy.da.UpdateCursor(fc, "RMS") ).
run it with row[0] = "test"
then with row[0] = "" # of course, all this assumes the "Region" field is a text field...

Once you get it working for ALL rows in the table, then I'd figure out the proper where clause to put in there.
A lot of time, select/copy/paste each line in the IDLE window and running it will often give an idea of what is going wrong. Also, in the IDLE, you can type "print variable" (I.e. >>>print RMS ) at any time to see the value that is currently assigned to it. Also "type(variable)" will give you the variable type to ensure it is the proper input for a tool.

On another note, you said you could calculate it, but want to do it with python, why not:

arcpy.CalculateField_management(fc, "RMS", "\\"", "PYTHON_9.3")

Also, your selectLayerbyAttributes is putting a selection on the fc before the cursor. Documentation for UdateCursor doesn't say if it honors selections or not, but you might try to clear selection before the updatecursor.
R_

View solution in original post

MathewCoyle · ‎07-30-2013

I'm not sure what you need a field list for when you are just changing one field with a known name. Here is how I would go about it.

import arcpy

field = "RMS"
cursor = arcpy.da.UpdateCursor(fc, field, "{0} IS NULL".format(arcpy.AddFieldDelimiters(fc, field)))
for row in cursor:
    row[0] = ""
    cursor.updateRow(row)
del row, cursor

JamesSmith7 · ‎07-30-2013

Initially, I was going to create a list, but then determined that I needed to update two fields. So, if I get help with the syntax, I can just recreate the for loop twice. Guess, I could have taken the list out.

The suggested code is not working yet. So, I am going to go through my code and see if something else is missing.

MathewCoyle · ‎07-30-2013

Initially, I was going to create a list, but then determined that I needed to update two fields. So, if I get help with the syntax, I can just recreate the for loop twice. Guess, I could have taken the list out.

The suggested code is not working yet. So, I am going to go through my code and see if something else is missing.

Are you getting an error message or is the script just not changing the values?

JamesSmith7 · ‎07-30-2013

The script completes with no error codes returned.

RhettZufelt · ‎07-30-2013

Is the "IS NULL" being passed with quotes? That will break an IS NULL query.

Maybe something like this:

import arcpy

field = "RMS"
sql = "{0}".format(arcpy.AddFieldDelimiters(fc,field)) + " IS NULL"

cursor = arcpy.da.UpdateCursor(fc, field, sql)
for row in cursor:
    row[0] = ""
    cursor.updateRow(row)
del row, cursor.

R_

JamesSmith7 · ‎07-30-2013

The IS NULL statement works, because I added an arcpy.AddMessage afterwards and the value was returned. The script seems to hang on the UpdateCursor.

RhettZufelt · ‎07-30-2013

Perhaps because it is formatted incorrectly.

updateRows = arcpy.da.UpdateCursor(fc, RMS + " IS NULL", "", RMS)

UpdateCursor (in_table, field_names, {where_clause}       , {spatial_reference}, {explode_to_points}, {sql_clause})

so maybe something like:

updateRows = arcpy.da.UpdateCursor(fc,"RMS",RMS + " IS NULL")

It appears as if you where clause and fields are transposed and no quotes around the field attribute so was looking for the variable RMS.
of course, with your where clause, you still need to define RMS, so maybe that part doesn't matter.

R_

JamesSmith7 · ‎07-30-2013

Provided is my entire code.

import arcpy

RMS = arcpy.GetParameterAsText(0)
Type = arcpy.GetParameterAsText(1)

mxd = arcpy.mapping.MapDocument("CURRENT")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
fc = arcpy.mapping.ListLayers(mxd, "Region", df)[0]

#Logic
try:
 whereClause = ''' "RMS" = '{0}' AND "Type" = '{1}' '''.format(RMS, Type)
 arcpy.SelectLayerByAttribute_management(fc, "NEW_SELECTION", whereClause)
 df.extent = fc.getSelectedExtent()
 df.scale = df.scale*1.1

 sql = "{0}".format(arcpy.AddFieldDelimiters(fc,field)) + " IS NULL"

 cursor = arcpy.da.UpdateCursor(fc, field, sql)
 for row in cursor:
  row[0] = ""
  cursor.updateRow(row)
 del row, cursor


except:
 print arcpy.GetMessages()

JamesSmith7 · ‎07-30-2013

Perhaps because it is formatted incorrectly.
updateRows = arcpy.da.UpdateCursor(fc, RMS + " IS NULL", "", RMS)
UpdateCursor (in_table, field_names, {where_clause}       , {spatial_reference}, {explode_to_points}, {sql_clause})

so maybe something like:

updateRows = arcpy.da.UpdateCursor(fc,"RMS",RMS + " IS NULL")
It appears as if you where clause and fields are transposed and no quotes around the field attribute so was looking for the variable RMS.
of course, with your where clause, you still need to define RMS, so maybe that part doesn't matter.

R_

I itend to implement your suggested changes tomorrow morning. I got stuck in a meeting this afternoon.