I have a very simple python toolbox that takes in an excel sheet that contains lithology information and a kml/kmz file that contains borehole locations, processes them and appends them to a specified point and table featureclass. The tool works great in pro, with no issues. However, I published the tool out to my arcgis portal, published a point and table layer to go with it, wrapped them all up in a web app and it fails to add any information to the web layers. Does anyone here know what's going wrong?
import arcpy, os, openpyxl, shutil, time
from arcgis.gis import GIS
from arcgis.features import FeatureLayerCollection
class Toolbox(object😞
def __init__(self😞
"""Define the toolbox (the name of the toolbox is the name of the
.pyt file)."""
self.label = "Toolbox"
self.alias = "toolbox"
# List of tool classes associated with this toolbox
self.tools = [BoreholeArchive]
class BoreholeArchive(object😞
def __init__(self😞
"""Define the tool (tool name is the name of the class)."""
self.label = " Simple Borehole Archive Tool"
self.description = ""
self.canRunInBackground = False
def getParameterInfo(self😞
"""Define parameter definitions"""
param0 = arcpy.Parameter(
displayName="Input KML/KMZ File",
name="input_kml",
datatype="DEFile",
parameterType="Optional",
direction="Input")
param1 = arcpy.Parameter(
displayName="Input excel file",
name="input_excel",
datatype="GPType",
parameterType="Required",
direction="Input")
param2 = arcpy.Parameter(
displayName="Borehole point featuer layer to append",
name="output_borehole_point",
datatype="DEFeatureClass",
parameterType="Optional",
direction="Input")
param3 = arcpy.Parameter(
displayName="Table to append",
name="output_lithology_table",
datatype="DETable",
parameterType="Required",
direction="Input")
params = [param0, param1, param2, param3]
return params
def isLicensed(self😞
"""Set whether tool is licensed to execute."""
return True
def updateParameters(self, parameters😞
"""Modify the values and properties of parameters before internal
validation is performed. This method is called whenever a parameter
has been changed."""
return
def updateMessages(self, parameters😞
"""Modify the messages created by internal validation for each tool
parameter. This method is called after internal validation."""
return
def execute(self, parameters, messages😞
kml_kmz_filepath = parameters[0].valueAsText
excel_filepath = parameters[1].valueAsText
output_fc = parameters[2].valueAsText
output_table = parameters[3].valueAsText
# Get folder paths, do any work relating to temp files here
arcpy.AddMessage("Starting")
cwd = arcpy.env.scratchFolder
temp_excel_file_path = os.path.join(cwd, "temp.xlsx")
if os.path.exists(temp_excel_file_path😞
os.remove(temp_excel_file_path)
kml_folder = os.path.join(cwd, "kml_conversion")
if os.path.exists(kml_folder) == False:
os.mkdir(kml_folder)
kml_items = os.listdir(kml_folder)
for kml_item in kml_items:
if ".gdb" in kml_item:
arcpy.Delete_management(os.path.join(kml_folder, kml_item))
else:
os.remove(os.path.join(kml_folder, kml_item))
temp_gdb = os.path.join(cwd, "borehole_temp.gdb")
if arcpy.Exists(temp_gdb😞
arcpy.Delete_management(temp_gdb)
# clean row 1 of the excel file
interval_column_names = ["0_5",
"5_10",
"10_15",
"15_20",
"20_25",
"25_30",
"30_35",
"35_40",
"40_45",
"45_50",
"50_55",
"55_60"]
# Create a copy of the input excel sheet
shutil.copy(excel_filepath, temp_excel_file_path)
wb1 = openpyxl.load_workbook(filename=excel_filepath)
ws1 = wb1.worksheets[0]
wb2 = openpyxl.load_workbook(filename=temp_excel_file_path)
ws2 = wb2.create_sheet(ws1.title)
for row in ws1:
for cell in row:
ws2[cell.coordinate].value = cell.value
wb2.save(temp_excel_file_path)
wb = openpyxl.load_workbook(temp_excel_file_path)
sheet = wb.active
i = 1
bid_tracker_no_column = 1
id_column = 2
# Clean column names
current_interval = ""
while i < sheet.max_column +1:
cell = sheet.cell(1, i)
column_name = cell.value
column_name = column_name.strip()
column_name = column_name.replace("'", "")
column_name = column_name.replace(".", "")
column_name = column_name.replace("-", "_")
column_name = column_name.replace(" ", "_")
column_name = column_name.replace("/", "_")
if column_name == "Bid_Tracker_No":
bid_tracker_no_column = i
if column_name == "ID":
id_column == i
if column_name in interval_column_names:
current_interval = column_name
if current_interval == column_name:
cell.value = "Int_{}".format(column_name)
elif current_interval == "":
cell.value = column_name
else:
cell.value = "Int_{}_{}".format(current_interval, column_name)
i += 1
# Run through the spreadsheet and create the foriegn keys
master_id_column = sheet.max_column +1
cell = sheet.cell(1, master_id_column)
cell.value = "Bid_Tracker_No_and_ID"
i = 2
while i < sheet.max_row + 1:
id_cell = sheet.cell(i, id_column)
bid_tracker_cell = sheet.cell(i, bid_tracker_no_column)
master_id_cell = sheet.cell(i, master_id_column)
if bid_tracker_cell.value and id_cell.value:
master_id_cell.value = "{}-{}".format(bid_tracker_cell.value, id_cell.value)
else:
arcpy.AddWarning("Bad Bid_Tracker_No_and_ID in lithology layer, replace 99999 values with correct value")
master_id_cell.value = 99999
i+=1
wb.save(temp_excel_file_path)
# Create a temp gdb to hold temp data
arcpy.management.CreateFileGDB(cwd, "borehole_temp.gdb")
arcpy.env.workspace = temp_gdb
# Convert the excel sheet into a gdb table
lithology_table_path = os.path.join(temp_gdb, "Lithology")
arcpy.conversion.ExcelToTable(temp_excel_file_path, lithology_table_path)
# Add the tracking fields
arcpy.AddField_management(lithology_table_path, "created_user", "TEXT")
arcpy.AddField_management(lithology_table_path, "created_date", "DATE")
arcpy.AddField_management(lithology_table_path, "last_edited_user", "TEXT")
arcpy.AddField_management(lithology_table_path, "last_edited_date", "Date")
# Update the online table
arcpy.Append_management(lithology_table_path, output_table, "NO_TEST")
arcpy.AddMessage("Lithology records added")
# If a kml filpath is given then make the kml/kmz into a gdb and pull out the
# needed information and push it into the temp gdbt
if kml_kmz_filepath != None:
arcpy.conversion.KMLToLayer(kml_kmz_filepath, kml_folder)
arcpy.AddMessage("KML/KMZ converted")
temp_folder_contents = os.listdir(kml_folder)
kmz_gdb = ""
for item in temp_folder_contents:
if 'gdb' in item:
kmz_gdb = os.path.join(kml_folder, item)
kml_points_fc = os.path.join(kmz_gdb, "Points")
# If the kml has been processed extract out the relevant information into a new point feature class
# in the temp geodatabase
if arcpy.Exists(kml_points_fc😞
new_points_fc = os.path.join(temp_gdb, "Boreholes")
arcpy.CreateFeatureclass_management(temp_gdb, "Boreholes", "POINT")
arcpy.CopyFeatures_management(kml_points_fc, new_points_fc)
arcpy.AddField_management(new_points_fc, "Bid_Tracker_No_and_ID", "TEXT")
arcpy.AddField_management(new_points_fc, "Bid_Tracker_No", "TEXT")
arcpy.AddField_management(new_points_fc, "ID", "TEXT")
arcpy.AddField_management(new_points_fc, "created_user", "TEXT")
arcpy.AddField_management(new_points_fc, "created_date", "DATE")
arcpy.AddField_management(new_points_fc, "last_edited_user", "TEXT")
arcpy.AddField_management(new_points_fc, "last_edited_date", "Date")
# Pull the information from Name and put it in Bid_Tracker_No_and_ID
with arcpy.da.UpdateCursor(new_points_fc, ["Name", "Bid_Tracker_No_and_ID"]) as cursor:
for row in cursor:
if row[0] != None:
row[1] = row[0]
else:
arcpy.AddWarning("Bad Bid_Tracker_No_and_ID in boreholes layer, replace 99999 values with correct value")
row[1] = 99999
cursor.updateRow(row)
del cursor
arcpy.AddMessage("Primary Keys Created")
# Delete unneeded fields
# new_points_fc_fields = arcpy.ListFields(new_points_fc)
# for field in new_points_fc_fields:
# if field.name not in ["OBJECTID", "Shape", "Bid_Tracker_No", "ID", "Bid_Tracker_No_and_ID", "created_user", "created_date", "last_edited_user", "last_edited_date"]:
# arcpy.DeleteField_management(new_points_fc, field.name)
# arcpy.AddMessage("Extra KML/KMZ fields removed")
# Since there was a kml update the web point layer
arcpy.Append_management(new_points_fc, output_fc, "NO_TEST")
arcpy.AddMessage("Borehole locations added")
else:
arcpy.AddWarning("No points found in kml/kmz, kml/kmz not processed")
arcpy.AddMessage("Complete")
time.sleep(10)
return
What it looks like in WAB pre run:
After running: