Error with XY Table to Point in Python Script

[KieranClark]

I am receiving the following error when trying to use XY Table to Point in a Python Script:

Traceback (most recent call last):
  File "C:\Users\kierc\projects\SubTelCableMap\01 Update Current Position.py", line 22, in <module>
    arcpy.XYTableToPoint_management(in_table=Ship_Positions_csv, out_feature_class=ShipLatestPosition, x_field="AIS.LONGITUDE", y_field="AIS.LATITUDE", z_field="", coordinate_system="GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119521E-09;0.001;0.001;IsHighPrecision")
  File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 3992, in XYTableToPoint
    raise e
  File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 3989, in XYTableToPoint
    retval = convertArcObjectToPythonObject(gp.XYTableToPoint_management(*gp_fixargs((in_table, out_feature_class, x_field, y_field, z_field, coordinate_system), True)))
  File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 511, in <lambda>
    return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: Traceback (most recent call last):
  File "c:\program files\arcgis\pro\Resources\ArcToolbox\Scripts\XYTableToPoint.py", line 183, in <module>
    xy_table_to_points(in_table, output_fc, x_field, y_field, z_field, coordinate_system)
  File "c:\program files\arcgis\pro\Resources\ArcToolbox\Scripts\XYTableToPoint.py", line 158, in xy_table_to_points
    coord_values.append(row[scur.fields.index(val)])
ValueError: tuple.index(x): x not in tuple

Failed to execute (XYTableToPoint).‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Unfortunately, I'm having trouble understanding just what this error means. This script previously ran without trouble on a previous computer. I have kept all filenames and file paths the same. Even running a normal XY Table to Point tool within ArcGIS Pro fails to complete with the following error:

 Traceback (most recent call last):
  File "c:\program files\arcgis\pro\Resources\ArcToolbox\Scripts\XYTableToPoint.py", line 183, in <module>
    xy_table_to_points(in_table, output_fc, x_field, y_field, z_field, coordinate_system)
  File "c:\program files\arcgis\pro\Resources\ArcToolbox\Scripts\XYTableToPoint.py", line 158, in xy_table_to_points
    coord_values.append(row[scur.fields.index(val)])
ValueError: tuple.index(x): x not in tuple
 Failed to execute (XYTableToPoint).‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Looking for any guidance here. The code for the Python script is as follows:

# -*- coding: utf-8 -*-
"""Generated by ArcGIS ModelBuilder on: 2019-12-14 00:22:34
All ModelBuilder functionality may not be exported. Edits may be required for equivalency with the original model.
"""

import arcpy

# To allow overwriting the outputs change the overwrite option to true.
arcpy.env.overwriteOutput = True

# Local variables:
Ship_Positions_csv = r"C:\Users\kierc\projects\api\Ship_Positions.csv"
ShipLatestPosition = r"C:\Users\kierc\Documents\ArcGIS\Projects\SubTelCableMap\SubTelCableMap.gdb\ShipLatestPosition"
CurrentCableShipPositionTmp = r"C:\Users\kierc\Documents\ArcGIS\Projects\SubTelCableMap\SubTelCableMap.gdb\CurrentCableShipPositionTmp"
Updated_Target_Dataset = CurrentCableShipPositionTmp
CurrentCableShipPosition = r"C:\Users\kierc\Documents\ArcGIS\Projects\SubTelCableMap\SubTelCableMap.gdb\CurrentCableShipPosition"
Updated_Input_With_Rows_Removed = CurrentCableShipPosition

# Process: XY Table To Point
tempEnvironment0 = arcpy.env.outputCoordinateSystem
tempEnvironment0 = arcpy.env.outputCoordinateSystem
arcpy.XYTableToPoint_management(in_table=Ship_Positions_csv, out_feature_class=ShipLatestPosition, x_field="AIS.LONGITUDE", y_field="AIS.LATITUDE", z_field="", coordinate_system="GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119521E-09;0.001;0.001;IsHighPrecision")
arcpy.env.outputCoordinateSystem = tempEnvironment0

# Process: Copy Features
arcpy.CopyFeatures_management(in_features=ShipLatestPosition, out_feature_class=CurrentCableShipPositionTmp, config_keyword="", spatial_grid_1="", spatial_grid_2="", spatial_grid_3="")

# Process: Delete Rows
arcpy.DeleteRows_management(in_rows=CurrentCableShipPosition)

# Process: Append
arcpy.Append_management(inputs=r"C:\Users\kierc\Documents\ArcGIS\Projects\SubTelCableMap\SubTelCableMap.gdb\CurrentCableShipPositionTmp", target=Updated_Input_With_Rows_Removed, schema_type="TEST", field_mapping="", subtype="", expression="")



‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This was originally made with Model Builder in Pro 2.4 and cleaned up/tweaked slightly by me after exporting to a editable Python file.

Edit:

For some reason, manually renaming the AIS.LATITUDE/AIS.LONGITUDE fields in my CSV file to simply LATITUDE/LONGITUDE allows the XY Table to Point tool to run successfully in ArcGIS Pro. I'm not sure why this would suddenly not work and I'd rather not having to add an additional step of renaming data fields.

[DanPatterson_Retired]

"AIS.LONGITUDE

You shouldn't use punctuation in field names, only letters, numbers and the _ and don't begin with a number.

That is why changing the csv field name worked.

from an error message

010085: is an invalid field name.—Help | Documentation 

Field names should only contain characters a–z, 0–9, and underscores.

Do not use spaces, hyphens, parentheses, brackets, and other special characters, such as $, %, and #.

Plus a whole load more recommendations.

[KieranClark]

That is unfortunately what I suspected. However, I am not exactly an expert in Python yet and have had to rely on code examples to cobble together what's necessary to get my tools to work. The following code takes the JSON output from our AIS service and converts it into a CSV file:

import json
from pandas.io.json import json_normalize

def loading_file():
	#file path
	file_path = r'C:\Users\kierc\projects\api\latestshiplocation.json'

	#Loading json file
	json_data = open(file_path)
	data = json.load(json_data)
	return data

#Storing Available Keys
def data_keys(data):
	keys = {}
	for i in data:
		for k in i_keys():
			keys[k] = 1
	
	keys = keys.keys()

#Excluding nested arrays from keys - hard coded -> IMPROVE
	new_keys = [x for x in keys if
	x != 'attributes' and
	x != 'change']

	return new_keys

#Excluding nested arrays from json dictionary
def new_data(data, keys):
	new_data = []
	for i in range(0, len(data)):
		x = {k:v for (k,v) in data[i].items() if k in keys }
		new_data.append(x)
	return new_data

def csv_out(data):
	data.to_csv(r'C:\Users\kierc\projects\api\Ship_Positions.csv', encoding='utf-8')

def main():
	data_file = loading_file()
	keys = data_keys(data_file)
	table = new_data(data_file, keys)
	csv_out(json_normalize(table))

main()
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

It converts this to a CSV file very nicely, except for the column headers. I'm not sure where I need to change the code so that it uses an underscore instead of a period punctuation.

Again, I'm a little unsure as to why these field names aren't working any longer. They were on the previous computer using ArcGIS Pro 2.4 - both through these python scripts and the tools within ArcGIS Pro.

[DanPatterson_Retired]

You might have to throw in some print statements to find out where the field names are coming from, but just guessing...

keys = ['A.Long', 'A.Lat', 'A.SomethingElse']

keys = [k.replace(".", "_") for k in keys]

keys
['A_Long', 'A_Lat', 'A_SomethingElse']‍‍‍‍‍‍

[KieranClark]

Alright...clearly I need some further assistance. Where would I even start looking (documentation/examples) to try to resolve this issue? I understand the 

keys = [k.replace(".", "_") for k in keys]

line is intended to replace the period in the keys (column headers) after they've been defined or stored but how do I determine where in the process to do that step?

[DanPatterson_Retired]

start by printing the keys line

line 42.5 print(keys)

Short of guessing where they might be, I don't know where the header line is stored.

If it is actually stored as the first line in "table", then that would be an issue.

Go with the guess first

[KieranClark]

Dan, thank you for your help. You've given me a lot to work with and some avenues to explore to keep sharpening my skills.

For now, as a quick and dirty workaround, I'm writing the initial JSON to CSV conversion to a temporary file then taking that temporary file and renaming the column headings. This allows the feature updates to happen and gets our project back in working order. 

[VincentAbere]

I am also getting the same error and just working with the tool XY Table to Point. Am trying to import data on a csv file but i keep getting the same error. Am currently using Pro Version 2.5.0

Traceback (most recent call last):
File "c:\users\bushmaps\appdata\local\programs\arcgis\pro\Resources\ArcToolbox\Scripts\XYTableToPoint.py", line 183, in <module>
xy_table_to_points(in_table, output_fc, x_field, y_field, z_field, coordinate_system)
File "c:\users\bushmaps\appdata\local\programs\arcgis\pro\Resources\ArcToolbox\Scripts\XYTableToPoint.py", line 158, in xy_table_to_points
coord_values.append(row[scur.fields.index(val)])
ValueError: tuple.index(x): x not in tuple
Failed to execute (XYTableToPoint).

[DanPatterson_Retired]

one bug... maybe related?

BUG-000129225: In ArcGIS Pro 2.5, the XYTableToPoint tool fails to .. 

[zhenghan]

Not sure if this applies to the case above, I noticed that if X or Y field names include string "shape_" (or maybe just "shape") in my input csv/text table, the same error will rise. When I changed the X,Y field names to exclude that particular string without changing anything else, the tool can run without problem.

I am using ArcGIS Pro 2.9.3

Attached is a input table where I ran into the issue