1

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.

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 TableToExcel tool 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_conversion tool prior to the ArcPy Mapping code, I receive this error:

Traceback (most recent call last): File "C:\Users\lwilson\Desktop\test.py", line 156, in addLayer = arcpy.mapping.Layer(queryLayer) # Set variable for queryLayer File "c:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\arcobjects\mixins.py", line 389, in init super(LayerMixin, self).init(lyrfile) File "c:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\arcobjects_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 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
  • 1
    Can you just convert the data using Python's builtin tools into a csv file that you can rename as a xlsx? – dassouki Jul 2 '15 at 15:02
  • @Dassouki - Thank you for the tip. I will try to do this, but have essentially no experience (yet) with Python's Xlsx Writer. Either way, it would be nice to figure out what is causing this strange occurrence :) – JumpInTheFire Jul 2 '15 at 15:14
  • 2
    To try and debug this I recommend that you put your full script aside for now and work on creating some test scripts that make sure each of its parts work as you expect them to. In addition I would remove your try/except statements so that all errors come through in their raw form because they are likely to be helpful. – PolyGeo Jul 4 '15 at 0:45
  • @PolyGeo - Thank you for the constructive comments. Unfortunately, I have done the aforementioned suggestions with no such luck. Fortunately, I have found someone who might know the answer to my issue. Either way, I'll be sure to keep this thread updated as needed. – JumpInTheFire Jul 6 '15 at 14:43
1

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?

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.