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.
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)