export content.search results to excel

03-05-2020 11:44 AM
by Anonymous User
Not applicable

how do i export the results of the search to excel?  In the export i would want username, modified date, created date and view count

import arcgis
from arcgis.gis import GIS

portal = "https://xxxxx"

gis = GIS(portal, "username", "password", verify_cert=False)
print ("Connected")

items = gis.content.search(query="owner: s*",item_type='', sort_field="numViews", sort_order= "asc", max_items=5000, outside_org=False)
for item in items:

0 Kudos
5 Replies
Esri Contributor

Hi Louis K.

Here's the script that does what you are after:

from arcgis.gis import GIS
from arcgis.features import SpatialDataFrame
import pandas as pd

gis = GIS(portal, "username", "password", verify_cert=False)

search_result = gis.content.search(query="owner: s*", item_type="Feature Service", sort_field="numViews", sort_order="asc", max_items=5000)
df_list = []
for item in search_result:
    items = item.items()
    df = pd.DataFrame.from_dict(items)
    dfTransposed = df.T
    dfTransposed.columns = dfTransposed.iloc[0]
    dfTransposed = dfTransposed[1:]
    dfTransposed = dfTransposed.set_index('name', inplace=False)
    dfTransposed = dfTransposed[["owner", "created", "modified", "numViews"]]
merged_df = pd.concat(df_list)
# export to excel
merged_df.to_excel(r"Path to output.xlsx")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Just input your destination path for the excel output to the script.

New Contributor III

Is there a way to convert the date format (e.g. 1570463334131) to more readable when exporting into excel file?

0 Kudos
Esri Contributor

I believe this can be done by python DateTime object converting timestamp to date format. 

Esri Contributor

Adityaraj Chavada

The function below does the trick:

import datetime

def modifiedTime(index, dataframe):
    ts = dataframe.iloc[index].created/1000
    convertedTime = datetime.datetime.utcfromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
    return convertedTime‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This can be included in the for loop to convert the unix time. This is for the 'created' column.

Same can be applied to the 'modified' column too.

New Contributor III

Thanks, Mehdi Pira‌! I am no expert at ArcGIS API for Python. Forgive me for silly question. Can you confirm where exactly I need to insert this function in the for loop and how I can get the updated date printed in the excel file? Thank you so much for your help..

0 Kudos