Use OData Endpoints to Import Data into Excel

You can use OData endpoints to import data directly into Excel. The OData query model provides access to the objects (entities) within the Anthology Student data model. You can use the ds/odata endpoint to gain access to the query. For example, the following simple OData query provides access to the Programs entity.

https://hostname:port/ds/odata/Programs

Once the base query is added to Excel, navigation properties within the entity can be expanded using the Excel Query Builder. Any available navigation property can be included in the import.

Prerequisites

You must have a user ID and password that has access to Anthology Student.

You must be authorized to access the type of data. For more information, see Security Enhancement for OData Queries.

Procedure to Import Data into Excel

In most cases, you will want to specify just the object (entity) in the query. For example: 

  • https://hostname:port/ds/odata/Programs

  • https://hostname:port/ds/odata/StudentEnrollmentPeriods

Specifying selects, filters, and expands can reduce the capabilities you will have with the Query Editor.

  1. Access Excel and select the default Blank workbook.

  2. Select the Data tab New Query menu > From Other Sources command > From OData Feed command.

  3. On the OData feed dialog, select the Basic radio button and specify or paste the URL in the URL field.

  4. Select the OK button.

  5. Select Basic for authentication and specify the same credentials you use to access Anthology Student. (If you are using a domain account, enter your username as username@domain.) 

  6. On the dialog, select the Edit button.

  7. Use the Query Builder dialog to make your changes.

  8. When you are done, select the Home tab and use the Close & Load menu to select the appropriate option.