Automatically Enhancing Excel Data and Publishing to ArcGIS Online

Introduction

Want to use Microsoft Excel and ArcGIS Online in an automated workspace? Create a workspace in FME Desktop and set up an automation on FME Server that looks at an Excel sheet of addresses and publishes the final product to ArcGIS Online! Find the sample workspace in the Files section to the right of this article. This example showcases the ability of FME to use ArcGIS Online custom transformers, change detection, and directory watching.

Video

Transformers Used

Creator

This transformer is used to initiate the workspace when you run it or it is initiated by a trigger.

FeatureReader

This transformer is used to read in a file using a published user parameter so that it can be passed in when you eventually publish this to FME Server.

ArcGISOnlineBatchGeocoder

This is a custom transformer that uses ArcGIS Online’s REST API to perform an analysis service. This service matches point geometry with features that have addresses attached to one or more of their attributes.

ArcGISOnlineGeoenricher

This is a custom transformer that uses ArcGIS Online’s REST API to perform an analysis service. This service matches points with demographic data of your choice, such as age, population, or household income. 

AttributeExposer

This transformer is used to expose certain attributes that are embedded as list attributes of a feature. This is because the previous custom transformer, the ArcGISOnlineGeoEnricher, does not expose those attributes dynamically.

AttributeRemover

This transformer is used to remove unwanted attributes from the features.

ChangeDetector

This transformer is used to detect changes from one dataset to another, given a key or attribute that would be unique to compare. Has multiple output ports such as inserted and updated. We use the inserted output port for this example. 

BulkAttributeRenamer

This transformer is used to remove spaces and replace them with underscores “_” so that ArcGIS Online can upload the data in the attributes the same as the previous file.

FME Server

Publish Workspace to FME Server

With the workspace open, go to File > Publish to FME Server on the top menu bar. Step-by-step instructions can be found on the Publish a Workspace to FME Server and Run It article. We want to set this up as a “Job Submitter” Service because the Job Submitter Service accepts and runs workspace job requests.

Automation

Set up an automation with a “Resource or Network Directory” trigger that will search for file creations from a specific directory within the server resources. Connect this to a “Run a Workspace” action that will run the workspace you have recently uploaded. Make sure to set the file path to the path that the trigger receives from your created file. Accomplish this by selecting the drop-down next to “file path” and go to Directory > File Path. 

Additional Resources

• Getting Started with ArcGIS Online Custom Transformers
• How do I write updates to an existing ArcGIS Online feature service?

Data Attribution

The data used throughout this series originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.