export content.search results to excel

1402
6
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:
display(item)

0 Kudos
6 Replies
MehdiPira1
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)
print("Connected")

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"]]
    df_list.append(dfTransposed)
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.

Raj-Chavada
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
by Anonymous User
Not applicable

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

MehdiPira1
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.

Raj-Chavada
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..

NathanBaier1
New Contributor III

I am struggling to make the table actually display the dates in the proper format using your code. I'm new to python coding and still learning. How would you take the output of the above code and place it into the code for the output table?

0 Kudos