Creating Multiple Field Indexes Using Arcpy

Question

I want to create a code that will take a feature class' fields and create indexes for each of them (because it is tedious to do it by hand). I'm trying to use arcpy.ListFields to get the fields and put the result as the second parameter for arcpy.AddIndex. I am using a FOR loop and each field is called "f." If I just use f as the parameter then I get the vague error "RuntimeError: Object: Error in executing tool." if I use f.name or just name (created from f.name) then I get "raise e ExecuteError: ERROR 000308: Invalid field type." The ArcGIS examples show a string of multiple names (probably for a composite index) separated by semicolans which will be read as distinct fields when the tool is executed. Why isn't my field being read as a field when I execute the tool? The code reads the file correctly and returns the names of all of the fields, so that shouldn't be the problem? Do you have any ideas or do these two tools not work well with each other?

fc = r"C:\data\featureclass"
flist = arcpy.ListFields(fc)
for f in flist:
    name = str(f.name)
    print name
    arcpy.AddIndex_management (fc, f , "inx_" + name, "NON_UNIQUE", "NON_ASCENDING")

Answer 1

To get past this error:

ExecuteError: ERROR 000308: Invalid field type

try changing:

arcpy.AddIndex_management (fc, f , "inx_" + name, "NON_UNIQUE", "NON_ASCENDING")

to:

arcpy.AddIndex_management (fc, f.name , "inx_" + name, "NON_UNIQUE", "NON_ASCENDING")

This tool is expecting a field name, or list of field names, and you are providing it with a field object. ListFields creates a list of field objects and not a list of field names.

If this just leads you to another error that the advice of @MichaelMiles-Stimson's comment does not get you past, then be sure to research/ask that as a separate question. However, I think they may be the only two things to take care of.

Answer 2

AddIndex tool does not allow the Shape and Object ID fields to be indexed. The other problem is that f.name is a unicode string object rather than a regular string which the tool does not accept. To solve the problem, change the unicode object to normal text and omit the Shape and Object ID fields. Here is the working code:

fc = r"C:\data\featureclass"
flist = arcpy.ListFields(fc)
for f in flist:
    name = str(f.name)
    if name == "OBJECTID":
        print "no oid"  
    elif name == "GlobalID":
        print "no guid"
    elif name == "SHAPE":
        print "no shape"
    else:
        arcpy.AddIndex_management (fc, name , "inx_" + name, "NON_UNIQUE", "NON_ASCENDING")

Answer 3

This is the solution I use. 1. Run the code below to create a file with all the columns from datasets, feature classes and tables. 2. Open the CSV file and delete columns you do not want to index. 3. Run the indexing code to create the indexes.

I write to a CSV file, but you can use a text file or use the XLRD excel module. Either of the three will work. The CSV and XRLD allows me to work with the data in excel. I use different sort options to isolate columns to delete.

import arcpy,csv,os

#### Connection to FGDB
FGDB = r"C:\*\*\*.gdb"

#### Leaving the variable the same but using an SDE connection
##FGDB = r"C:\*\*\Connection to *.sde"

arcpy.env.workspace = FGDB

## Path to csv file
csvExportFolder = "C:\\*\\*\\*.csv"

## Set to write csv file
csvFile = csv.writer(open(csvExportFolder,"wb"),delimiter = ",")

###########################################################################################################
###########################################################################################################
## Get list of columns from feature classes in a Datasets
datasets = arcpy.ListDatasets(feature_type='feature')

for ds in datasets:
## List feature class in datasets
    for fc in arcpy.ListFeatureClasses(feature_dataset=ds):
        path = os.path.join(arcpy.env.workspace, ds, fc)
##    List fields in feature class
        fields = arcpy.ListFields("%(path)s" % vars())
##    Loop through fields
        for field in fields:
            colmm = ("{0}".format(field.name))
            columns = FGDB + ',' + ("%(ds)s" % vars()) + ',' + ("%(fc)s" % vars()) + ',' + ("%(colmm)s" % vars())
            csvFile.writerow([FGDB,("%(ds)s" % vars()),("%(fc)s" % vars()),("%(colmm)s" % vars())])
            print ("%(columns)s" % vars())

###########################################################################################################
###########################################################################################################
## Get list of columns from feature classes
FCs = arcpy.ListFeatureClasses()

## Loop through feature classes in list
for FC in FCs:
    pathandtable = FGDB + '\\' + ("%(FC)s" % vars())
##    print pathandtable

##    List fields in feature class
    fields = arcpy.ListFields("%(pathandtable)s" % vars())
##    Loop through fields
    for field in fields:
        colmm = ("{0}".format(field.name))
        columns = FGDB + ',' + ("%(FC)s" % vars()) + ',' + ("%(colmm)s" % vars())
        csvFile.writerow([FGDB,"FC",("%(FC)s" % vars()),("%(colmm)s" % vars())])
        print ("%(columns)s" % vars())

###########################################################################################################
###########################################################################################################
##Get list of columns from tables
tables = arcpy.ListTables()

## Loop through tables in list
for table in tables:
##    print table
    pathandtable = FGDB + '\\' + ("%(table)s" % vars())
##    print pathandtable

##    List fields in tables
    fields = arcpy.ListFields("%(pathandtable)s" % vars())
##    Loop through fields
    for field in fields:
        colmm = ("{0}".format(field.name))
##        print ("%(colmm)s" % vars())
        columns = FGDB + ',' + ("%(table)s" % vars()) + ',' + ("%(colmm)s" % vars())
        csvFile.writerow([FGDB,"Table",("%(table)s" % vars()),("%(colmm)s" % vars())])
        print ("%(columns)s" % vars())

When you have creating the final csv file for indexing run the indexing code below.

import arcpy,csv,os,time

PathFGDB = "C:\\*\\ *"
FGDB_name = "*.gdb"
DDash = "\\"
CSVFile = "*.csv"

WorkSpace = PathFGDB + DDash + FGDB_name
arcpy.env.workspace = WorkSpace

csvExportFolder = PathFGDB + DDash + CSVFile

e = 0
max_rows = sum(1 for line in open("%(csvExportFolder)s" % vars ()))
print max_rows

with open(("%(csvExportFolder)s" % vars()), 'rb') as f:
    reader = csv.reader(f)
    for line in reader:
        ##print line
        DS1 = line[1];FC1 = line[2];CNme = line[3];INme = line[4]
##        print ("This is the Feature Class = %(FC1)s" % vars())
##        print ("This is the Column Name = %(CNme)s" % vars())
##        print ("This is the Index Name = %(INme)s" % vars())
        # Create index
        if DS1 == "FC" or DS1 == "Table":
            pathTB = PathFGDB + DDash + FGDB_name + DDash + ("%(FC1)s" % vars())
        else:
            pathTB = PathFGDB + DDash + FGDB_name + DDash + ("%(DS1)s" % vars()) + DDash + ("%(FC1)s" % vars())
        ##print pathTB
        x = []
        indexes = arcpy.ListIndexes("%(pathTB)s" % vars())
        for index in indexes:
            pa2 = (format(index.name)); ##print pa2
            x.extend([("%(pa2)s" % vars())])
        indnam = ["OBJECTID","SHAPE"]
        indexlist = (x); ##print indexlist
        if (pa2 <> indnam):
            if any(INme in s for s in indexlist):
              e += 1; l = max_rows - e
              print ("EXISTS row %(e)s of %(max_rows)s rows and %(l)s rows left FC = %(FC1)s and Column = %(INme)s" % vars())
            else:
              e += 1; l = max_rows - e
              print ("NOT EXIST row %(e)s of %(max_rows)s rows and %(l)s rows left FC = %(FC1)s and Column = %(INme)s" % vars())
              ### "NON_UNIQUE", "NON_ASCENDING"     "UNIQUE", "ASCENDING"
              arcpy.AddIndex_management(pathTB, ("%(CNme)s" % vars()), ("%(INme)s" % vars()), "UNIQUE", "ASCENDING")
    else:
        # No more lines to be read from file
        print ("No more indexs to create")

Also, I copied the column name for the index name in the csv and I deleted the following _. I created 2,383 indexes in 41 minutes.