Using ArcPy.TableToExcel_conversion in Script Tool removes automatically added data
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 Moduleto 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_conversiontool 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_conversiontool 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.
In my continued efforts to troubleshoot this occurrence I have learned something even more baffling. While in the Script Tool GUI, if I purposely choose to leave the optional excel inputs blank, therefore making it to where the
TableToExceltool will not run, the feature class data is still removed from the TOC. Then again, to reconfirm what I already knew, I coded out (#) the excel code, and lo and behold, it worked perfectly fine and the feature class was added to the TOC (and stayed). I have no idea why/how this could be happening?
EDIT (6 JULY 2015): When trying to run the
TableToExcel_conversiontool prior to the ArcPy Mapping code, I receive this error:
Traceback (most recent call last): File "C:UserslwilsonDesktop est.py", line 156, in addLayer = arcpy.mapping.Layer(queryLayer) # Set variable for queryLayer File "c:program files (x86)arcgisdesktop10.3arcpyarcpyarcobjectsmixins.py", line 389, in init super(LayerMixin, self).init(lyrfile) File "c:program files (x86)arcgisdesktop10.3arcpyarcpyarcobjects_base.py", line 47, in init for arg in args)) ValueError: Object: CreateObject Layer invalid data source
I am not quite sure how the Layer is all of a sudden an invalid data source due to changing the order in which the code runs?
# 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 SystemsLRS 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 = 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 for row in arcpy.da.SearchCursor(queryLayer, "LOGDT")] except: # exception error if the user's SQL query produces no return err = "No Results Found Via SQL Query SCRIPT 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 = firstLogdt cursor.updateRow(row) # SearchCursor that will read the values of LOGDT and return the last value (latest) lastLogdt = [row 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 = 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 = 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 = 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) # 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
I had the same issue. I simply reversed the order of operations by export to excel first, then adding the layer. Or if that doesn't work you could just re-add the layer, no?