1

So here's my script tool code in its entirety:

import arcpy
import datetime
import os
import sys
import itertools
import traceback


#Set Environment Variables
arcpy.env.overwriteOutput = True
arcpy.env.workspace = arcpy.GetParameterAsText(1)

#Collect user info from ArcGIS
PTE_layer = arcpy.GetParameterAsText(0)
rlf = arcpy.GetParameterAsText(2)
output_folder = arcpy.GetParameterAsText(3)
pdf = arcpy.GetParameter(4)
excel = arcpy.GetParameter(5)

#Set usefuls for later
today = datetime.datetime.today()
mxd = arcpy.mapping.MapDocument("CURRENT")  
df  = arcpy.mapping.ListDataFrames(mxd)[0]

class ProjectPTE:

    def __init__(self, CIP_ID = None, answer = None):
        self.CIP_ID = CIP_ID
        self.Yes = 0
        self.No = 0
        self.OwnerChange = 0
        self.Other = 0
        self.add(answer)

    def add(self,answer):
        if answer == "Y":
            self.Yes += 1
        elif answer == "N":
            self.No += 1
        elif answer == "New Ownership - Need PTE":
            self.OwnerChange += 1
        else:
            self.Other += 1

    def compare(self,other):
        try:    
            if self.CIP_ID == other.CIP_ID:
                return True
            else:
                return False               
        except AttributeError:
            return False


#Create cursor to move through PTE layer
fields = ["CIP_ID","PTE_Rcvd"]

#Main loop
#Using the ProjectPTE class above to define the rules for comparison,
# initialization, and updating, the loop goes through each row in the PTE
# layer and adds it's information to the project list.  This is accomplished
# by creating a ProjectPTE out of the current row and comparing that ProjectPTE
# with the existing ProjectPTEs in the project list.
project_list = []
pte_count = 0
with arcpy.da.SearchCursor(PTE_layer,fields) as PTE_cursor:
    for row in PTE_cursor:
        pte_count += 1
        CIP_ID = row[0]
        PTE_Rcvd = row[1]
        #Use current row CIP_ID to initialize new ProjectPTE
        current = ProjectPTE(CIP_ID,PTE_Rcvd)

        #Search through project list
        for project in project_list:

            #Compare CIP_IDs of list project and current layer project
            if current.compare(project):          
                #If a match is found, add PTE_Rcvd status to the project's
                # respective variable
                project.add(PTE_Rcvd)
                break           #Stop searching the project_list

        #If break was never reached, no matching CIP_ID was found.  Add current
        # project to project list and update that project's respective attribute
        else:
            project_list.append(current)


arcpy.AddMessage(str(pte_count) + " total PTEs counted for " + str(len(project_list)) + " projects")


del PTE_cursor


#Create a new table and populate it with the information collected in the
# project_list

stat_table = "PTE_Status_" + today.strftime("%Y%m%d")
arcpy.CreateTable_management(arcpy.env.workspace, stat_table)
fields = ["CIP_ID","Yes","No","Change_Owner","Other","Total"]
types = ["TEXT","SHORT","SHORT","SHORT","SHORT","SHORT"]
lengths = 12
for field_name,field_type in itertools.izip(fields,types):
    arcpy.AddMessage("Inserting " + field_name + " : " + field_type)
    arcpy.AddField_management(in_table=stat_table, field_name=field_name,
                              field_type=field_type, field_length=12)

#Create an insert cursor to populate the table
stat_cursor = arcpy.da.InsertCursor(stat_table, fields)


for project in project_list:
   total = project.Yes + project.No + project.OwnerChange + project.Other
    row = [
           project.CIP_ID,
           project.Yes,
           project.No,
           project.OwnerChange,
           project.Other,
           total
          ]
    stat_cursor.insertRow(row)


#Export Reports
export_string = "\\PTE_Status_" + today.strftime("%Y%m%d")
report_table = arcpy.mapping.TableView(stat_table)
#Insert the table into the current map
arcpy.mapping.AddTableView(df, report_table)
arcpy.RefreshTOC()


if report_table == arcpy.mapping.ListTableViews(mxd)[0]:
    arcpy.AddMessage("They are the same!")
else:
    arcpy.AddMessage("They are different!")

try:
    output_location = output_folder + export_string
    os.mkdir(output_location)
except WindowsError:
    pass
except:
    arcpy.AddWarning("Cannot create new folder. Generating files in "
                     "given folder instead.")
    output_location = output_folder

export_stub = output_location + export_string

if pdf:

    arcpy.AddMessage("Writing PDF from " + str(report_table) + " to " + export_stub + ".pdf")
    try:
        export_file = export_stub + ".pdf"
        arcpy.mapping.ExportReport(report_table, rlf, export_file)
    except:
        arcpy.AddWarning("No PDF Written")
        tb = sys.exc_info()[2]
        tbinfo = traceback.format_tb(tb)[0]
        pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + sys.exc_info()[1].args[0]
        msgs = "ArcPy ERRORS:\n" + arcpy.GetMessages(2) + "\n"
        arcpy.AddWarning(pymsg)
        arcpy.AddWarning(msgs)    

if excel:

    arcpy.AddMessage("Writing spreadsheet from " + report_table.name + " to " + export_stub + ".xls")
    try:
        export_file = export_stub + ".xls"
        arcpy.TableToExcel_conversion(report_table.name, export_file)
    except:
        arcpy.AddWarning("No Excel Spreadsheet written")
        tb = sys.exc_info()[2]
        tbinfo = traceback.format_tb(tb)[0]
        pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + sys.exc_info()[1].args[0]
        msgs = "ArcPy ERRORS:\n" + arcpy.GetMessages(2) + "\n"
        arcpy.AddWarning(pymsg)
        arcpy.AddWarning(msgs)

The error generated from Export Report is an IOError: Could not write output file from report. This tells me that the report is successfully run (the fields in my generated table match the fields in my rlf) but is unable to be written.

There is no error generated from TabletoExcel_conversion. The output, however, is a valid .xls with the correct table headers and no attribute values.

The table is successfully created and added to my map. I can then manually run the TabletoExcel geoprocessing tool AND export the report to PDF from within ArcMap with absolutely no issues. There is no reason these tools can't be run within a ArcTool script tool.

I have run every test I can think of. Some are still in my code (such as the check to see if my TableView object variable is the same as the TableView in my map). I see no reason why these tools aren't working correctly. However, I do believe the error being generated by ExportReport is directly related to the lack of data being written by TabletoExcel_conversion. I just don't know why both tools can see my table and the field names but can't read the attributes.

Interesting note: http://pro.arcgis.com/en/pro-app/tool-reference/conversion/table-to-excel.htm states that the tool accepts a TableView object. When I pass "report_table" as a TableView object, I get the following runtime error:

Traceback (most recent call last):
  File "c:\program files\arcgis\desktop10.3\ArcToolbox\Scripts\TableToExcel.py", line 222, in <module>
    arcpy.GetParameter(3))
  File "c:\program files\arcgis\desktop10.3\ArcToolbox\Scripts\TableToExcel.py", line 190, in table_to_excel
    with arcpy.da.SearchCursor(in_table, field_names) as cursor:
RuntimeError: cannot open 'GPT0'

When I run it as it is currently (TableView.name attribute), it creates the headers only .xls. I'm assuming the provided string name generates a search for the table using the workspace so successfully finding the table. However, the tool help specifically states that a TableView object should be passed, not a string name of the table.

Yes, the table exists, exists in my map, can be printed row by row to the message window, etc.

I have successfully run similar tools for other purposes, some much more complicated. This one has me stumped. I've included my entire code because at this point the problem could be anywhere. This should be the easy part.

1

Alright. I'll answer my own question.

Always remember to delete your cursors when you're done with them. I didn't delete my insert cursor, so every subsequent access to the table got hit with the lock that the insert cursor placed on it. That lock allowed new cursors to be created, but didn't allow access to the data.

Having added del stat_cursor to my code after the loop that uses the cursor, everything now works

headpalm

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.