topic Re: Using Search Cursor with Table to Excel Tool ArcPy in Python Questions

Using Search Cursor with Table to Excel Tool ArcPy

PatrickMcKinney1 — Sat, 11 Dec 2021 12:57:44 GMT

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

Re: Using Search Cursor with Table to Excel Tool ArcPy

WesMiller — Sun, 12 Jun 2016 00:35:49 GMT

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

Re: Using Search Cursor with Table to Excel Tool ArcPy

DanPatterson_Retired — Sat, 11 Dec 2021 12:57:46 GMT

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'

Re: Using Search Cursor with Table to Excel Tool ArcPy

WesMiller — Sun, 12 Jun 2016 02:38:25 GMT

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.

Re: Using Search Cursor with Table to Excel Tool ArcPy

DanPatterson_Retired — Sun, 12 Jun 2016 06:12:26 GMT

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

Re: Using Search Cursor with Table to Excel Tool ArcPy

JoshuaBixby — Sun, 12 Jun 2016 15:06:42 GMT

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.

Re: Using Search Cursor with Table to Excel Tool ArcPy

DanPatterson_Retired — Sun, 12 Jun 2016 16:58:49 GMT

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

Re: Using Search Cursor with Table to Excel Tool ArcPy

PatrickMcKinney1 — Mon, 13 Jun 2016 14:13:37 GMT

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

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

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

Re: Using Search Cursor with Table to Excel Tool ArcPy

PatrickMcKinney1 — Mon, 13 Jun 2016 14:15:02 GMT

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?

Re: Using Search Cursor with Table to Excel Tool ArcPy

PatrickMcKinney1 — Mon, 13 Jun 2016 14:15:47 GMT

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

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

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

Re: Using Search Cursor with Table to Excel Tool ArcPy

DanPatterson_Retired — Mon, 13 Jun 2016 14:26:40 GMT

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

Re: Using Search Cursor with Table to Excel Tool ArcPy

RichardFairhurst — Mon, 13 Jun 2016 14:30:35 GMT

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.

Re: Using Search Cursor with Table to Excel Tool ArcPy

PatrickMcKinney1 — Mon, 13 Jun 2016 15:42:20 GMT

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.

Re: Using Search Cursor with Table to Excel Tool ArcPy

PatrickMcKinney1 — Mon, 13 Jun 2016 15:45:07 GMT

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?

Re: Using Search Cursor with Table to Excel Tool ArcPy

RichardFairhurst — Sat, 11 Dec 2021 12:57:49 GMT

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.

Re: Using Search Cursor with Table to Excel Tool ArcPy

RebeccaStrauch__GISP — Sat, 11 Dec 2021 12:57:52 GMT

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.

Re: Using Search Cursor with Table to Excel Tool ArcPy

PatrickMcKinney1 — Mon, 13 Jun 2016 16:05:44 GMT

Thanks for the code.