Using an Excel file in desktop to feed the dashboard

LMBaptista

Hi, all.

The principle is:

I have an excel file that is being filled by several users. I want to follow up this info in a dashboard.

The question(s) is:

1 - Can I use an excel file in a local net location as source data to a dashboard? I think not.

2 - How can I turn around the problem?

2a) keep the original file in the intranet net location where people fill it and periodically I update the xlsx file in AGOL. Boring and not in real time.

2b) the excel file goes to AGOL and I need an application to fill it online. Here I can't find a good solution because all the apps I know from ESRI are maps oriented. I don't need maps, just some fields to put alphanumerical information related to spatial information in the database.

Any help, ideas or suggestions are welcome

 

jcarlson

For your first question, the simple answer is "no". Or at least, "not easily".

I'm assuming that the edits are all made to the excel sheet, and the AGOL layer will just be a copy? If that's the case, you could schedule a Python script to periodically read the excel sheet and push updates to a hosted layer. We do that kind of thing in our org, and updating even very large datasets typically takes under a minute, so you could have the dashboard be nearly real-time.

Here's the process.

1. Create a standalone table in AGOL

You can do this by publishing the Excel sheet, but I find that AGOL tries to "guess" the data type when you upload a spreadsheet. Even when the upload process checks and asks for the field types, I have had the resulting layer make mistakes anyway. You're better off creating the table from scratch so that you can have full control over the fields.

2. Write a Python script

This part could be as simple or complex as you want it to be. How many rows are in the spreadsheet, by the way? If it's a large dataset, you can have your script compare the source and destination tables and only submit edits. But if it's a small dataset, you could just truncate and append. It might look like this:

from arcgis import GIS
import pandas as pd

# connect to AGOL
gis = GIS('https://your-org.maps.arcgis.com', 'user', 'password')

# load source data
df = pd.read_excel('path-to-file.xls', 'specific-sheet')

# do some reshaping here
# in general, pandas does better at inferring data types, but it can still be wrong.
# use pandas to alter and adjust fields as needed to match the output table

# get destination layer
dest_table = gis.content.get('itemid of table').tables[0]

# remove existing features
dest_table.manager.truncate()

# load in new data
dest_table.edit_features(adds=df.spatial.to_featureset())

 Like I said, it could be as simple as that. Our get more complex to handle weird incoming data or to make the process more efficient, but this will still work.

When this script runs, the hosted layer will be refreshed with the latest data.

3. Schedule a Task

In Windows, you'll have to wrap this script in a BAT file, probably. However you need to do it (will depend on what machine is running this), just make sure that the script executes in a location that has access to the shared spreadsheet.

Schedule the task to run as often as you need. This may depend on how quickly the script can execute. I mentioned there are ways to make that process a lot more efficient, so let me know if it doesn't run quick enough for you. If you want the data to be near realtime, just have this run every few minutes, and you should be all set!