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-30-2013

You should have what you need to get it working.

If not, after these two lines:

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

could you print them out to see what is getting reported?

print RMS
print Type

also, I don't believe "Type" is a reserved word, but it is generally a bad idea to use variable with names the same as built in functions as it can mask or overwrite the built in function(s).

Type(RMS) in python gives the "type" of object assigned to the RMS variable.

Also, what is the format of the "Regions" layer? FGDB, oracle spatial table, etc.? I ask as there is a bug in the update cursor and will not work on external database tables that are not registered.

In this case, I have to actually use the oracle module for python.

R_

JamesSmith7 · ‎07-31-2013

I copied the data from the Type field to a new field, named Classification. The Regions layer is a feature class.

print RMS
print Classification

After running the print RMS & Classification the script completes with no errors or results. I just receive the typical start time, end time, running, and completed notifications. If I add arcpy.AddMessage("Good") after the
RMS = arcpy.GetParameterAsText(0)
Classification = arcpy.GetParameterAsText(1)

Then, I receive the same as before with "Good" added. So, the input parameters seem to be ok.

I notice that upon running the script as a tool in ArcToolbox, that if the parameter is changed to optional, and no input value is provided, # is listed in the Executing line. Is the # a place holder?

JamesCrandall · ‎07-31-2013

I copied the data from the Type field to a new field, named Classification. The Regions layer is a feature class.

print RMS
print Classification

After running the print RMS & Classification the script completes with no errors or results. I just receive the typical start time, end time, running, and completed notifications. If I add arcpy.AddMessage("Good") after the
RMS = arcpy.GetParameterAsText(0)
Classification = arcpy.GetParameterAsText(1)

Then, I receive the same as before with "Good" added. So, the input parameters seem to be ok.

Just to follow up with Rhett's suggestion of the print/addmessage statements...

I am not sure if both are seen (in PythonWin and the Geoprocessor results window maybe?). But rather than:


arcpy.AddMessage("Good")

do this instead:


arcpy.AddMessage(str(RMS))
arcpy.AddMessage(str(Classification))

...and see if these values are seen in the results window.

JamesSmith7 · ‎07-31-2013

The values returned this time are the ones entered in the script tool.

I entered 1 for RMS and F for Classification

The script runs and returns the start and end time, running and completed, with the additional comments:

1
F

I am still missing something with the UpdateCursor. Any other suggestions?

The whereClause works fine, until it encounters a null value. Therefore, leads me to believe that I still have an issue with the UpdateCursor.

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))

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

 cursor = arcpy.da.UpdateCursor(fc,"RMS", RMS + " IS NULL")
 for row in cursor:
  row[0] = ""
  cursor.updateRow(row)
 del row, cursor
 
except:
 print arcpy.GetMessages()

JamesSmith7 · ‎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

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

except:
 print arcpy.GetMessages()

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_

JamesSmith7 · ‎07-31-2013

I am using the script as a tool in ArcToolbox. Do I need to set the Region fc to text, if it is not being used as a entered value? For instance, I am using RMS = arcpy.GetParameterAsText(0) and Classification = arcpy.GetParameterAsText(1) as values to be entered, selected, and zoomed to. If so, do I have to include a # at the end, such as 0, 1, 2, etc?

JamesSmith7 · ‎08-01-2013

The UpdateCursor works when the selection is cleared. Then, proceeds to blank all values within a field. So, I went ahead and selected the desired records within the field and changed them from null to blank via Field Calculator. Now, the whereClause runs as intended.