AnsweredAssumed Answered

Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

Question asked by lancemwilson on Jul 1, 2015
Latest reply on Jul 2, 2015 by Dan_Patterson

I'm using `ArcGIS for Desktop 10.3.0.4322`.

 

 

I have a peculiar occurrence that I don't quite fully understand. I have a Python Script Tool that does a number of different things, to include using the `ArcPy Mapping Module` to add newly acquired data to the `CURRENT` .mxd for viewing. This works perfectly fine, except when running a bit of additional code (bottom of post), which optionally uses (based on user input) the `arcpy.TableToExcel_conversion` tool in order to provide the user with an Excel spreadsheet version of the feature class data they queried. When watching the script run, the new feature class, `queryLayer`, shows up in the .mxd's Table of Contents, only to immediately disappear thereafter. While investigating, the feature class still exists and is fine (as would be expected), as the `arcpy.TableToExcel_conversion` tool does not delete the source file, it merely creates a newly converted one. So it is obvious that something is happening while/after the conversion is being made (as without the conversion it works fine), but I don't understand why it would be removing the newly added feature class from the .mxd's data frame/TOC. Any help or thoughts are greatly appreciated, thank you!

 

 

    # import arcpy module
    import arcpy
    
    # Ask user to select the Geodatabase workspace to use for data output
    userWorkspace = arcpy.GetParameterAsText(0)
    
    # Set workspace environment based upon user's choice
    arcpy.env.workspace = userWorkspace
    
    # Ask user to select an Oracle dB Connection
    oracleDB = arcpy.GetParameterAsText(1)
    
    # Ask user to name the Query Layer
    queryLayer = arcpy.GetParameterAsText(2)
    
    # Ask user if they want to overwrite previously named Query Layer
    overwriteQ = arcpy.GetParameterAsText(3)
    
    # Ask user for an SQL Query Expression to be run against the selected Oracle dB
    sqlQuery = arcpy.GetParameterAsText(4)
    
    # Ask user to name the output of excel file (this is optional, for those who want to do excel analysis)
    excelName = arcpy.GetParameterAsText(5)
    
    # Ask user if they want to overwrite previously named Excel File
    overwriteE = arcpy.GetParameterAsText(6)
    
    # Ask user to select the folder output of the excel file (optional as well, depending on if user wants an excel file)
    excelFolder = arcpy.GetParameterAsText(7)
    
    # Create spatial reference variable to assign to queryLayer
    spatialRef = arcpy.SpatialReference("W:\Coordinate Systems\LRS Lambert.prj")
    
    # Process: 'Make Query Layer' - Creates a Query Layer using the user's Oracle dB and SQL query expression
    arcpy.MakeQueryLayer_management(oracleDB, "Temp_Layer", sqlQuery, "UNIQUE_ID", "POINT", "1050010", spatialRef)
    
    # Set overwrite output for Query Layer to user's choice
    arcpy.env.overwriteOutput = overwriteQ
    
    # Process: 'Copy Features' - Copies the temporary Query Layer and stores it as a permanent feature class
    arcpy.CopyFeatures_management("Temp_Layer", queryLayer)
    
    # Process: 'Delete Features' - Deletes the temporary file Temp_Layer
    # This allows for multiple executions of this tool within the same ArcMap session without error
    arcpy.Delete_management("Temp_Layer")
    
    # Process: 'Define Projection' - Defines the projection of queryLayer feature class output
    arcpy.DefineProjection_management(queryLayer, spatialRef)
    
    # Process: 'Add Field' - Adds new column fields to queryLayer
    arcpy.AddField_management(queryLayer, "First_Time", "DATE") # The first LOGDT ping
    arcpy.AddField_management(queryLayer, "Last_Time", "DATE") # The last LOGDT ping
    arcpy.AddField_management(queryLayer, "Total_Time", "STRING") # Summation of the first to last ping in time
    arcpy.AddField_management(queryLayer, "Total_Pings", "INTEGER") # Total number of pings (rows)
    arcpy.AddField_management(queryLayer, "Possible_Pings", "INTEGER") # Total number of pings possible in given timeframe
    arcpy.AddField_management(queryLayer, "Time_to_Process", "DATE") # How long it took for each ping to process
    
    # Calculates the total number of rows (pings) for the Total_Pings field
    numRows = int(arcpy.GetCount_management(queryLayer).getOutput(0))
    
    # UpdateCursor that will write the value of numRows to the Total_Pings field
    cursor = arcpy.da.UpdateCursor(queryLayer, "Total_Pings")
    for row in cursor:
        row[0] = numRows
        cursor.updateRow(row)
    
    # SearchCursor that will read the values of LOGDT and return the first value (earliest)
    try: # try to read in values based on the user's SQL query
        firstLogdt = [row[0] for row in arcpy.da.SearchCursor(queryLayer, "LOGDT")][0]
    except: # exception error if the user's SQL query produces no return
        err = "No Results Found Via SQL Query\nSCRIPT EXITED" # Error message variable
        arcpy.AddError(err) # Print error message
        sys.exit() # Exits the script, does not attempt to run any further
    
    # UpdateCursor that will write the first (earliest) LOGDT value to the First_Time field
    cursor = arcpy.da.UpdateCursor(queryLayer, "First_Time")
    for row in cursor:
        row[0] = firstLogdt
        cursor.updateRow(row)
    
    # SearchCursor that will read the values of LOGDT and return the last value (latest)
    lastLogdt = [row[0] for row in arcpy.da.SearchCursor(queryLayer, "LOGDT")][-1]
    
    # UpdateCursor that will write the last (latest) LOGDT value to the Last_Time field
    cursor = arcpy.da.UpdateCursor(queryLayer, "Last_Time")
    for row in cursor:
        row[0] = lastLogdt
        cursor.updateRow(row)
    
    # Calculates the difference between firstLogdt and lastLogdt
    timeDiff = lastLogdt - firstLogdt # Produces a timedelta object, not datetime
    
    # Calculates the total number of seconds from timeDiff
    timeSecs = timeDiff.total_seconds()
    
    # Creates a function that will convert timeSecs to a readable format (yy:dd:hh:mm:ss)
    def readTime(seconds):
        minutes, seconds = divmod(seconds, 60)
        hours, minutes = divmod(minutes, 60)
        days, hours = divmod(hours, 24)
        years, days = divmod(days, 365)
        return '%02d:%02d:%02d:%02d:%02d' % (years, days, hours, minutes, seconds)
    
    # UpdateCursor that will write the time difference calculation to the Total_Time field
    cursor = arcpy.da.UpdateCursor(queryLayer, "Total_Time")
    for row in cursor:
        row[0] = readTime(timeSecs)
        cursor.updateRow(row)
    
    # Calculates the total number of pings that would occur with optimal reception
    possiblePings = timeSecs / 5 # 1 ping every 5 seconds
    
    # UpdateCursor that will write the total number of pings possible to the Possible_Pings field
    cursor = arcpy.da.UpdateCursor(queryLayer, "Possible_Pings")
    for row in cursor:
        row[0] = possiblePings
        cursor.updateRow(row)
    
    # Using ArcPy Mapping Module to add queryLayer to current .mxd map display and zoom to it
    mxd = arcpy.mapping.MapDocument("CURRENT") # Set variable to currently active .mxd
    dataFrame = arcpy.mapping.ListDataFrames(mxd)[0] # Set variable equal to the first data frame within mxd
    addLayer = arcpy.mapping.Layer(queryLayer) # Set variable for queryLayer
    arcpy.mapping.AddLayer(dataFrame, addLayer) # Adds queryLayer to the map
    dataFrame.zoomToSelectedFeatures() # Zooms to queryLayer
    arcpy.RefreshActiveView() # Refreshes the active data frame's view
    
    # Set overwrite output for Excel File to user's choice
    arcpy.env.overwriteOutput = overwriteE
    
    # Change workspace environment to where the user wants the Excel file to be saved
    try: # Tries to change the environment workspace to the user's optional excel output folder
        arcpy.env.workspace = excelFolder
    except:
        pass # If user did not specify excel folder input, then let pass and continue on
    
    # Process: 'Table To Excel' - Converts the final queried data to an excel spreadsheet file (optional)
    try: # Tries to run the tool (will only work if user specified optional excel inputs)
        arcpy.TableToExcel_conversion(queryLayer, excelName+'.xls')
    except:
        pass # If user did not specify optional excel inputs, then let pass and continue on
    
    # delete cursor, row variables
    del cursor, row

Outcomes