nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Excel × Python Combined Technique: How to Bulk Update Item Details in ArcGIS Online / Portal

ShinjiKATAYA1

Introduction

ArcGIS Online andArcGIS Enterprise Portal To widely utilize items registered in , detailed information input such as item title, summary, description, tags, and usage restrictions is required.

That said, opening each item one by one to input and update detailed information is quite a tedious task.

In the US Esri partner companyGEO Jobe 'sAdmin Tools paid version, bulk updates from CSV are possible, but this time we introduce a method to bulk update item details using Excel × Python (ArcGIS API for Python). No explanation needed; if you want to check the sample Excel and Python code immediately, as usual,published on GitHub, please refer to it.

Note: This method was implemented when updating and publishing item details of file geodatabase (FGDB) listed in the file "A Utilization Site Collecting Use Cases of 3D City Models in ArcGIS" published in 2021. This utilization site is built withArcGIS Hub Basic, so in the code, a specific group ID of the site created in ArcGIS Hub is specified as a group. The group ID can be confirmed on ArcGIS Online as shown in the figure below, but the ID can be any group belonging to the user other than groups created in ArcGIS Hub.The group ID of the site “Ministry of Land, Infrastructure, Transport and Tourism 3D City Model ‘Project PLATEAU’ Utilization Contents” created in ArcGIS Hub.

Also, for how to create and start using sites in ArcGIS Hub, please refer to the "ArcGIS Hub Start-up Guide" or "A quick start guide to ArcGIS Hub Basic" etc.

Figure - Example of group ID (Our company’s “Ministry of Land, Infrastructure, Transport and Tourism 3D City Model ‘Project PLATEAU’ Utilization Contents” group)

1 Procedure to Bulk Update Item Details

1.1 Item Properties

First, let's confirm the API for updating item details with ArcGIS API for Python. According to the API reference, updating item details is possible with the method of arcgis.gis.Item class called

update(item_properties=None, data=None, thumbnail=None, metadata=None)

and item_properties can specify a list of dictionary keys. The Help documents keys such as type, typeKeywords, description, title, url, tags, text, snippet, extent, spatialReference, accessInformation, licenseInfo, culture, access, commentsEnabled.

In this sample we will update title, snippet, description, tags, licenseInfo, accessInformation. To help you understand more intuitively, here is a correspondence between keys specified in item_properties and actual screen elements.

id : Item ID
title : Title
snippet : Summary
description : Description (HTML allowed)
tags : Tags (If multiple tags exist, separate with commas)
licenseInfo : Usage Restrictions
accessInformation : Copyright Information

1.2 Organizing Item Details Information into Excel

Since we understood what information organizes item details to be updated, enter each key of item_properties in row 1 of Sheet1 (in the sample Excel file named “UpdateSheet1” for example), then enter each item's properties from row 2 downwards (using keys converted by .to_dict() within code). Note that column names in row 1 must exactly match keys of item_properties.

Also note that since description column contains HTML but should be unified into easier-to-read descriptions visually, VBA macro was actually used for updating. Because distributing Excel files with macros may cause security issues around here’s macro code provided separately as “UpdateDescription_SampleVBA.txt” for your reference if needed.

Figure - Example of organizing data in Excel (Example input from sample Excel file used in development test environment)

1.3 Explanation of Code to Bulk Update Items

After organizing into Excel, just modify slightly and execute code recorded in notebook according to your environment. Specifically adjust places recorded as “your_xyz”, “new_owner_name”, “share_group_id” inside notebook according to your environment before running.

Comments are also written inside notebook but again explanation about processing steps follows below.

(1) Read Excel Data into Pandas DataFrame

First read Excel into Pandas DataFrame.

import pandas as pd
excel_file = r"your_folder_path\3DCityModel_AGOL_ItemDetails_forDevTest.xlsx" 
df = pd.read_excel(excel_file)

Note: Since xlrd library version 2.0.0 only supports "xls" format now depending on environment when reading "xlsx" with pd.read_excel you need to specify openpyxl as engine like below. In ArcGIS Pro cloned environment xlrd version is 1.2.0 so basically no need to specify engine (For how to create cloned environment in ArcGIS Pro see [ArcGIS Developer Resources] - [Upgrading arcgis package] - [For environments above ArcGIS Pro 2.3]).

import pandas as pd
excel_file = r"your_folder_path\3DCityModel_AGOL_ItemDetails_forDevTest.xlsx" 
df = pd.read_excel(excel_file,engine='openpyxl')

(2) Connect to ArcGIS Online Organization Site and Update Item Info & Share with Specific Group

Next connect to ArcGIS Online organization site and for each row get item by 'id' from Excel then update item's 'title', 'snippet', 'description', 'tags', 'licenseInfo', 'accessInformation' using item.update(). Also set deletion protection effective by item.protect() and share items with organization and specific groups by share().

As explained above too keys available for item_properties and their screen text correspondence are described in 1.1 Item Properties section.

from arcgis.gis import GIS
gis = GIS("https://www.arcgis.com/", "your_username", "your_password")

for index, row in df.iterrows():
    item_id = row[0] # Get id
    item_properties = row[1:7].to_dict() # Get title through accessInformation columns as dictionary
    my_item = gis.content.get(item_id) # Get item object
    my_item.update(item_properties) # Update item's title through accessInformation info via item.update() method
    my_item.protect(enable = True) # Enable deletion protection on item to prevent accidental deletion
    my_item.share(org= True, groups=['share_group_id']) # Share with organization and specific group - Test environment; “Open Data Shared Contents” (id:f862b4f5d32f435596795947df012042), Production environment; “Ministry of Land Infrastructure Transport and Tourism 3D City Model ‘Project PLATEAU’ Utilization Contents” (id:d568e2190c4f456f8bd812c8e07c719a)

2 Explanation of Posting Procedure to ArcGIS Hub Catalog Site

Note: Originally procedures up to "Bulk Updating Item Info" above and those from here differ because operators differ or business process differs so it might be better separated into different notebooks. This time we wrote them into same notebook file conveniently so please understand.

If splitting sample notebook file please add necessary processing before code explained at (1) Read Excel Data into Pandas DataFrame below.

import pandas as pd
excel_file = r"your_folder_path\3DCityModel_AGOL_ItemDetails_forDevTest.xlsx" 
df = pd.read_excel(excel_file)

2.1 Explanation of Code Posting to ArcGIS Hub Catalog Site

'''(3) As administrator connect to ArcGIS Online organization site then change ownership of items and share with specific groups and publish publicly'''

'Last step is first reassign_to() changes ownership of items (ownership change not always necessary but this time changing from personal account to our company public account), then new owner shares items with organization and specific groups via share() method plus publishes publicly.'',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''',''','''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '''''''' '' ','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'gisadmin = GIS("https://www.arcgis.com/", "your_admin_username", "your_admin_password") # Administrator account connection

gisadmin = GIS("https://www.arcgis.com/", "your_admin_username", "your_admin_password") # 管理者

for index, row in df.iterrows():
    item_id = row[0] # id を取得
    my_item = gisadmin.content.get(item_id)
    # 最初に所有者変更
    my_item.reassign_to("new_owner_name") # テスト環境；"kataya@dev3admin"、本番環境："Esri_Japan"
    To reflect content published on ArcGIS Hub, after changing the owner, share the specified group of the organization again
    my_item.share(everyone = True, org= True, groups=['your_share_group_id']) # Share with organization and specified group - Test environment; "Open Data shared content" (id:f862b4f5d32f435596795947df012042), production environment; "Ministry of Land, Infrastructure, Transport and Tourism 3D City Model 'Project PLATEAU' utilization content" (id:d568e2190c4f456f8bd812c8e07c719a)

Image after update

After running the script, when viewed from ArcGIS Online as a standalone item, it looks like the following.

Map - Example after updating a standalone item (ArcGIS Online item details: Development test environment "3D City Model (Project PLATEAU) Utsunomiya City File Geodatabase")

Also, in the ArcGIS Hub catalog site, it appears as follows.

Map - Example after updating a standalone item (ArcGIS Hub catalog site item details: Development test environment catalog site)

Here, if you focus on the center of the catalog site screen on "3D City Model Utsunomiya City (Project PLATEAU) File Geodatabase" and look at the description of the ArcGIS Online item details above (the description column in Excel), 107 characters are displayed. In other words, when publishing data on ArcGIS Hub, prioritizing necessary information in the first part of the description text of the item details is one technique to save double entry effort when making data public.

Summary

With the combination of Excel × Python (ArcGIS API for Python) introduced in this article, not only can you bulk update information of items in ArcGIS Online, but also save time logging in on the ArcGIS Hub side, and ultimately105 items could be published (Reference:Catalog of "3D City Model File Geodatabase").

- Notebook file, - Excel sample, - Sample of VBA macro to update description column for 3 files as usualPublished on GitHubPlease make use of it.

Also, as a point when using items within an organization, there is an article introducing "Item Management Points (Part 1)Prequel,Sequel)", so please refer to it together.