This site uses cookies to improve your experience. I'll assume you're ok with this.
Read more

PYTHON GIS / EXCEL - List empty fields within features contained in database

0

 


In this article you will learn how to make a list of the empty fields that can have the features inside a spatial database using Python in a simple way.



If you need to make a control of the fields not filled within the elements (Feature or Shapefile, and tables) contained in the database that you have been developing during the execution of a project, either to fill them or to justify why they are empty, this procedure will be very helpful, and as far as I know, there is no way to do this in ArcGIS directly.  


If you want to list the empty fields of the elements of a database, follow the steps below


By way of clarification, the following procedure considers as empty fields those that are null, blank, filled with 0, -1 or 999; whether they are numeric or alphanumeric fields.


How to make a list of empty fields in the features of a spatial database (File geodatabase .gdb or Personal geodatabase .mdb)


Step 0: Make a backup of the database (You never know what can happen).


Step 1: Identify the path that represents the database to examine in the operating system explorer.


Step 2: Open ArcMap (A blank mxd file).


Step 3: Open the Python window in the Standard toolbar.


Muestra la ubicación de la ventana Python dentro de la barra de herramientas Standard (estándar)


Step 4: Copy in a notepad the following code and replace the text C:\EJEMPLO\EJEMPLO.GDB in line #11 by the path of the database identified in step 1. On the other hand replace in line #09 C:\EJEMPLO by the path where the list will be saved in .txt format. If you want to modify the name of the output file, you can change the word “ListFields” in line #10 by the name of your choice (without removing the quotes).


IMPORTANT NOTE: This script has always given me problems using very long paths so I recommend working in a folder at the root of a storage unit (a).



Ejemplo de donde ubicar la base de datos dentro de una unidad de almacenamiento






import arcpy             #01
import os             #02
import sys             #03
import fileinput     #04
                    #05
reload(sys)             #06
sys.setdefaultencoding("utf-8")     #07
                    #08
outputDir = r"C:\EJEMPLO"     #09
outputName = "ListFields"     #10
A = r"C:\EJEMPLO\EJEMPLO.gdb"     #11
textFile = (outputDir + os.sep + outputName + ".txt")     #12
                    #13
outFile = open(textFile, "w")     #14
                    #15
def listFcsInGDB(gdb):     #16
    arcpy.env.workspace = gdb     #17
    tables = arcpy.ListTables()     #18
                        #19
    for table in tables:     #20
        fields = arcpy.ListFields(table)     #21
        problem_fields_set = set()     #22
        for field in fields:     #23
            with arcpy.da.SearchCursor(table, field.name) as cursor:     #24
                for row in cursor:     #25
                    field_value = row[0]     #26
                    if field_value is None or + \
                    (isinstance(field_value, (int, float)) and \
                    field_value == 0 or field_value == -1 or \
                    field_value == 999) or str(field_value).strip() == "" \
                    or str(field_value) == "0" or str(field_value) == '0' \
                    or str(field_value) == '-1' or str(field_value) == '999' \
                    or str(field_value) == '':     #33
                        problem_fields_set.add(field.name)     #34
        for problem_field in problem_fields_set:     #35
            outFile.write("{0}, {1}, {2}\n".format("TABLE", table, problem_field))     #36
                    #37
    for fds in arcpy.ListDatasets('', 'feature') + ['']:     #38
        if fds == '':     #39
            for fc in arcpy.ListFeatureClasses():     #40
                fields = arcpy.ListFields(fc)     #41
                problem_fields_set = set()     #42
                for field in fields:     #43
                    with arcpy.da.SearchCursor(fc, field.name) as cursor:     #44
                        for row in cursor:     #45
                            field_value = row[0]     #46
                            if field_value is None or + \
                            (isinstance(field_value, (int, float)) and \
                            field_value == 0 or field_value == -1 or \
                            field_value == 999) or str(field_value).strip() == "" \
                            or str(field_value) == "0" or str(field_value) == '0' \
                            or str(field_value) == '-1' or str(field_value) == '999' \
                            or str(field_value) == '':     #53
                                problem_fields_set.add(field.name)     #54
                for problem_field in problem_fields_set:     #55
                    outFile.write("{0}, {1}, {2}\n".format("GDB", fc, problem_field))     #56
        else:             #57
            for fc in arcpy.ListFeatureClasses('', '', fds):     #58
                fields = arcpy.ListFields(fc)     #59
                problem_fields_set = set()     #60
                for field in fields:     #61
                    with arcpy.da.SearchCursor(fc, field.name) as cursor:     #62
                        for row in cursor:     #63
                            field_value = row[0]     #64
                            if field_value is None or + \
                            (isinstance(field_value, (int, float)) and \
                            field_value == 0 or field_value == -1 or \
                            field_value == 999) or str(field_value).strip() == "" \
                            or str(field_value) == "0" or str(field_value) == '0' \
                            or str(field_value) == '-1' or str(field_value) == '999' \
                            or str(field_value) == '':     #71
                                problem_fields_set.add(field.name)     #72
                for problem_field in problem_fields_set:     #73
                    outFile.write("{0}, {1}, {2}\n".format(fds, fc, problem_field))     #74
                    #75
gdb = A                     #76
fcs = listFcsInGDB(gdb)     #77
outFile.close()             #78
                    #79
headers = 'DATASET_OR_CLASS, FEATURE_CLASS, PROBLEM_FIELDS'.split()     #80
for line in fileinput.input([outputDir + os.sep + outputName + ".txt"], inplace=True):     #81
    if fileinput.isfirstline():             #82
        print '\t'.join(headers)     #83
    print line,             #84


                                                                                            



Step 5: Copy the modified code into the Python window and press the Enter key twice.


Step 6: Once the .txt file is generated (a), open it, select everything with CTR+E (b) and copy the text with CTR+C (c).



Muestra grafica del resultado del archivo de texto y ejemplo de como copiar el contenido



Step 7: Open an Excel file, in the text import section select as separator the comma character (,) as shown in the following image.


Ejemplo de como importar el contenido del archivo de texto a excel





Ejemplo de como obtener el listado de los los campos vacíos en los elementos de la base de datos espacial llevado a EXCEL



This way you will have a list of the empty fields that have your features in the GDB, I hope you enjoyed this article, soon I will upload a video explaining the procedure that you can consult in this blog. Save my blog between your favorite links, I will be uploading many more tricks of this style, remember that you can send me your concerns in the contact page or leave your comment, I will be attentive to respond.

You may like these posts

Newer Posts Older Posts

No comments