1EdTech OneRoster™: CSV Tables
Version 1.0 Final
Date Issued: 3 June 2015
Latest version: http://www.imsglobal.org/lis/
IPR and Distribution Notices
Recipients of this document are requested to submit, with their comments, notification of any relevant patent claims or other intellectual property rights of which they may be aware that might be infringed by any implementation of the specification set forth in this document, and to provide supporting documentation.
1EdTech takes no position regarding the validity or scope of any intellectual property or other rights that might be claimed to pertain to the implementation or use of the technology described in this document or the extent to which any license under such rights might or might not be available; neither does it represent that it has made any effort to identify any such rights. Information on 1EdTech’s procedures with respect to rights in 1EdTech specifications can be found at the 1EdTech Intellectual Property Rights web page: http://www.imsglobal.org/ipr/imsipr_policyFinal.pdf.
Copyright © 2015 1EdTech Consortium. All Rights Reserved.
Use of this specification to develop products or services is governed by the license with 1EdTech found on the 1EdTech website: http://www.imsglobal.org/license.html.
Permission is granted to all parties to use excerpts from this document as needed in producing requests for proposals.
The limited permissions granted above are perpetual and will not be revoked by 1EdTech or its successors or assigns.
THIS SPECIFICATION IS BEING OFFERED WITHOUT ANY WARRANTY WHATSOEVER, AND IN PARTICULAR, ANY WARRANTY OF NONINFRINGEMENT IS EXPRESSLY DISCLAIMED. ANY USE OF THIS SPECIFICATION SHALL BE MADE ENTIRELY AT THE IMPLEMENTER'S OWN RISK, AND NEITHER THE CONSORTIUM, NOR ANY OF ITS MEMBERS OR SUBMITTERS, SHALL HAVE ANY LIABILITY WHATSOEVER TO ANY IMPLEMENTER OR THIRD PARTY FOR ANY DAMAGES OF ANY NATURE WHATSOEVER, DIRECTLY OR INDIRECTLY, ARISING FROM THE USE OF THIS SPECIFICATION.
For comments and feedback, join the discussion at: http://www.imsglobal.org/community/forum/categories.cfm?catid=59
Trademark Information: http://www.imsglobal.org/copyright.html
Document Name: 1EdTech OneRoster: CSV Tables
Revision: 3 June 2015
Table of Contents
1. Introduction
1.1. OneRoster Overview
Learning Information Services (LIS) is a standard developed by 1EdTech. The standard addresses the exchange of student data (about people, courses, enrollments and grades) between different educational systems.
In order to address the needs to the K12 Community, 1EdTech has undertaken a profile of the LIS Core profile to widen the functionality around grade transfer for K12, narrow the LIS data model to make it more appropriate to K12, and provide formats for .csv files for those who wish to provide Roster information to learning tools. In addition, the OneRoster specification will be made available via a REST or a SOAP binding.
The OneRoster Specification [OneRoster, 15] will contain multiple parts, including a Best Practice and Implementation Guide and Open Roster Information. This document is the v1.0 Final release and will change based on further implementations of the specification. Conformance for OneRoster will be available shortly.
1.2. References
[LTI, 10] 1EdTech Learning Tools Interoperability (LTI) v1.0, 1EdTech, May 2010.
[LIS, 13] 1EdTech Learning Information Services 2.0.1, 1EdTech, July 2013.
[OneRoster, 15] 1EdTech One Roster Specification v1.0, 1EdTech, June 2015.
2. CSV Overview
Many districts currently provide student information to tool providers and LMS/LOR vendors as .csv formatted files.
For those districts who must continue to use .csv files to exchange roster information with vendors, below is an outline of the format for the roster data which corresponds to the OneRoster standard.
Districts can choose to upload class rosters by preparing seven (7) files in csv format outlined in this document. The rosters could be available for both import and export.
1. orgs.csv
2. users.csv
3. courses.csv
4. classes.csv
5. enrollments.csv
6. academicSessions.csv
7. demographics.csv
3. CSV Format
The file format MUST be comma separated values format (CSV) for the OneRoster profile. Each field will be separated by commas and line breaks between each row. Double quotes MUST be used with a field that contains a comma.
● All files are required
● The header row is required.
● Some fields are required.
● The primary id is marked in red below, this must be unique per file.
ALL Header fields MUST be supplied in EXACTLY the same order as in the tables below. Optional fields with no data MUST simply be empty in the CSV. Header fields MUST be named the same as per the field header in the tables below. All filenames and header fields are case sensitive.
CSV import is envisaged to work in one of two ways, bulk and delta.
In BULK processing, the incoming csv file MUST NOT contain DATA in the “dateLastModified” and “status” fields. That is to say, for each row, there will be no data for either of these fields, just an empty space between commas.
Importing Systems MUST view this incoming data as the reference version of all data. That is to say, if records that were previously imported do not appear in this bulk csv file, then the importing system should internally mark those records as inactive. If said records subsequently appear in a future bulk file, then those records should be updated and made active.
In DELTA processing, the incoming csv file MUST contain DATA in the “dateLastModified” and “status” fields. That is to say, each row in the csv file should include data describing that (the row) data was last modified and its status.
Importing systems must view this incoming delta file as changes to the reference data, and should make the appropriate changes, deletions and insertions of the new data.
Empty DELTA files can either be rejected, or processed such that no changes are made to anything.
It is recommended that all (seven) files are supplied for processing, even empty ones. Files which contain no data MUST contain the header fields.
Date Format in the CSV files DIFFERS from that of the RESTful API. Please use the format YYYY-MM-DD for this property.
3.1. orgs.csv
|
field header |
required |
format |
description |
|
sourcedId |
Yes |
string |
unique id for the organization. SourcedId is used in other files and must be unique across all organizations. (For example –schools in your district). |
|
status |
No |
String |
“active” | “inactive” | “tobedeleted”. A tobedeleted object may be deleted in receiving systems if it already exists |
|
dateLastModified |
No |
Date |
The date that this record was last modified. (format is YYYY-MM-DD) |
|
name |
Yes |
string |
name of the organization |
|
type |
Yes |
String |
“school” | “local” | “state” | “national” |
|
identifier |
No |
string |
NCES ID National Center for Education Statistics) for the school / district |
|
metadata.classification |
No |
String |
“charter” | “private” | “public” |
|
metadata.gender |
No |
String |
“female” | “male” | “mixed” |
|
metadata.boarding |
No |
Boolean |
True if school is boarding school |
|
parentSourcedId |
No |
string |
SourcedId of the Parent organization |
3.2. users.csv
|
field header |
required |
format |
description |
|
sourcedId |
Yes |
String |
Unique ID for the user. SourcedId is used in other files and must be unique across all users, |
|
status |
No |
String |
“active” | “inactive” | “tobedeleted”. A tobedeleted object may be deleted in receiving systems if it already exists |
|
dateLastModified |
No |
Date |
The date that this record was last modified. (FORMAT IS YYYY-MM-DD) |
|
orgSourcedIds |
Yes |
String |
SourcedIds of the Organizations to which this user belongs. If multiple Ids are required then use double quotes and separate with commas. (Note in most in cases, it is expected that users will belong to a single school). |
|
role |
Yes |
String |
“teacher”, “student”, “parent”, “guardian”, “relative”, “aide”, “administrator” |
|
username |
Yes |
String |
Username |
|
userId |
No |
String |
external machine readable id (e.g. LDAP id, LTI id) for this user, to be used if the sourcedId should not be used. |
|
givenName |
Yes |
String |
User’s first name |
|
familyName |
Yes |
String |
User’s surname |
|
identifier |
No |
String |
Identifier for the user with a human readable meaning |
|
|
No |
String |
Email address for the user |
|
sms |
No |
String |
SMS address for the user |
|
phone |
No |
String |
Phone number for the user |
|
agents |
No |
String |
SourcedIds of the users to which this user has a relationship. If multiple Ids are required then use double quotes and separate with commas. Note: In most cases this will be for indicating parental relationships. |
3.3. courses.csv
|
field header |
required |
format |
description |
|
sourcedId |
Yes |
String |
Unique ID for the course. SourcedId is used in other files and must be unique across all courses. |
|
status |
No |
String |
“active” | “inactive” | “tobedeleted”. A tobedeleted object may be deleted in receiving systems if it already exists |
|
dateLastModified |
No |
Date |
The date that this record was last modified. (FORMAT IS YYYY-MM-DD) |
|
schoolYearId |
No |
String |
School year in which course runs. This the sourcedId of an academic Session (see below) |
|
metadata.duration |
No |
String |
Description of how long the course runs for (e.g. two weeks, one semester). |
|
title |
Yes |
String |
Name of the course |
|
courseCode |
No |
String |
Human readable course code |
|
grade |
No |
String |
Grade (i.e. 9 or range 9-12) |
|
orgSourcedId |
No |
String |
SourcedId of the org to which this course belongs. This may be a district level org Id. |
|
subjects |
No |
String |
Subject name(s). If more than one subject is needed, use double quotes, and separate with commas (per RFC 4180): |
3.4. classes.csv
|
field header |
required |
format |
description |
|
Yes |
String |
Unique ID for the class. SourcedId is used in other files and must be unique across all classes. |
|
|
status |
No |
String |
“active” | “inactive” | “tobedeleted”. A tobedeleted object may be deleted in receiving systems if it already exists |
|
dateLastModified |
No |
Date |
The date that this record was last modified. (FORMAT IS YYYY-MM-DD) |
|
title |
Yes |
String |
Name of this class |
|
grade |
No |
String |
Grade (i.e. 9 or range 9-12) |
|
courseSourcedId |
No |
String |
SourcedId of the course of which this class is an instance |
|
classCode |
No |
String |
Human readable code used to help identify this class |
|
classType |
Yes |
String |
“homeroom”, “scheduled” |
|
location |
No |
String |
Human readable description of where the class is physically located |
|
schoolSourcedId |
Yes |
String |
SourcedId of the organization which teaches this class |
|
termSourcedId |
Yes |
String |
SourcedId of the academicSessions(s) in which the class is taught. If more than one term is needed, use double quotes and delimit with commas, (per RFC 4180) Examples: “1,2” 1 “1,4,8” |
|
subjects |
No |
String |
Subject name(s). If more than one subject is needed, use double quotes, and delimit with commas (per RFC 4180): |
3.5. enrollments.csv
|
field header |
required |
format |
description |
|
sourcedId |
Yes |
String |
Id of this enrollment |
|
classSourcedId |
Yes |
String |
Id of the class |
|
schoolSourcedId |
Yes |
String |
Id of the school |
|
userSourcedId |
Yes |
String |
Id of the user (teacher or student) |
|
role |
Yes |
String |
“student” | “teacher” | “parent” | “guardian” | “relative” | “aide”, “administrator” |
|
status |
No |
String |
“active” | “inactive” | “tobedeleted”. A tobedeleted object may be deleted in receiving systems if it already exists |
|
dateLastModified |
No |
Date |
The date that this record was last modified. (FORMAT IS YYYY-MM-DD) |
|
primary |
No |
Boolean |
MUST only be set to true for ONE teacher for a class. |
3.6. academicSessions.csv
|
field header |
required |
format |
description |
|
sourcedId |
Yes |
String |
SourcedId of this academicSession. AcademicSessions are used in other files and need to be unique across all academicSessions. |
|
status |
No |
String |
“active” | “inactive” | “tobedeleted”. A tobedeleted object may be deleted in receiving systems if it already exists |
|
dateLastModified |
No |
Date |
The date that this record was last modified. (FORMAT IS YYYY-MM-DD) |
|
title |
Yes |
String |
Name or title of the grading period |
|
type |
Yes |
String |
“term”, “gradingPeriod”, “schoolYear”, “semester” |
|
startDate |
Yes |
String |
Start date |
|
endDate |
Yes |
String |
End date |
|
parentSourcedId |
No |
String |
Sourced id of parent of this academic session |
3.7. demographics.csv
Demographics are optional, so data providers do not necessarily need to provide this information. If they do, this is the format to use. (Note if demographics is NOT being provided, an empty file (with he headers) needs to be sent).
|
field header |
required |
format |
description |
|
userSourcedId |
Yes |
String |
SourcedId of the user to which the demographcis refer. Typically this will be a student. |
|
status |
No |
String |
“active” | “inactive” | “tobedeleted”. A tobedeleted object may be deleted in receiving systems if it already exists |
|
dateLastModified |
No |
Date |
The date that this record was last modified. (FORMAT IS YYYY-MM-DD) |
|
birthdate |
Yes |
Date |
Student’s date of birth |
|
sex |
Yes |
String |
“Female” | “Male” |
|
americanIndianOrAlaskaNative |
Yes |
Boolean |
|
|
asian |
Yes |
Boolean |
|
|
blackOrAfricanAmerican |
Yes |
Boolean |
|
|
nativeHawaiianOrOtherPacificIslander |
Yes |
Boolean |
|
|
white |
Yes |
Boolean |
|
|
demographicRaceTwoOrMoreRaces |
Yes |
Boolean |
|
|
hispanicOrLatinoEthnicity |
Yes |
Boolean |
|
|
countryOfBirthCode |
Yes |
String |
Vocabulary – https://ceds.ed.gov/CEDSElementDetails.aspx?TermxTopicId=20002 |
|
stateOfBirthAbbreviation |
No* |
String |
Vocabulary – https://ceds.ed.gov/CEDSElementDetails.aspx?TermxTopicId=20837 NOTE: For US and US territories, this field should be considered mandatory. For outside of the US, this field is optional. |
|
cityOfBirth |
Yes |
String |
|
|
publicSchoolResidenceStatus |
Yes |
String |
Vocabulary - https://ceds.ed.gov/CEDSElementDetails.aspx?TermxTopicId=20863 |
4. Conformance Testing
1EdTech will offer conformance testing for the .csv files. (TBA)
About This Document
Title: 1EdTech OneRoster: CSV Tables
Editor: Phil Nicholls (1EdTech)
Version: 1.0
Version Date: 3 June 2015
Status: Final
Summary: This document contains the information regarding the .csv file structure for OneRoster comprising the 1EdTech OneRoster specification.
Purpose: This document is made available for public comment and feedback.
Document Location: http://www.imsglobal.org/lis
List of Contributors
The following individuals contributed to the development of this document:
Name Organization
David Gappa Safari Montage
Tom Ingram Escambia County School District
Mike Kaastra Desire2Learn
Andrew Kuritzky HMH
Lisa Mattson 1EdTech
David Mayes Gwinnett County Schools
Andy Miller Learning.com
Phil Nicholls 1EdTech (UK)
Padraig O’hiceadha HMH
Upendra Penegalapati Pearson
George Perreault Orange County Public Schools
James Perreault FLVS
Patrick Porter Houston ISD
Wendy Riedy Sungard K12
Kurt Rompot Pearson
Aditya Subramaniam Schoology
Mark Walls Gwinnett County Schools
Stanley Watts Classlink
Mike Zackerson Instructure
Revision History
|
Version No. |
Release Date |
Comments |
|
v1.0 Public Draft |
12 February 2015 |
The first release of the OneRoster Specification. |
|
v1.0 Final |
3 June 2015 |
Updated following member comment and implementation experience. |
1EdTech Consortium, Inc. ("1EdTech") is publishing the information contained in this 1EdTech OneRoster ("Specification") for purposes of scientific, experimental, and scholarly collaboration only.
1EdTech makes no warranty or representation regarding the accuracy or completeness of the Specification.
This material is provided on an "As Is" and "As Available" basis.
The Specification is at all times subject to change and revision without notice.
It is your sole responsibility to evaluate the usefulness, accuracy, and completeness of the Specification as it relates to you.
1EdTech would appreciate receiving your comments and suggestions.
Please contact 1EdTech through our website at http://www.imsglobal.org
Please refer to Document Name: 1EdTech OneRoster: CSV Tables
Date: 3 June 2015
