AnsweredAssumed Answered

Using Search Cursor with Table to Excel Tool ArcPy

Question asked by pmckinneyccpa on Jun 10, 2016
Latest reply on Jun 13, 2016 by pmckinneyccpa


I am developing an ArcGIS Toolbox tool that will do the following:

  • User selects a feature class that is a buffer of a site
    • The number of features in this input layer will always be different
  • User selects an output folder for files created
  • Various layers in the same geodatabase are turned into Feature Layers
  • A search cursor is created for the input dataset
  • A for in loop is performed on the cursor (each record of the feature class)
  • In the loop, a select by location (intersect) is performed between the input layer and various layers
  • The selected features from each layer are converted to a spreadsheet




The goal is to add field attributes (buffer distance and units) to each spreadsheet.  As the code stands now, no spreadsheets are created and I get the following message/error:


Executing: MakeFeatureLayer "\\CCPASR07\ncgs$\Scripts\ArcGIS Geoprocessing\SARA Tool\SARA_Tool_DEV.gdb\Site_Education" schools_lyr # # "OBJECTID OBJECTID VISIBLE NONE;SHAPE SHAPE VISIBLE NONE;Source_DatasetDesc Source_DatasetDesc VISIBLE NONE;Permanent_Identifier Permanent_Identifier VISIBLE NONE;Source_Originator Source_Originator VISIBLE NONE;LoadDate LoadDate VISIBLE NONE;Name Name VISIBLE NONE;Owner Owner VISIBLE NONE;Address1 Address1 VISIBLE NONE;City City VISIBLE NONE;State State VISIBLE NONE;Zip Zip VISIBLE NONE;PocName PocName VISIBLE NONE;PocOrganization PocOrganization VISIBLE NONE;PocTel1 PocTel1 VISIBLE NONE;PocEmail PocEmail VISIBLE NONE;Comments Comments VISIBLE NONE;State_FacilityID State_FacilityID VISIBLE NONE;FacilityID FacilityID VISIBLE NONE;FType FType VISIBLE NONE;FCode FCode VISIBLE NONE;Site_Plan_Link Site_Plan_Link VISIBLE NONE;Valid_Status Valid_Status VISIBLE NONE;Address2 Address2 VISIBLE NONE;Phone2 Phone2 VISIBLE NONE;Fax Fax VISIBLE NONE;NMFCID NMFCID VISIBLE NONE"

Start Time: Fri Jun 10 14:33:24 2016

Succeeded at Fri Jun 10 14:33:24 2016 (Elapsed Time: 0.21 seconds)


If I do not try to access the fields from the cursor, the first set of spreadsheets (from the first record) are created, but then the tool fails because of an already existing name for the spreadsheet.


Any tips for making this tool run as intended are much appreciated.





Note: I do have the import arcpy code at the beginning.


# User selects risk radii layer as input for analysis
# SARA Risk Radius
#sara = arcpy.GetParameterAsText(0)

# User selects output folder
# outputFolder = arcpy.GetParameterAsText(1)
# Hard-coded for testing
outputFolder = r'\\CCPASR07\ncgs$\Scripts\ArcGIS Geoprocessing\SARA Tool\excel'

# Vulnerable Facilities Sites
# Create Feature Layers for analysis
# Repath for live testing/deployment

# file geodatabase containing vulnerable facilities
vsGdb = r'\\CCPASR07\ncgs$\Scripts\ArcGIS Geoprocessing\SARA Tool\SARA_Tool_DEV.gdb'
# Assisted Living
assistedLiving = vsGdb + '\EOC_AssistedLiving'
arcpy.MakeFeatureLayer_management(assistedLiving, 'assistedLiving_lyr')
# Daycares
daycares = vsGdb + '\EOC_Daycare'
arcpy.MakeFeatureLayer_management(daycares, 'daycares_lyr')
# Health Medical Sites
medical = vsGdb + '\Site_HealthMedical'
arcpy.MakeFeatureLayer_management(medical, 'medical_lyr')
# MHIDD Sites
mhIdd = vsGdb + '\EOC_MHIDD_Facility'
arcpy.MakeFeatureLayer_management(mhIdd, 'mhIdd_lyr')
# Schools
schools = vsGdb + '\Site_Education'
arcpy.MakeFeatureLayer_management(schools, 'schools_lyr')

# Hard-coded for input parameter for testing
sara = vsGdb + '\CarlislePumpShools'


    cursor = arcpy.SearchCursor(sara, fields="BUFFDIST; UNITS") # old cursor syntax

    for row in cursor:

        # Having this block active fails tools
        # Goal is to add buffer distance and units to file name of excel spreadsheet
        dynamicFileName = '_{0}_{1}_.xls'.format(str(row.getValue("BUFFDIST")), row.getValue("UNITS"))

        # Assisted Living
        # Select Assisted Living sites that intersect SARA risk radius
        arcpy.SelectLayerByLocation_management('assistedLiving_lyr', 'INTERSECT', sara, "", 'NEW_SELECTION')
        # Export selected features to Excel
        arcpy.TableToExcel_conversion('assistedLiving_lyr', 'AssistedLiving' + dynamicFileName)

        # Daycares
        # Select Daycares sites that intersect SARA risk radius
        arcpy.SelectLayerByLocation_management('daycares_lyr', 'INTERSECT', sara, "", 'NEW_SELECTION')
        # Export selected features to Excel
        arcpy.TableToExcel_conversion('daycares_lyr', 'Daycares' + dynamicFileName)

        # Health Medical
        # Select Health Medical sites that intersect SARA risk radius
        arcpy.SelectLayerByLocation_management('medical_lyr', 'INTERSECT', sara, "", 'NEW_SELECTION')
        # Export selected features to Excel
        arcpy.TableToExcel_conversion('medical_lyr', 'Medical' + dynamicFileName)

        # MHIDD
        # Select MHIDD sites that intersect SARA risk radius
        arcpy.SelectLayerByLocation_management('mhIdd_lyr', 'INTERSECT', sara, "", 'NEW_SELECTION')
        # Export selected features to Excel
        arcpy.TableToExcel_conversion('mhIdd_lyr', 'MHIDD' + dynamicFileName)

        # Schools
        # Select Schools sites that intersect SARA risk radius
        arcpy.SelectLayerByLocation_management('schools_lyr', 'INTERSECT', sara, "", 'NEW_SELECTION')
        # Export selected features to Excel
        arcpy.TableToExcel_conversion('schools_lyr', 'Schools' + dynamicFileName)

    del cursor

except Exception:
    e = sys.exc_info()[1]