topic Re: Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data in Python Questions

Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

LanceWilson — Sat, 11 Dec 2021 06:41:46 GMT

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

Re: Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

DanPatterson_Retired — Wed, 01 Jul 2015 22:36:18 GMT

arcpy.env.overwriteOutput = overwriteQ

wont work selectively, it is True or False and applies to everything. The potential solution is to use Delete_Management in its place.

Re: Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

LanceWilson — Thu, 02 Jul 2015 14:33:29 GMT

@Dan Patterson - Thank you very much for taking a look at my code and trying to help me discern the problem.

Unfortunately, that is not the issue and did not solve my strange occurrence. I removed both 'arcpy.env.overwriteOutput' lines and then ran the script, it still had the same issue. So then I went a step further and in the Script Tool GUI I didn't put in any of the optional excel inputs, ran the script, and still the same issue occurred, regardless of the fact that arcpy.TableToExcel_conversion didn't even run, but the code was still there.

Lastly, to reconfirm what I already knew, I coded (#) out the excel parts, and lo and behold, the feature class was added to the TOC and worked just fine.

Besides there being some very strange bug or interaction happening with the TableToExcel tool, I have no idea what could be making this happen.

Re: Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

DanPatterson_Retired — Thu, 02 Jul 2015 16:42:13 GMT

I am not sure...any problems with the try-except blocks are simply ignored by the use of the pass statement. I didn't know whether you had modified your original code to reflect your current standing in it, so I can't really comment further.

Re: Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

LanceWilson — Thu, 02 Jul 2015 17:02:12 GMT

Thanks again for your help Dan. I haven't edited/updated my code that is listed here, as none of it seems to be the issue/remedy (except obviously for the TableToExcel tool). I took out the try/except statements and just let the excel inputs run (after putting them in on the GUI) and the same situation happens. Very odd!

Re: Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

DanPatterson_Retired — Thu, 02 Jul 2015 18:20:05 GMT

Ok...what I am saying that with a try except block there is this

try:

try to see if this works

except:

pass

When you put pass NO error message will show up at all unless it triggers some system/python/etc error

Why not change it to

try:

whatever

except:

print("oops")

as a minimum, you can actually trap error messages should the try part fail, check out arcpy.AddMessage and the other options in the message section it is worth a shot .... if all goes well, then you do have a mysterious error and need to pass it on to tech support...especially since no one else is chiming in. Good luck

Re: Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

LanceWilson — Thu, 02 Jul 2015 18:31:57 GMT

Thanks, Dan.

My apologies for not being clearer, I did/do understand what you were trying to say about the try/except and that by using "pass", I am simply letting it go without any helpful error messages. The main purpose for this (at the moment) is that I'm still writing the code (there is still more functionality that I want to accomplish after I figure out this mysterious occurrence). I wouldn't have even used the try/except code, because obviously TableToExcel would give its own errors if something went wrong. I merely am using the try/except/pass because of the fact that the excel inputs are optional for the user via the GUI (because the main purpose is to output a feature class, but some do/may want an excel file). In the case that someone doesn't want an excel file, I didn't want the script to freak out because there was no input for it and I don't need any error message to show, it just needs to simply carry along with the rest of the process (pass).

Thanks again, and by the way, I've never sent any issues to tech support. Is there a specific way I should go about doing this?

Re: Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

DanPatterson_Retired — Thu, 02 Jul 2015 18:41:19 GMT

Ok...gotcha

As for reporting a problem...I must admit, I do kindof circumvent the system...however apparently

Esri Support is the link. If you have your own license then away you go...if someone else is responsible, then they have to do it.

Re: Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data

Dwight — Tue, 05 Jul 2022 18:53:10 GMT

I encountered this issue today, still no resolution I can find on any of the message boards 7 years later...