topic Re: Change Nulls to Blanks in Python Questions

Change Nulls to Blanks

JamesSmith7 — Tue, 30 Jul 2013 13:29:47 GMT

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

Re: Change Nulls to Blanks

MathewCoyle — Sat, 11 Dec 2021 14:26:55 GMT

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

Re: Change Nulls to Blanks

JamesSmith7 — Tue, 30 Jul 2013 14:22:45 GMT

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.

Re: Change Nulls to Blanks

MathewCoyle — Tue, 30 Jul 2013 15:00:44 GMT

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?

Re: Change Nulls to Blanks

JamesSmith7 — Tue, 30 Jul 2013 15:53:25 GMT

The script completes with no error codes returned.

Re: Change Nulls to Blanks

RhettZufelt — Sat, 11 Dec 2021 14:26:58 GMT

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.

Re: Change Nulls to Blanks

JamesSmith7 — Tue, 30 Jul 2013 16:56:29 GMT

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.

Re: Change Nulls to Blanks

RhettZufelt — Sat, 11 Dec 2021 14:27:01 GMT

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_

Re: Change Nulls to Blanks

JamesSmith7 — Sat, 11 Dec 2021 14:27:03 GMT

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

Re: Change Nulls to Blanks

JamesSmith7 — Sat, 11 Dec 2021 14:27:06 GMT

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.

Re: Change Nulls to Blanks

RhettZufelt — Tue, 30 Jul 2013 20:27:13 GMT

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_

Re: Change Nulls to Blanks

JamesSmith7 — Wed, 31 Jul 2013 10:49:00 GMT

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?

Re: Change Nulls to Blanks

JamesCrandall — Sat, 11 Dec 2021 14:27:09 GMT

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.

Re: Change Nulls to Blanks

JamesSmith7 — Sat, 11 Dec 2021 14:27:12 GMT

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

Re: Change Nulls to Blanks

JamesSmith7 — Sat, 11 Dec 2021 14:27:14 GMT

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

Re: Change Nulls to Blanks

RhettZufelt — Wed, 31 Jul 2013 16:38:34 GMT

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_

Re: Change Nulls to Blanks

JamesSmith7 — Wed, 31 Jul 2013 16:50:13 GMT

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?

Re: Change Nulls to Blanks

JamesSmith7 — Thu, 01 Aug 2013 18:44:04 GMT

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.