2

I am seeking some help with fixing my Python script to allow it to run as an ArcGIS Script Tool. Let me first say that the script runs without issue outside of ArcGIS.

The section giving me grief inside the Script Tool is simply joining a few tables to make one long table, which I will later query and export to an Excel template (the end goal).

I basically feed in two tables, join them using acrpy.MakeQueryTable_management, and then save the output as a new table with arcpy.CopyRows_management (either a temporary table to save to a GDB)

The script does this three times in three similarly structured blocks of code, using the output of the previous arcpy.CopyRows_management as an input in the next arcpy.MakeQueryTable, in order to join more fields. The script is failing when running arcpy.MakeQueryTable_management the second time, giving the error message:

ExecuteError: Failed to execute. Parameters are not valid. ERROR 000152: Invalid input data Failed to execute (MakeQueryTable)

Again, the first pass works fine, but it fails on the second. As best I can tell, the script fails because it does not read/recognize the table from the first arcpy.MakeQueryTable_management /arcpy.CopyRows_management, therefore the input data is seen as not valid

If I run each block one at a time, it works fine.

How do I pass the table along so it runs all at once?

I think it is as simple as that, but I am at a loss.

I’ve looked at these discussions for some help, and it’s been useful, but hasn’t helped me sort out my problem.

https://community.esri.com/t5/python-questions/make-query-table-works-in-python-window-but-not-as-tool/m-p/365787

https://community.esri.com/t5/geoprocessing-questions/arcpy-scripting-works-in-python-window-but-not-in-script/td-p/694518

import arcpy, csv
import os
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

startTime = datetime.now()

arcpy.env.workspace = arcpy.GetParameterAsText(0)
arcpy.env.overwriteOutput = True

#Tables to join. These tables will be joing to make a new Query Table. 
tableList = ["Gt_Locations", "Gt_Samples"]

##Fields to include in Query table
fieldList = [["Gt_Locations.Location_ID",],
            ["Gt_Locations.Location_Name",'Location_Name'],
            ["Gt_Locations.Type_Code"],
            ["Gt_Locations.X_2", 'X'],
            ["Gt_Locations.Y_2", 'Y'],
            ["Gt_Samples.Location_ID", 'Location_ID_'],
            ["Gt_Samples.Sample_date", 'Sample_date']]

##Define matching fields to use in join and use keyField as unique ID
whereClause = "Gt_Locations.Location_ID = Gt_Samples.Location_ID"
keyField = "Gt_Samples.Sample_ID"
lyrName = arcpy.GetParameterAsText(1)    ##Default to SampleID_Key_CopyRows

##Execute the join with MakeQueryTable and define new table name with lyrName variable
arcpy.MakeQueryTable_management(tableList, lyrName, "USE_KEY_FIELDS", keyField, fieldList, whereClause)
arcpy.CopyRows_management(lyrName, lyrName)
    
##This block joines the Results table table with QueryTable from above. 
tableList2 = ["SampleID_Key_CopyRows", "Gt_Results"]

fieldList2 =  [["SampleID_Key_CopyRows.Location_Name", 'Location_Name'],
               ["SampleID_Key_CopyRows.Gt_Locations_Type_Code", 'Type_Code'],
               ["SampleID_Key_CopyRows.Gt_Samples_Sample_ID", 'Sample_ID'],
               ["SampleID_Key_CopyRows.Sample_date", 'Sample_date'],
               ["SampleID_Key_CopyRows.X", 'X'],
               ["SampleID_Key_CopyRows.Y", 'Y'],
               ["Gt_Results.Value_txt", 'Value_txt'],
               ["Gt_Results.Unit_ID", 'Unit_ID'],
               ["Gt_Results.Analyte_ID", 'Analyte_ID'],
               ["Gt_Results.Sample_ID_D", 'Sample_ID_D'],
               ["Gt_Results.Result_ID", 'Result_ID']]

whereClause2 = "SampleID_Key_CopyRows.Gt_Samples_Sample_ID = Gt_Results.Sample_ID_D"
keyField2 = "Gt_Results.Result_ID"
lyrName2 = arcpy.GetParameterAsText(2)   ## Default to Results_Key_CopyRows

arcpy.MakeQueryTable_management(tableList2, lyrName2, "USE_KEY_FIELDS", keyField2, fieldList2, whereClause2)    ##THIS IS WHERE THE ERROR IS REPORTED
arcpy.CopyRows_management(lyrName2, lyrName2)
2
  • I'm going to have a guess... your first query table SampleID_Key_CopyRows you have a field called Location_ID but you seem to be calling it Sample_ID in fieldList2?
    – Hornbydd
    Jun 1 '21 at 13:22
  • I believe those are two different fields. Location_ID is not part of the fieldList2 because I no longer need it to be included in the next output. It was used in the first fieldList as a join field (not sure it needs to be included, but I wanted it there to verify it joined as intended) . I'll look closer at the field names as you suggested, as it is sort of messy. Jun 1 '21 at 13:36
0

Solved my issue. I just had to reference the actual location of the first input, not just the output name. I thought I could simply reference the output name because it was saved to my defined workspace.

Here the change:

lyrName = arcpy.GetParameterAsText(0) ##Default to SampleID_Key_CopyRows

##Execute the join with MakeQueryTable and define new table name with lyrName variable
arcpy.MakeQueryTable_management(tableList, lyrName, "USE_KEY_FIELDS", keyField, fieldList, whereClause)
arcpy.CopyRows_management(lyrName, "SampleID_Key_CopyRows")

SampleID_Location = dir + '\\' + str(lyrName)

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.