topic Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column in Python Questions

JSONToFeatures_conversion: Cannot create a table with a duplicate column

RyanDavis1 — Wed, 16 Aug 2017 20:26:25 GMT

Hi,

I'm working on a Python script that bulk downloads layers from feature servers. I download each layer as JSON and then use JSONToFeatures_conversion to convert it to a feature class. The script works okay for the most part, but for some reason about a quarter of the layers fail to convert from JSON to Feature Class.

I consistently receive an Error 206: Cannot create FeatureClass, and it further elaborates that I "cannot create a table with a duplicate column." While that makes sense, this seems odd to me because I'm basically copying valid layers, not adding any columns, and there shouldn't be any duplicate columns. I've also manually converted individual layers using the same workflow just to see if it was a data issue, and I'm able to create feature classes with no problem.

Could someone give me a hint as to what I may be doing wrong?

Here's how I'm writing my JSON if it helps:

def write_json(url_response):
"""Writes JSON to a file."""
with open('downloadFile.json', 'wb') as chunkTemp:
for chunk in url_response.iter_content(chunk_size=1024):
if chunk:
try:
chunkTemp.write(chunk)
except Exception as e:
print 'Yuck. ' + str(e)
break
chunkTemp.seek(0) #Go back to the top of the file.
return chunkTemp

If it's not obvious, I'm a relative novice with Python and a complete noob when it comes to working with JSON.

Thanks.

Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column

DanPatterson_Retired — Wed, 16 Aug 2017 21:24:15 GMT

What are the field names?

Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column

MicahBabinski — Wed, 16 Aug 2017 23:09:42 GMT

Hi Ryan,

Can you post the .json and include the link to the service which you exported it from? That would help troubleshoot.

Micah

Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column

RyanDavis1 — Thu, 17 Aug 2017 13:05:23 GMT

Thanks for the reply.

Here's an example of one of the layers that fails.

Service: https://services2.arcgis.com/1cdV1mIckpAyI7Wo/ArcGIS/rest/services/All_Places_of_Worship/FeatureServer

JSON: https://services2.arcgis.com/1cdV1mIckpAyI7Wo/ArcGIS/rest/services/All_Places_of_Worship/FeatureServer/0/query?where=OBJ…

This is just one example of maybe 50 or so layers that fail with this error.

Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column

RyanDavis1 — Thu, 17 Aug 2017 13:08:16 GMT

Well, the different layers have different field names, but here's an example of one of the downloads:

https://services2.arcgis.com/1cdV1mIckpAyI7Wo/ArcGIS/rest/services/All_Places_of_Worship/FeatureServer/0/query?where=OBJ…

I don't see any duplicates, but maybe I'm missing something obvious.

Thanks.

Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column

RyanDavis1 — Thu, 17 Aug 2017 13:15:28 GMT

I've also manually converted individual layers using the same workflow just to see if it was a data issue, and I'm able to create feature classes with no problem.

I think I was mistaken when I wrote this. Now when I try to manually convert an individual layer through ArcToolbox, I'm getting an error claiming "The workspace is not connected."

Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column

JoshuaBixby — Fri, 10 Dec 2021 23:04:01 GMT

I took the worship layer above, downloaded the JSON output, and ran the following code just fine:

>>> json = # path to JSON file
>>> arcpy.JSONToFeatures_conversion(json,"in_memory/worship")
<Result 'in_memory\\worship'>
>>> ‍‍‍‍

Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column

RandyBurton — Fri, 10 Dec 2021 23:04:04 GMT

The JSONToFeatures Tool does not like the OBJECTID definition in the JSON file.

The JSON contains the following in the "fields" section:

  "fields" : [
    {
      "name" : "OBJECTID", 
      "type" : "esriFieldTypeInteger", 
      "alias" : "OBJECTID", 
      "sqlType" : "sqlTypeOther", 
      "domain" : null, 
      "defaultValue" : null
    }, 
    {‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Note the setting for "type" which is "esriFieldTypeInteger". When testing it with ArcMap 10.5 using a file geodatabase, the tool wanted to duplicate this field as it wants the type of the OBJECTID to be "esriFieldTypeOID".

There are a couple of things you can do. The easiest is to edit the JSON by changing the type on line 12 as indicated below. Your script that processes the JSON could include some code to make the edit for you.

{
  "objectIdFieldName" : "OBJECTID", 
  "globalIdFieldName" : "", 
  "geometryType" : "esriGeometryPoint", 
  "spatialReference" : {
    "wkid" : 102100, 
    "latestWkid" : 3857
  }, 
  "fields" : [
    {
      "name" : "OBJECTID", 
      "type" : "esriFieldTypeOID", 
      "alias" : "OBJECTID", 
      "sqlType" : "sqlTypeOther", 
      "domain" : null, 
      "defaultValue" : null
    }, 
    {
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

A second option would be to do a global replace of "OBJECTID" with something like "OBJ_ID" in the JSON file. You would also need to set line 2 to an empty string ( "objectIdFieldName" : "" ). Then the tool would create a new OBJECTID. As this option is a bit more complicated, I would use the first suggestion - just change the type to "esriFieldTypeOID".

Hope this helps.

Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column

RyanDavis1 — Fri, 18 Aug 2017 12:32:50 GMT

This is extremely helpful. Thank you. I'll give it a try.

Re: JSONToFeatures_conversion: Cannot create a table with a duplicate column

RyanDavis1 — Fri, 18 Aug 2017 20:31:53 GMT

Thanks, rvburton It's very appreciated. I took your first suggestion and created a function to change the type to "esriFieldTypeOID". I won't try to claim it's the most elegant code, but I'll post it here in case it's helpful to someone later.

def change_objectID_type(json_file):
"""Changes objectID type from esriFieldTypeInteger to esriFieldTypeOID"""
with open(json_file, 'rb') as f:
json_dict = json.load(f)
oid = json_dict['objectIdFieldName']
field_list = json_dict['fields']
for field in field_list:
if field['name'] == oid:
field['type'] = 'esriFieldTypeOID'
else:
pass
with open(json_file, 'wb') as json_out:
json.dump(json_dict, json_out)