topic Re: publish() a CSV to Enterprise portal: Decoding JSON has failed in Python Questions

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

JaredPilbeam2 — Thu, 25 Mar 2021 21:15:54 GMT

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

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

LongDinh — Thu, 25 Mar 2021 23:08:11 GMT

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)

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

JaredPilbeam2 — Fri, 26 Mar 2021 18:01:55 GMT

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.

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

JaredPilbeam2 — Fri, 26 Mar 2021 18:50:20 GMT

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?

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

JaredPilbeam2 — Fri, 26 Mar 2021 20:31:44 GMT

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

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

LongDinh — Sun, 28 Mar 2021 23:30:50 GMT

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

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

JaredPilbeam2 — Mon, 29 Mar 2021 14:57:23 GMT

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.

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

LongDinh — Mon, 12 Apr 2021 03:06:34 GMT

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 :

/Analyze/POST,
/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.

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

JaredPilbeam2 — Wed, 14 Jul 2021 14:21:10 GMT

@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