topic Re: export content.search results to excel in ArcGIS API for Python Questions

export content.search results to excel

Anonymous User — Thu, 05 Mar 2020 19:44:45 GMT

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)

Re: export content.search results to excel

MehdiPira1 — Sun, 12 Dec 2021 08:39:26 GMT

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.

Re: export content.search results to excel

Raj-Chavada — Fri, 18 Sep 2020 00:11:19 GMT

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

Re: export content.search results to excel

Anonymous User — Fri, 18 Sep 2020 00:38:24 GMT

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

Re: export content.search results to excel

MehdiPira1 — Sun, 12 Dec 2021 08:39:29 GMT

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.

Re: export content.search results to excel

Raj-Chavada — Fri, 18 Sep 2020 22:30:56 GMT

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

Re: export content.search results to excel

NathanBaier1 — Mon, 27 Jun 2022 15:38:01 GMT

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?