publish() a CSV to Enterprise portal: Decoding JSON has failed

[JaredPilbeam2]

I'm the same person who posted about this in the ArcGIS API for Python forum. I'm trying use the publish() method in a stand-alone script. The only documentation I see for it is for the API. I'm able to publish a CSV in a Pro Notebook. But, when I try this in a python IDE the script runs but produces nothing but an error:

json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

The error is coming from this line:

  }, data=fp)

Script:

from arcgis.gis import GIS
import os

gis_dest = GIS('portalurl', 'user', 'pass')
# data
fp = r'C:\Users\jpilbeam\Test\c19_Vaccine.csv'
# content manager
cm = gis_dest.content

# 1. add
item = cm.add(item_properties={
    'type' : 'CSV',
    'title' : 'c19',
    'tags' : 'tags',
    'typeKeywords' : "CSV"
    }, data=fp)

# 2. analyze
analyze_csv = cm.analyze(item=item, file_type='csv')
pp = analyze_csv['publishParameters']

# 3.  Modify the publish parameters then publish
#     For tables ensure the `locationType` is None
pp['locationType'] = 'none'
pitem = item.publish(publish_parameters=pp, file_type='csv')

[LongDinh]

Hi JaredPilbeam2,

The decoder error that appears is from the 'json' module.

What is occurring is the ContentManager is trying to request the data (url/path), convert the response to the  JSON format and return it as a Python Dictionary. However, the response from the request fails to decode values from your csv input.

Your csv file may contain JSON keywords that do not decode well in the Python 'json' module. Quite often this is due to a WHERE clause field that can't escape the " or \ in a to JSON string correctly.

Some ways you might test this is to try to load your csv to json using pandas. Something like:

import json
from pandas import pandas as pd

myCSVFileInput = r'C:\csvfile.csv'
myJSONFileOutput= r'C:\jsonfile.json'
# read the file into a pandas dataframe & convert to json
df = pd.read_csv(myCSVFileInput)
df.to_json(myJSONFileOutput)

# Try to decode the new JSON file using json module
with open(myJSONFileOutput) as jFile:
   jsonData = json.load(jFile)

[JaredPilbeam2]

Hi LongDinh,

Thanks for the explanation. Does the decode step sort of clean up the json? After using the json module to decode I attempted to convert back to a CSV (because that's the file type I ultimately need). I just tried this real quick, but got an AttributeError.

with open(myJSONFileOutput) as jFile:
    jsonData = json.load(jFile)
jsonData.to_csv(r'C:\Users\jpilbeam\Test\decodedCSV.csv', encoding='utf-8', index=False)

AttributeError: 'dict' object has no attribute 'to_csv'

EDIT:

I was able to convert that json back to a csv (I think?).

with open(myJSONFileOutput) as jFile:
    jsonData = json.load(jFile)
    df = pd.DataFrame.from_dict(jsonData)
    df.to_csv(r'C:\Users\jpilbeam\Test\decodedCSV.csv', encoding='utf-8', index=False)

Then I ran the initial script on this 'decodedCSV.csv'. Same error. I guess I didn't change anything.

[JaredPilbeam2]

In a Pro Notebook if I continuously run the initial code it throws that error only until about the seventh time then it finally works. How's that?

[JaredPilbeam2]

If it matters, here's my whole script. My only option is to publish as a CSV because in the first part I write requested JSON to a CSV. If I run this I'll get the 'Decoding JSON has failed' error (even in a try/except) and the CSV will neither be added nor published.

import json,requests
from arcgis.gis import GIS
import os,sys
import csv
from datetime import datetime

#the directory you want the csv written to
file_path = r'C:\Users\jpilbeam\Test'
#file name
csv_file = 'c19_VaccineTest'
##idph url goes here
idph_data = 'https://idph.illinois.gov/DPHPublicInformation/api/covidVaccine/getVaccineAdministrationCurrent'

#1----------------this section writes the json to a csv--------------

full_file = os.path.join(file_path, csv_file) + str(datetime.now().strftime('_%m_%d_%Y_%H%M')) + '.csv'

#get json data from idph
response = requests.get(idph_data,verify=True)
#read the json response and keep the VaccineAdministration part
data = response.json()['VaccineAdministration']

#write to file
with open(full_file, 'w', newline='', encoding='UTF-8') as csvfile:
    f = csv.writer(csvfile) 
    #write the headers of the csv file
    f.writerow(['County','AdminCount','AdminCountChange', 'RollAvg', 'AllocDoses', 'FullyVaccinated', 'FullyVaccinatedChange', 'ReportDate', 'Pop', 'PctVaccinated', 'LHDInventory', 'CommInventory','TotalInventory', 'InventoryDate'])
    for elem in data:
        #get the values for all the keys (i.e. CountyName, AdministeredCount, etc...)
        f.writerow([elem['CountyName'], elem['AdministeredCount'], elem['AdministeredCountChange'], elem['AdministeredCountRollAvg'], elem['AllocatedDoses'], elem['PersonsFullyVaccinated'], elem['PersonsFullyVaccinatedChange'], elem['Report_Date'], elem['Population'], elem['PctVaccinatedPopulation'], elem['LHDReportedInventory'], elem['CommunityReportedInventory'], elem['TotalReportedInventory'], elem['InventoryReportDate']])
    
print('csv successfully written')

#2------------this section takes that csv and publishes it to Enterprise Portal--------------

#provide Enterprise Portal login info here
gis_dest = GIS('portalURL', 'user', 'pass', verify_cert=False, trust_env=True)
#content manager
cm = gis_dest.content

print('publishing to Enterprise Portal...')
try:
    #add the CSV file
    item = cm.add(item_properties={
        'type' : 'CSV',
        'title' : 'C19VaccineTest',
        'tags' : 'test',
        'typeKeywords' : "CSV"
        },
           data=full_file)

    #analyze the CSV file to autogenerate the publish parameters
    analyze_csv = cm.analyze(item=item, file_type='csv')
    pp = analyze_csv['publishParameters']

    #Modify the publish parameters 
    #for tables ensure the `locationType` is None
    pp['locationType'] = 'none'
    itemid = item.id

    #Publish
    item.publish(publish_parameters=pp, overwrite=True, file_type='csv', item_id=itemid)
   
   
except ValueError as e:  # includes json.decoder.JSONDecodeError
    print(f'Decoding JSON has failed: {e}')

print("---done---")

[LongDinh]

So I managed to publish the table service using parts your code. What versions of the GIS module, Portal and ArcGIS Server are you using?

My assumption is that you are using AGS 10.8.1+ since you are using the item.publish item_id parameter to overwrite. 

If you are using ArcGIS Server, you can access your server's REST API and perform add contents and analyze by sending a requests.post() to the urls. See if they work individually with your files. 

https://developers.arcgis.com/rest/users-groups-and-items/analyze.htm

https://developers.arcgis.com/rest/users-groups-and-items/add-item.htm

[JaredPilbeam2]

Hi LongDinh,

That's great you were able to publish. If you don't mind I'd like to pick your brain a bit more. I'm using GIS module/Python API v. 1.8.4, Portal v. 10.8.

Would it be possible for you to post your code so I can see what you did? I'm looking into sending a requests.post(). Yes, I'm using ArcGIS Server v. 10.8.

[LongDinh]

Hi JaredPilbeam2,

Apologies for the delayed response - was on a break. The only thing I changed with my code was the item_id and overwrite kwargs in item.publish as I am using AGS 10.7.1 which does not accept them.

item.publish(publish_parameters=pp, file_type='csv')

If this isn't working, it might be something to do with your Portal Installation - I'm not really sure how to debug this issue.

In regards to sending requests.post() manually, it is a bit harder. You will need to determine which REST APIs to use (portal and/or admin).

For the arcigs.GIS implementation as Python REST Requests, (not exactly sure but) you can call the following  :

1. /Analyze/POST,
2. /user/{username}/AddItem, or /user/{username}/PublishItem (I think it's the latter)

See notes: https://developers.arcgis.com/rest/users-groups-and-items/add-item.htm and  https://developers.arcgis.com/rest/users-groups-and-items/analyze.htm 

Your requests for #1 should look be something the below. It should work with your current codeblock which creates full_file path

import requests 

analyzeUrl = "https://domain.gis.com/arcgis/sharing/rest/content/features/analyze"

payload={'filetype': 'csv',
'f': 'json'}
files=[
  ('file',(os.path.basename(full_file),open(full_file),'text/csv'))
]
headers = {}

response = requests.request("POST", analyzeUrl, headers=headers, data=payload, files=files)

print(response.text)

I highly recommend using Postman - an application for HTTP/S request management. Postman will also allow you to convert your HTTPS request to Python and other languages.

[JaredPilbeam2 · Accepted solution]

@LongDinhThanks for the help. This was recently resolved. After a lot of trouble shooting, I still don't know that we (an ESRI analyst and I) nailed down a cause of that JSONDecode error because we tried so many things. But, this at least may be informative:

When running a Python script that is set up to add then publish an item to Portal for ArcGIS the POST request to the /addItem endpoint of the Portal is being redirected, which causes the script to crash.

What we ultimately came up with is a two part script that publishes a CSV to Portal,

import json,requests
from arcgis.gis import GIS
import os,sys
import csv
from datetime import datetime

#the directory you want the csv written to
file_path = r'C:\Users\jpilbeam\Test'
#file name
csv_file = 'c19_Vaccine_Current_standAloneScriptTest'
##idph url goes here
idph_data = 'https://idph.illinois.gov/DPHPublicInformation/api/covidVaccine/getVaccineAdministrationCurrent'


#1----------------this section writes the json to a csv--------------

full_file = os.path.join(file_path, csv_file) + '.csv'

#get json data from idph
response = requests.get(idph_data)
#read the json response and keep the VaccineAdministration part
data = response.json()['VaccineAdministration']

#write to file
with open(full_file, 'w', newline='', encoding='UTF-8') as csvfile:
    f = csv.writer(csvfile) 
    #write the headers of the csv file
    f.writerow(['County','AdminCount','AdminCountChange', 'RollAvg', 'AllocDoses', 'FullyVaccinated', 'FullyVaccinatedChange', 'ReportDate', 'Pop', 'PctVaccinated', 'LHDInventory', 'CommInventory','TotalInventory', 'InventoryDate', 'Latitude', 'Longitude'])
    for elem in data:
        #get the values for all the keys (i.e. CountyName, AdministeredCount, etc...)
        f.writerow([elem['CountyName'], elem['AdministeredCount'], elem['AdministeredCountChange'], elem['AdministeredCountRollAvg'], elem['AllocatedDoses'], elem['PersonsFullyVaccinated'], elem['PersonsFullyVaccinatedChange'], elem['Report_Date'], elem['Population'], elem['PctVaccinatedPopulation'], elem['LHDReportedInventory'], elem['CommunityReportedInventory'], elem['TotalReportedInventory'], elem['InventoryReportDate'], elem['Latitude'], elem['Longitude']])
    
print('csv successfully written')


#2------------this section takes that csv and publishes it to Enterprise Portal--------------

#provide Enterprise Portal login info here
gis_dest = GIS("home")

#content manager
cm = gis_dest.content

#add the CSV file
status = 1
while status <= 1:
    try:
        item = cm.add(item_properties={
            'type' : 'CSV',
            'title' : 'CoVID19_csvtest',
            'tags' : 'test',
            'typeKeywords' : "CSV"
            },
                data=full_file)
        status = 2
        print("Item added successfully.")
    except:
        print("Item failed to add, trying again...")
        status = 0
    

#analyse/publish the CSV file
status = 1
while status <= 1:
    try:
        #analyze the CSV file to autogenerate the publish parameters
        analyze_csv = cm.analyze(item=item, file_type='csv')
        pp = analyze_csv['publishParameters']
        #Publish
        pitem = item.publish(publish_parameters=pp)
        print(pitem.layers)
        print("---done---")
        status = 2
    except:
        print("Item publishing failed, trying again...")
        status = 0

and because this is run on a regular basis the feature layer needs to be overwritten

import json,requests
from arcgis.gis import GIS
from arcgis.features import FeatureLayerCollection
import os,sys
import csv
from datetime import datetime

#the directory you want the csv written to
file_path = r'C:\Users\jpilbeam\Test'
#file name
csv_file = 'c19_Vaccine_Current_standAloneScriptTest'
##idph url goes here
idph_data = 'https://idph.illinois.gov/DPHPublicInformation/api/covidVaccine/getVaccineAdministrationCurrent'


#1----------------this section writes the json to a csv--------------

full_file = os.path.join(file_path, csv_file)+ '.csv'
if os.path.exists(full_file):
    os.remove(full_file)

#get json data from idph
response = requests.get(idph_data)
#read the json response and keep the VaccineAdministration part
data = response.json()['VaccineAdministration']

#write to file
with open(full_file, 'w', newline='', encoding='UTF-8') as csvfile:
    f = csv.writer(csvfile) 
    #write the headers of the csv file
    f.writerow(['County','AdminCount','AdminCountChange', 'RollAvg', 'AllocDoses', 'FullyVaccinated', 'FullyVaccinatedChange', 'ReportDate', 'Pop', 'PctVaccinated', 'LHDInventory', 'CommInventory','TotalInventory', 'InventoryDate', 'Latitude', 'Longitude'])
    for elem in data:
        #get the values for all the keys (i.e. CountyName, AdministeredCount, etc...)
        f.writerow([elem['CountyName'], elem['AdministeredCount'], elem['AdministeredCountChange'], elem['AdministeredCountRollAvg'], elem['AllocatedDoses'], elem['PersonsFullyVaccinated'], elem['PersonsFullyVaccinatedChange'], elem['Report_Date'], elem['Population'], elem['PctVaccinatedPopulation'], elem['LHDReportedInventory'], elem['CommunityReportedInventory'], elem['TotalReportedInventory'], elem['InventoryReportDate'], elem['Latitude'], elem['Longitude']])
    
print('csv successfully written')


#2------------this section takes that csv and publishes it to Enterprise Portal--------------

#provide Enterprise Portal login info here
gis_dest = GIS("home")

#Get published item's ID to overwrite
hostedFeatureService = gis_dest.content.get('5c770f0e208d4504872a7e4b2a825124')
hostedFeatureLayerCollection = FeatureLayerCollection.fromitem(hostedFeatureService)

#overwrite existing HFL contents
status = 1
while status <= 1:
    try:
        result = hostedFeatureLayerCollection.manager.overwrite(full_file)
        print('layer overwritten')
        print('---done---')
        status = 2
    except:
        print("Layer failed to overwrite, trying again...")
        status = 0