topic Re: Search for blank/empty and NULL records in Python Questions

Search for blank/empty and NULL records

2Quiker — Thu, 18 Aug 2022 15:35:17 GMT

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

Re: Search for blank/empty and NULL records

Brian_Wilson — Thu, 18 Aug 2022 15:42:12 GMT

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.

Re: Search for blank/empty and NULL records

2Quiker — Thu, 18 Aug 2022 16:59:19 GMT

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

Re: Search for blank/empty and NULL records

2Quiker — Thu, 18 Aug 2022 17:51:53 GMT

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;

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.

Re: Search for blank/empty and NULL records

Brian_Wilson — Thu, 18 Aug 2022 18:25:59 GMT

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)