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”:
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.
Retrieve all the items in a particular dataset.
Retrieve one item, by identifier
Retrieving a particular field within the item
The OrderBy Query option
The Top Query option
The Skip Query Option
The Filter Query Option
The Select Query Option
The InlineCount Query Option
Socrata Datatypes exposed as OData
This table lists the OData types used to represent Socrata data.
|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|
|Location||Complex type with Edm.Decimal for latitude/longitude and Edm.String for Address, City, State and Zip|
|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|
|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.