Using Search Cursor with Table to Excel Tool ArcPy

[PatrickMcKinney1]

Summary:

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

Problem:

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.

Code:

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'


try:


    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
        #print(buffAppend)
        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]
    arcpy.AddError(e.args[0])
    print(arcpy.GetMessages())

[WesMiller]

line 52 str takes one argument you've given it two

[DanPatterson_Retired]

if the fields return values, then it should work

>>> a = 'make_a_name_called_{0}_{1}_.xls'.format(10, "mm")
>>> a
'make_a_name_called_10_mm_.xls'

[PatrickMcKinney1]

I need {0} to pull from a Double field, not hardcode a value in.  Do I need to use the string conversion method?  Or will it automatically force it into a string because the variable is already a string?

[PatrickMcKinney1]

dynamicFileName = '_{0}_{1}_.xls'.format(str(row.getValue("BUFFDIST")), row.getValue("UNITS"))

1st argument = str(row.getValue("BUFFDIST"))

2nd argument = row.getValue("UNITS")

[WesMiller]

When I first looked at it, it looked like both fields were in the same str. I don't see where you've used outputFolder as part of you're output paths.

[DanPatterson_Retired]

report the error message, perhaps its source lies within the section you have omitted

[PatrickMcKinney1]

This is the message I received, which I provided in the post:

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)

[DanPatterson_Retired]

If nothing gets selected by the select by location, then nothing gets created and I suggest you print out what this is returning before using it in the table to excel 'MHIDD' + dynamicFileName  why is that being prepended to the dynamicFileName

[PatrickMcKinney1]

If nothing gets created, then there would be no records in the spreadsheet.  The tool will be used by a Public Safety department. They use the spreadsheets to get information for their reports.

The goal for the filename will include what kind of facility the list is being created for, as well as the buffer distance and units.  The end user could be running the tool on a feature with 3 records (1000-ft, 1500-ft, and 2000-ft).  In this example, the would get a spreadsheet for the 1000-ft buffer selected features, 1500-ft buffer selected features, and 2000-ft buffer selected features for each kind of vulnerable facility.