How to convert json to table in arcpy?

NiharSahoo

I have data in JSON format that I'd like to convert to a table in ArcPy. I have used arcpy.CreateTable_Management() but this will require input in '.dbf' format only ? So please let me know all the possible ways to achieve it. Currently, I am converting the json data to Excel format, and then using Excel to Table conversion using ArcPy, I am able to do it.

arcpy.ExcelToTable_conversion(in_excel, out_table, sheet)

HannesZiegler

Assuming the JSON is formatted like this:

{'col_1': [3, 2, 1, 0], 'col_2': ['a', 'b', 'c', 'd']}

You could convert the JSON to a pandas DataFrame using from_dict, then use the ArcGIS API for Python to create a table/featureclass from it. 

Something like this should work (note, this should work in theory but I did not test it, please read this as pseudocode, it's probably not the full solution and you might need to fill in the gaps as needed):

 

import json
import pandas as pd
import arcgis


with open("c:\myjson", "r") as f:
  data = json.loads(f)

df = pd.DataFrame.from_dict(data)  # Note, column data types will be inferred
                              # You may need/want to set them explicitly

df.spatial.to_featureclass("C:\workspace.gdb\mytbl")

 

Hope this helps!