Search for blank/empty and NULL records

[2Quiker]

I am trying to print empty/blank or Null records with the following but when nothing is printed and there is empty/blank and Null records in the layer. I would also like to print the number of empty/blank or Null records.

fc = "C:/Temp/Test.gdb/feature Class"

fields = ["OBJECTID", "PN1"]

for field in fields:
    where = field + " IS " " OR NULL"
    try:      
        with arcpy.da.SearchCursor(fc, fields, where) as cursor:
            for row in cursor:
                print("OBJECTID {0}").format(row[0]) +  " has a Empty/ Blank or NULL value in field " + field
    except RuntimeError:
        pass

del cursor

counter = 0
with arcpy.da.SearchCursor(fc,["OBJECTID","PN1"]) as cursor:
    for row in cursor:
        if row[1] in ["", " ", None]:
            counter += 1
            print('{}  {} is null {} times'.format(row[0],row[1],counter))
        else:
            pass

[Brian_Wilson · Accepted solution]

Without testing it right this minute it looks like you have something like this

field IS " " OR NULL

and I think that might evaluate as

(field IS " ") OR (NULL)

which means nothing to me. You would need to write it as

field = " " OR field IS NULL

Then the problem becomes that the first part only matches if the string contains exactly one space. You probably want an empty string,

field = "" OR field IS NULL

I have actually had datasets that had fields with one space in them, those can drive you crazy. You could probably find a neat SQL way to strip extra spaces and then check the length of the result string but I don't know how to do that in SQL. In python for example this gives 0 as the return: len("      ".strip())

Personally I have taken to using pandas for things like this, it does the hard parts internally where I don't see them and does them 10x as fast. It's another learning curve though. Later for that maybe.

[2Quiker]

I was able to get something printed with the suggestion but it repeats.

fields = ["OBJECTID", "PN1"]

for field in fields:
    where = "PN1 = ' ' OR PlN1 IS  NULL"
    try:      
        with arcpy.da.SearchCursor(fc, fields, where) as cursor:
            for row in cursor:
                #print("Null value in row {0}".format(row[0], row[1]))
                print ("OBJECTID {0}".format(row[0]) + "has a NULL value in field")
##                result = arcpy.GetCount_management(fc).getOutput(0)
##                print ('{} has {} records'.format(fc, result[0]))
    except RuntimeError:
        pass

del cursor

OBJECTID 44has a NULL value in field
OBJECTID 47has a NULL value in field
OBJECTID 88has a NULL value in field
OBJECTID 106has a NULL value in field
OBJECTID 108has a NULL value in field
OBJECTID 121has a NULL value in field
OBJECTID 130has a NULL value in field
OBJECTID 182has a NULL value in field
OBJECTID 210has a NULL value in field
OBJECTID 44has a NULL value in field
OBJECTID 47has a NULL value in field
OBJECTID 88has a NULL value in field
OBJECTID 106has a NULL value in field
OBJECTID 108has a NULL value in field
OBJECTID 121has a NULL value in field
OBJECTID 130has a NULL value in field
OBJECTID 182has a NULL value in field
OBJECTID 210has a NULL value in field

[2Quiker]

I was able to get what I need with the following. My only other question is how do I get the counter to on top of the OBJECTID rows?

fields = ["OBJECTID", "PN1"]
where = "PN1 = ' ' OR PN1 IS  NULL"
counter = 0
with arcpy.da.SearchCursor(fc, fields, where) as cursor:
    for row in cursor:
        if row[1]in ["", " ", None]:
            counter += 1
            print ("OBJECTID {0}".format(row[0]) + "has a NULL value in field")
            
print ("{} {} records have blank/empty or NULL records".format(row[0],counter))

Results;

OBJECTID 44has a NULL value in field
OBJECTID 47has a NULL value in field
OBJECTID 88has a NULL value in field
OBJECTID 106has a NULL value in field
OBJECTID 108has a NULL value in field
OBJECTID 121has a NULL value in field
OBJECTID 130has a NULL value in field
OBJECTID 182has a NULL value in field
OBJECTID 210has a NULL value in field
210 9 records have blank/empty or NULL records

How do i put the "210 9 records have blank/empty or NULL records" on top like so

210 9 records have blank/empty or NULL records.

OBJECTID 44has a NULL value in field
OBJECTID 47has a NULL value in field
OBJECTID 88has a NULL value in field
OBJECTID 106has a NULL value in field
OBJECTID 108has a NULL value in field
OBJECTID 121has a NULL value in field
OBJECTID 130has a NULL value in field
OBJECTID 182has a NULL value in field
OBJECTID 210has a NULL value in field

[Brian_Wilson]

You could accumulate the messages in a string,

msg = ''
count = 0
for i in range(1,10):
  count += 1
  msg = f'record {i}\n'
print("total records =", count)
print(msg)