Oracle EGDB: Find junk values in all tables and columns (and log them in a table)

Bud

Oracle 18c/10.7.1 geodatabase:

I want to find junk values in all tables and columns (in a specific schema/owner/user):

1. Loop through all tables
2. Loop through each number and text column
3. If a column has junk values, then add a record to a log table that summarizes the column's data integrity issue (i.e., insert a rolled-up record into the log table with a COUNT column, not a row for each problem value):
   • 0 (number)
   • " " (text; single space)
   • "  " (text; double space)
   • "0" (text)
   • "-" (text)
   • "NULL" (text; not a true null)
   • "<NULL>" (text; not a true null)

Thanks.

JohannesLindner

 

def check_for_junk_values(layer_or_table):
    fields = [f.name for f in  arcpy.ListFields(layer_or_table)]
    junk_values = [0, " ", "  ", "-", "0", "NULL", "<NULL>", "<Null>"]
    data = [dict(zip(fields, row)) for row in arcpy.da.SearchCursor(layer_or_table, fields)]  # [{"Field1": 0, "Field2": 1}, {"Field1": 5, "Field2": 2}]
    counts = dict()
    for row in data:
        for field, value in row.items():
            if value in junk_values:
                key = (field, value)
                try:
                    counts[key] += 1
                except KeyError:
                    counts[key] = 1
    return counts



tables = ["TestPoints", "TestLines", "TestPolygons", "TestTable"]
# you can also use paths to your tables, or list them automatically with arcpy.ListTables() / arcpy.ListFeatureclasses()

log_table = arcpy.management.CreateTable("memory", "JunkLogTable")
arcpy.management.AddField(log_table, "TABLE_NAME", "TEXT")
arcpy.management.AddField(log_table, "COLUMN_NAME", "TEXT")
arcpy.management.AddField(log_table, "VAL_COUNT", "SHORT")
arcpy.management.AddField(log_table, "VALUE", "TEXT")

with arcpy.da.InsertCursor(log_table, ["TABLE_NAME", "COLUMN_NAME", "VALUE", "VAL_COUNT"]) as cursor:
    for table in tables:
        result = check_for_junk_values(table)
        for key, count in result.items():
            col = key[0]
            val = key[1].__repr__()  # __repr__() to get the quotes
            cursor.insertRow([table, col, val, count])