Using Search Cursor with Table to Excel Tool ArcPy

PatrickMcKinney1 · ‎06-10-2016

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())

JoshuaBixby · ‎06-12-2016

With Lines 20, 23, 26, 29, and 32; I recommend you either add a second backslash, change the backslash to forward slash, or use raw notation with the second string. Although it has been working for you so far, using a single backslash the way you are is asking for headaches at some point since a backslash is an escape character in Python.

See String literals in the Python documentation:

The backslash (\) character is used to escape characters that otherwise have a special meaning, such as newline, backslash itself, or the quote character. String literals may optionally be prefixed with a letter 'r' or 'R'; such strings are called raw strings and use different rules for interpreting backslash escape sequences.

Now that I think about it, using the os.path module is probably the best advice I can give for working with file system paths.

DanPatterson_Retired · ‎06-12-2016

and who could forget that brilliant blog post Filenames and file paths in Python that serves to point out some of the foibles of people trying to find the true path

RichardFairhurst · ‎06-13-2016

Clearly the BuffDist and Units fields you are using to modify the spreadsheet names do not contain unique combinations for each feature. If the first and second feature both have a BuffDist of 50 and a Units of 2 then the code will work for the first feature and fail for the second feature with a report that the spreadsheet name already exists. You need to make sure that the spreadsheet file names are always unique. You should add a counter value to each set of spreadsheet names or some other field to the file name that ensures that each set of spreadsheets is uniquely named.

PatrickMcKinney1 · ‎06-13-2016

The layer the select by location is based upon will be the output of a multi-ring buffer. And I would like to add the Name field to the output. This, combined with the user selecting the folder to place the spreadsheets should take care of this issue. However, I agree adding a counter would he helpful. I couldn't figure this out. Perhaps you could provide some sample code in addition to your advice?

RebeccaStrauch__GISP · ‎06-13-2016

To elaborate on what Richard Fairhurst mentioned, creating unique files for each "row in cursor" could be as easy as

filecnt = 0
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)  
    filecnt += 1
    dynamicFileName = '_{0}_{1}_{2}.xls'.format(str(row.getValue("BUFFDIST")), row.getValue("UNITS"), filecnt)

But are you trying to create a new file for each row (for each type), or are you wanting to create one output file for each type with all the cursor results? If you want one output for each, you could write all the results to arrays, then add them to a file to create each excel file at the end. I do similar things like that. I've attached a sample that I right to a csv fiel for each loop, then do the arcpy.TableToExcel_conversion at the end to go from the csv to the Excel file.

edit: I got sidetracke before I submitted my comment...Richard already has the counter covered.

RichardFairhurst · ‎06-13-2016

Adding a counter only requires the code modification below:

try:  
   
    cursor = arcpy.SearchCursor(sara, fields="BUFFDIST; UNITS") # old cursor syntax  
  
    counter = 0
    for row in cursor:  
          counter += 1
          dynamicFileName = '_{0}_{1}_{2}_.xls'.format(str(row.getValue("BUFFDIST")), row.getValue("UNITS"), counter)

If you have ArcGIS 10.1 or greater you really must convert the old cursor to the new da cursor to make it perform tolerably.

PatrickMcKinney1 · ‎06-13-2016

Thanks for the code.