OneRoster CSV Extract Configuration

Last Modified on 10/22/2022 10:09 am CDT

PATH: System Administration > Data Utilities > Data Extract Utility > Data Extract Utility Set Up

This document provides instructions on how to configure the Data Extract Utility to produce CSV files that match the OneRoster specification. A OneRoster CSV export produces zip files containing the 11 supported CSV files.

Campus supports OneRoster CSV data in the 1.1 format.

This CSV extract is provided “as is.” It will not be kept up to date, and is not certified by IMS Global. Alternatively, Infinite Campus fully supports the OneRoster specification via an API implementation that supports secure, real time connections between Campus and district’s 3rd-party vendors. Our API implementation is kept up to date and is certified by IMS Global. Infinite Campus makes this OneRoster v1.1 CSV extract available within the Data Extract Utility solely as a courtesy to districts when their 3rd-party vendors have not implemented OneRoster via an API.

All 3rd-party vendors who support OneRoster via their own API implementation should always connect via our API implementation and avoid using OneRoster CSV extracts. If your vendor does not have a OneRoster API implementation currently, Infinite Campus encourages their development team to develop one.

About OneRoster CSV Files

The Learning Information Services (LIS) is a standard developed by IMS Global for the exchange of student data between educational systems. IMS Global created the OneRoster data model for LIS to be more focused on K-12 education. The OneRoster data model allows student data to be exchanged via a REST API or through a set of CSV files.

OneRoster supports bulk and delta CSV exports. A bulk export produces a complete set of the student data while the delta export only contains the most recent changes. A bulk export provides vendors with their initial set of student data and is used to ensure data integrity between the Campus system and vendors. The delta export is intended to run more frequently to keep the Campus vendors up to date. The specific SQL/Prism procedure for bulk and delta is defined in the Campus application for each supported CSV file. These stored procedures allow for the quick configuration of the Data Extract Utility and allows for customers to receive updates without requiring any configuration changes. All of the stored procedures for the CSV export take in type and days arguments. The type argument is required and may be set to bulk to select bulk processing, or it can be set to delta to select delta processing. The days argument is optional and may be set to control how many days worth of data is exported with the delta export. The days argument is set to 1 day by default. This causes the delta export to export data for the last 24 hours.

Campus supports eleven of the fourteen CSV files defined by the OneRoster CSV specification. The three unsupported CSV files are related to importing grade-related data.

Specification Requirements

  • All of the CSV header names and filenames are case sensitive.
  • The CSV files have pre-defined filenames and each must have the .csv extension.
  • The ZIP file can have any filename, but it must have the .zip extension.
  • The CSV files must be in the root directory of the zip (no sub-folders).
  • The CSV files must include a header row.
  • The CSV files must include double quotes around values which contain a comma.
  • The CSV files must have a UTF-8 character encoding.

Setting Up The Files

Data Extract Utility must be installed and the user must have the proper tool rights to proceed with the following steps. The Data Extract Utility page also contains more information and definitions concerning the fields used to set up the OneRoster CSV Extract Configuration.

The following procedure details steps on how to define bulk and delta exports for each of the 11 supported CSV files.

  1. Enter the Extract Instance Name.
  2. Enter the File Name. See table below for a list of supported file names. All of the CSV header names and filenames are case sensitive.
  3. Select SFTP in the Delivery Mode field. This allows the user to enter the file name and file extension.
  4. Enter csv in the File Extension field. 
  5. Select CSV in the Column Delimiter dropdown.
  6. Select UTF-8 in the Character Set dropdown.
  7. Mark the Include header row? checkbox. 
  8. Mark the Include double quotes? checkbox.
  9. Select SQL.
  10. From the table below, select the appropriate text from the SQL/Prism Bulk column or the SQL/Prism Delta column for the desired filename.
  11. Enter the appropriate text in the SQL/Prism field. For example, to generate a manifest.csv file, copy "EXEC extract_V1P1OneRosterManifest @type = 'bulk'" from the table below and enter the text in the SQL/Prism field. 
    1. It is recommended to define a bulk export and a delta export for each of the 11 supported CSV files.
  12. To Save, enter all other required fields (such as Username and Password).
  13. Select the appropriate Test Extract, Test Connection, and/or Run Extract button. Extracts will display in a new window in the selected format. 
    • TheTest Extract button generates the file in the selected format. Data is not written to the database or stored in any location.
    • The Test Connection button checks the connection to the entered server by attempting to write a 1-byte file to the location setup. If it is successful, a pop-up displays indicating the connection is good. If it is not successful, the reason the connection failed is displayed.
    • The Run Extract button runs and generates the extract. It also sends and stores the file where needed (in case of an issue when it is an auto-run extract).

If multiple vendors need to consume the CSV data, the ZIP file should be exported to a shared location to ensure it is only generated once.

Data Extract Schedule lists the frequency, date, time and day of the week the extract runs. If this needs to stop for any reason, mark the Stop Automated Extract checkbox.

The queries used to generate the CSV files are expensive. The bulk and delta CSV export should be scheduled to run during off-peak hours.


Data Extract Utility Configuration, Bulk 

Data Extract Utility Configuration, Delta 

OneRoster Filenames and Procedures
Filename
SQL/Prism Bulk
SQL/Prism Delta
manifest.csv EXEC extract_V1P1OneRosterManifest @type = 'bulk' EXEC extract_V1P1OneRosterManifest @type = 'delta'
academicSessions.csv EXEC extract_V1P1OneRosterAcademicSessions @type = 'bulk' EXEC extract_V1P1OneRosterAcademicSessions @type = 'delta'
categories.csv EXEC extract_V1P1OneRosterCategories @type = 'bulk' EXEC extract_V1P1OneRosterCategories @type = 'delta'

classes.csv

 EXEC extract_V1P1OneRosterClasses @type = 'bulk' EXEC extract_V1P1OneRosterClasses @type = 'delta'
courses.csv EXEC extract_V1P1OneRosterCourses @type = 'bulk' EXEC extract_V1P1OneRosterCourses @type = 'delta'
demographics.csv EXEC extract_V1P1OneRosterDemographics @type = 'bulk' EXEC extract_V1P1OneRosterDemographics @type = 'delta'
enrollments.csv EXEC extract_V1P1OneRosterEnrollments @type = 'bulk' EXEC extract_V1P1OneRosterEnrollments @type = 'delta'
lineItems.csv EXEC extract_V1P1OneRosterLineItems @type = 'bulk' EXEC extract_V1P1OneRosterLineItems @type = 'delta'

orgs.csv

 EXEC extract_V1P1OneRosterOrgs @type = 'bulk' EXEC extract_V1P1OneRosterOrgs @type = 'delta'
results.csv EXEC extract_V1P1OneRosterResults @type = 'bulk' EXEC extract_V1P1OneRosterResults @type = 'delta'
users.csv EXEC extract_V1P1OneRosterUsers @type = 'bulk' EXEC extract_V1P1OneRosterUsers @type = 'delta'

Defined Bulk and Delta procedures for each of the supported CSV files. See step 11 in Setting Up The Files for further instructions.

Create Zip Tool Extract

PATH: System Administration > Data Utilities > Data Extract Utilities > Zip Tool

After successfully running the OneRoster CSV Extract, the Zip Tool can be used to export the files to a third party system. Unlike the Utility Set Up tool, the Zip Tool allows users to upload more than one file to export to a third party system. 

For more information on the data elements available in the Zip Tool go to the Data Extract Utility page.

The following procedure details steps on how to use the Zip Tool to export bulk or delta files to a third party system.

  1. Select New. 
  2. In the Detail Information editor enter the Extract Instance Name.
  3. Fill remaining fields with necessary information for defining an export for bulk or delta processing. See images below for examples of a bulk processing configuration and a delta processing configuration.
  4. Mark the 11 bulk export or 11 delta export checkboxes. 
  5. Click Save. 
  6. Select the appropriate Test Connection and/or Run Extract button. Extracts will display in a new window in the selected format. 
    • The Test Connection button checks the connection to the entered server by attempting to write a 1-byte file to the location setup. If it is successful, a pop-up displays indicating the connection is good. If it is not successful, the reason the connection failed is displayed.
    • The Run Extract button runs and generates the extract. It also sends and stores the file where needed (in case of an issue when it is an auto-run extract).

Data Extract Schedule lists the frequency, date, time, and day of the week the extract runs. If this needs to stop for any reason, mark the Stop Automated Extract checkbox.

Zip Tool, Bulk Processing Configuration


Zip Tool, Delta Processing Configuration

Related Articles

Was this helpful?
Thank you for your feedback!
User Icon

Thank you!

Copyright ©️ 2010-2023 Infinite Campus, Inc. All rights reserved. | Terms of Use | Privacy Policy | infinitecampus.com