We Are Here To Help

Follow

Analyze Socrata data in Microsoft Excel® using Socrata Open Data Connector

Microsoft Excel® is a very popular tool for analyzing and visualizing data. Follow these instructions to create an Excel workbook with a link to public data from any Socrata-powered website.

Because Excel maintains an active link to the data, all you need to do to refresh your workbook with the latest data is click the “Refresh All” button in the Excel ribbon.


 

Finding a dataset’s OData URL

Every dataset has a unique OData URL. When viewing a dataset select the “Export” pane and choose the OData section. Copy the OData endpoint to your clipboard:



Accessing Socrata data using Power Query for Microsoft Excel

Socrata Odata connections are only available through Microsoft Power Query. Power Query for Microsoft Excel provides more querying capabilities than Excel’s default Data tab. Here’s how you can use Power Query to create a link to a Socrata dataset.

Step 1: Install the Power Query feature

If you are using Excel 2016, you can skip this step (Power Query is built into Excel 2016 as part of the Get & Transform tool).

Download and install the latest version of Microsoft Power Query. You’ll need an installed version of Office 2010 Professional Plus, 2013 Professional Plus, Office 365 ProPlus, or a standalone edition of Excel 2013.

Step 2: Select the “Power Query” ribbon

In Excel 2016 from the Data Ribbon under Get & Transform choose "New Query" “From Other Sources” “From OData Feed”:

Power_Query_2016.png

In Excel 2010 and Excel 2013 from the Power Query Ribbon, choose “Get External Data” “From Other Sources” “From OData Feed”:

 

Step 3: Paste the OData endpoint link into the dialog box that appears and click “Ok”

When accessing private datasets you will be prompted to authenticate your credentials in order to view the data. In the window provided select "Basic" and enter your Socrata username and password.

Step 4: Shape the data using Power Query

Note that you will want to use the column header to expand complex types like Locations and Website URLs. When finished, click Apply and Close.

 

Result: Your data will appear in a table in Excel

 

Querying using OData

You can also directly query Socrata data using OData as a REST API.Socrata datasets follow the OData URI Conventions documented in http://www.odata.org/documentation/odata-version-2-0/uri-conventions/. For more information on OData, see http://www.odata.org/introduction

Here are some examples of how to apply these conventions to a Socrata catalog and dataset:

List all the datasets available for a domain.

https://sandbox.demo.socrata.com/OData.svc

Retrieve all the items in a particular dataset.

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp

Retrieve one item, by identifier

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp(16)

Retrieving a particular field within the item

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp(16)/depth/$value 

The OrderBy Query option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$orderby=magnitude

The Top Query option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$top=2

The Skip Query Option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$skip=1005

The Filter Query Option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$filter=magnitude%20gt%205

The Select Query Option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$select=magnitude,depth

The InlineCount Query Option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$filter=magnitude%20gt%205&$inlinecount=allpages

Socrata Datatypes exposed as OData

This table lists the OData types used to represent Socrata data.

Socrata Datatype ODataDatatype
Checkbox Edm.Boolean
Date & Time Edm.DateTime
Date & Time (with timezone) Edm.DateTimeOffset
Document Complext type with Edm.String listing the URL to the document and Edm.String listing the name of the document
Email Edm.String
Location Complex type with Edm.Decimal for latitude/longitude and Edm.String for Address, City, State and Zip
Money Edm.Decimal
Multiple Choice Edm.String
Number Edm.Decimal
Percent Edm.Decimal
Phone Complext type with Edm.String listing the phone number and Edm.String listing the phone type
Photo Edm.String listing the URL to the photo
Text (Formatted) Edm.String with HTML tags
Text (Plain) Edm.String
Website URL Complex type with Edm.String listing the URL and Edm.String listing the description

Four data types are not exposed via OData. These are: Star, Flag, Nested Table and Dataset Link.

Release Notes - February 2014

  • DateTimeZone filters using “before”, “after”, “between” in the Power Query editor will yield an error.

  • Pulling the entire opendata.socrata.com catalog is not supported because this domain has too many datasets. Linking to a dataset directly using the Data ribbon in Excel will work.

Release Notes - August 2015

  • Now you can seamlessly keep your Odata instance of both private and public datasets up to date when you use these OData enabled analytics tools.
  • Unlike with public datasets, the client, e.g. Excel or Tableau, will prompt you to enter your Socrata credentials to ensure you have permissions to view the private datasets.  

Microsoft, Excel, and Office 365 are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk