1EdTech OneRoster®: CSV Tables
1EdTech Final Release
Version 1.1.1
Date Issued: | 22nd August, 2023 |
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 © 2022 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/speclicense.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.
Public contributions, comments and questions can be posted here: http://www.imsglobal.org/forums/ims-glc-public-forums-and-resources/learning-information-services-oneroster-public-forum.
© 2022 1EdTech Consortium, Inc.
All Rights Reserved.
Trademark information: http://www.imsglobal.org/copyright.html
The 1EdTech Logo and OneRoster are trademarks of the 1EdTech Consortium, Inc. in the United States and/or other countries.
Document Name: 1EdTech OneRoster® v1.1.1 CSV Tables
Revision: 22nd August, 2023
Table of Contents
1.3. Structure of this Document
2.3. Compatibility Between V1.0 and V1.1
2.4. Bulk/Delta Behavior Constraints
Appendix A - File Dependencies Matrix
1. Introduction
1.1. OneRoster Overview
The Learning Information Services (LIS) is a standard developed by 1EdTech [LIS, 13]. The LIS standard addresses the exchange of student data (about people, courses, enrollments and grades) between different educational systems. In order to address the needs of the K-12 Community, 1EdTech has created a derivation of the LIS Core Profile [LIS, 11] to widen the functionality around grade transfer for K-12, to narrow the LIS data model to make it more appropriate to K12, and provide formats for Comma Separated Value (CSV) files for those who wish to provide Roster information to learning tools.
This document contains the description of how the OneRoster data model is exchanged in a set of CSV files. This binding addresses the format and content of the CSV files: it does NOT define how the CSV files are exchanged between end systems.
1.2. Context
This document must be used with the accompanying detailed OneRoster v1.1 data model [OneRoster, 20a] definition which itself is an evolution of the original OneRoster specification [OneRoster, 15a]. The technical changes between the V1.1 and V1.0 CSV binding are:
a) A 'manifest' CSV file has been introduced;
b) A 'lineItems' CSV file has been introduced to exchange the line item features in the data model;
c) A 'results' CSV file has been introduced to exchange the result features in the data model;
d) A 'categories' CSV file has been introduced to exchange the category features in the data model;
e) A 'resources' CSV file has been introduced for the new resources featured in the data model;
f) A 'classResources' file has been introduced for identifying the resources assigned to classes;
g) A 'courseResources' file has been introduced for identifying the resources assigned to courses;
h) The location of the metadata/extension data model features have been fixed in the CSV files. Therefore, all of the explicitly defined metadata fields in OR 1.0 (in the 'orgs' and 'courses' CSV files) have been removed;
i) The enumeration of the 'status' field in the Base class has had the token 'inactive' removed and there has been clarification on how the status values are to be used;
j) The set of files must be exchanged as a zip file. The number of files contained in the package will vary depending on the data consistency requirements for the transfer;
k) The full data model description [OneRoster, 20a] should be reviewed for the set of attribute changes i.e. additions, name alterations, multiplicity alterations and data-type refinements.
There have also been a number of editorial refinements, clarifications and corrections made in the binding document. The other document changes are:
a) The 'Conformance Testing' has been moved to the' OneRoster 1.1 Conformance and Certification' document [OneRoster, 20c];
b) The 'Best Practices' section has been moved to the 'OneRoster 1.1 Best Practices and Implementation Guide' [OneRoster, 20b].
1.3. Structure of this Document
The structure of the rest of this document is:
2. CSV OVERVIEW |
An overview of the structure of the CSV binding for OneRoster; |
3. CSV FORMAT |
The detailed format for the data in the CSV files for OneRoster; |
APPENDIX A - THE DEPENDENCY MATRIX |
An explanation of the file dependencies when exchanging bulk processing based CSV files. |
1.4. References
1EdTech Learning Information Services v2.0 Learning System: Profiles Overview v1.0, M.Feldstein and C.Smythe, 1EdTech, December 2011. |
|
1EdTech Learning Information Services v2.0 Overview, C.Smythe, 1EdTech, July 2013. |
|
1EdTech Learning Tools Interoperability (LTI) v1.0, S.Vickers 1EdTech, May 2010. |
|
OneRoster Specification v1.0, P.Nicholls, 1EdTech, July 2015. |
|
OneRoster Conformance v1.0, L.Mattson, 1EdTech, July 2015. |
|
OneRoster Specification v1.1.1, P.Nicholls and C.Smythe, Version 1.1.1 Final Release 1.01, 1EdTech, December 2020. |
|
OneRoster v1.1 Best Practices and Implementation Guide, C.Smythe and P.Nicholls, Version 1.1 Final Release 2.0, 1EdTech, December 2020. |
|
OneRoster v1.1 Conformance and Certification, L.Mattson and C.Smythe, Version 1.1 Final Release 2.0, 1EdTech, December 2020. |
|
[RFC1951] |
DEFLATE Compressed Data Format Specification 1.3, P.Deutsch, RFC 1951, IETF, May 1996. |
[RFC3629] |
UTF-8: A Transformation Format of ISO 10646, F.Yergeau, RFC 3629, IETF, November 2003. |
[RFC4180] |
Common Format and MIME Type for Comma-Separated Values (CSV) Files, Y.Shafranovich, RFC 4180, IETF, October 2005. |
1.5. Nomenclature
API | Application Programming Interface |
BOM | Beginning of Message |
CEDS | Common Education Data Standards |
CSV | Comma Separated Values |
GUID | Globally Unique Identifier |
IETF | Internet Engineering Task Force |
LDAP | Lightweight Directory Access Protocol |
LIS | Learning Information Services |
LMS | Learning Management System |
LOR | Learning Object Repository |
LTI | Learning Tools Interoperability |
RFC | Request For Comment |
UTF | Unicode Transformation Format |
2. CSV Overview
2.1. Binding of the Data
Many school districts currently provide student information to tool providers and LMS/LOR vendors as '.csv' formatted files. For those districts that must continue to use CSV files to exchange roster information with vendors, the format defined in the Tables in Section 3 should be used: this format corresponds to the data model in the OneRoster [OneRoster, 20a] standard. Districts can choose to upload class rosters/gradebooks by preparing up to fourteen (14) files in CSV format outlined in this document. The rosters/gradebooks could be available for both import and export. A summary of the set of files is given in Table 2.1.
Table 2.1 CSV Files Overview.
CSV File Name |
Req |
Description |
manifest.csv |
Yes |
A control file. The manifest contains the version (set as 1.1) and the list of files that are supplied in this data set. |
academicSessions.csv |
No |
A data file. The 'academic sessions' data model content. |
categories.csv |
No |
A data file. The definition for a set of lineItems. |
classResources.csv |
No |
A data file. The set of resources assigned to classes. |
classes.csv |
No |
A data file. The 'classes' data model content. |
courseResources.csv |
No |
A data file. The set of resources assigned to courses. |
courses.csv |
No |
A data file. The 'courses' data model content. |
demographics.csv |
No |
A data file. The 'demographics' data model content. |
enrollments.csv |
No |
A data file. The 'enrollments' data model content. |
lineItems.csv |
No |
A data file. The set of lineItems. |
orgs.csv |
No |
A data file. The' orgs' data model content. |
results.csv |
No |
A data file. The set of results. |
resources.csv |
No |
A data file. The 'resources' data model content. |
users.csv |
No |
A data file. The 'users' data model content. |
Key:
- CSV File Name - the name of the CSV file;
- Req - denotes whether or not the CSV file MUST be supplied (Yes or No);
- Description - a statement of the content in the file;
- Shaded rows are for files added in the V1.1 release.
When data is exchanged there will be 1-14 CSV files. An exchange MUST include the 'manifest.csv' file. The case of when only the 'manifest.csv' is supplied is used to denote that no updates occurred. At most, 14 CSV files exchanged i.e. there MUST NOT be more than one of each of the data CSV files. When the set of files are for bulk processing they must be semantically consistent, i.e., every object referenced in a file must also be defined in either the same, or one of the other files in the zip package (see Appendix A for more details on this set of file dependencies).
2.2. Exchanging the CSV Files
For V1.1 the CSV files will be exchanged as a zip file. The encompassed files must be at the root level, i.e., they MUST NOT be contained within an enclosing directory. There is NO constraint on the name of the zip file but it must have a file extension of 'zip'. The compression must conform to RFC 1951 [RFC1951].
In many cases these files will contain Personally Identifiable Information (PII) or other sensitive data. It is recommended that the appropriate security mechanisms are used to protect these files when they are exchanged. These mechanisms could include:
- Password protection of the zip file;
- Use of tthe Secure File Transfer Protocol (FTP) or equivalent protocol.
2.3. Compatibility Between V1.0 and V1.1
A system that imports both V1.0 and V1.1 compliant data sets must differentiate using the following algorithm:
a) If a 'manifest.csv' file is present then the data set conforms to V1.1 (this must be assumed even if the rest of the file set conforms to OneRoster 1.0 definitions and the appropriate error messages must be produced but the subsequent processing is implementation dependent). The manifest file contains the list of CSV files that MUST also accompany the manifest file. If the set of supplied files differs from the list of files in the manifest then an import error should be reported and the system is permitted to process the data as defined by the implementation;
b) If a 'manifest.csv' file is NOT present then, under normal operations, the data set conforms to V1.0. Seven data files MUST be supplied, i.e., one file for each of the data models (there MUST NOT be any of the new CSV data files introduced in V1.1). If a file is missing then an error should be reported and the system is permitted to process the data as defined by the implementation. In the case where the 'manifest.csv' file is missing but the file set conforms to OneRoster 1.1 then the appropriate error message should be produced and the subsequent processing is implementation dependent.
2.4. Bulk/Delta Behavior Constraints
The existence and management of a record is controlled via the bulk and delta processing options. A record can be in four possible states:
• 'No Record' - the record has not been created or it did exist but the system has deleted the record and it cannot be recovered with the previously assigned sourcedId;
• 'Record[-]' - the record has been created and assigned a sourcedId but it has not yet been activated;
• 'Record[Active]' - the record status has been set to 'Active';
• 'Record[ToBeDeleted]' - the record status has been set of 'tobedeleted'.
For each state there are four possible types of transition events:
• 'B[-]' - a bulk transfer has occurred and an existing record (one with a sourcedId) does not appear in those files;
• 'B[S]' - a bulk transfer has occurred and the record occurs in the new file set;
• 'D[A]' - a delta transfer has occurred and the record status is set as 'active';
• 'D[D]' - a delta transfer has occurred and the record status is set as 'tobedeleted'.
The 'System Deletes the Record' transition is only possible when the record is in the state of 'Record[ToBeDeleted]'. For this transition the system deletes the record (the length of time between this deletion action and the record being set as 'tobedeleted' is implementation dependent).
The state diagram for the creation and management of a record in a service provider is shown in Figure 2.1.
Figure 2.1 - State diagram for the management of records using bulk/delta processing at the service provider.
A OneRoster Service Consumer could issue a 'HTTP GET' request for the record in each of these four states. When such a read request is made the request will result in the following payloads:
• 'No Record' - in the case of a single record request a 404 response will be returned;
• 'Record[-]' - the record will be returned with an empty 'status' field and an empty 'dateLastModified' field;
• 'Record[Active]' - the record will be returned with a 'status' field value of 'active' and the 'dateLastModified' field set to the time the record was last changed;
• 'Record[ToBeDeleted]' - the record will be returned with a 'status' field value of 'tobedeleted' and the 'dateLastModified' field set to the time the record was last changed.
3. CSV Format
The file format for each of the data files is a Comma Separated Values (CSV) format as specified in RFC 4180 [RFC 4180] with the extra restriction that carriage-returns are not permitted within a field. Fields containing commas and double-quotes must be enclosed in double-quotes. If double-quotes are used to enclose a field, then a double-quote appearing inside the field must be escaped by preceding it with another double-quote.
The CSV files must be UTF-8 encoded [RFC3629]. Importing processors must tolerate BOM (Byte Order Mark) prefixes and ignore them. In a UTF-8 encoded file with a BOM, the BOM will appear as the 3-byte sequence 'EF BB BF' at the beginning of the file. If present, the CSV header will begin at the 4th byte of the file; if not present, the CSV header will begin at the 1st byte of the file.
Other key points are:
• All header names and content values within the CSV file are case-sensitive. Incorrect case will result in conformance failure and should be logged as a detected error;
• The Header row for each file is required;
• The Header fields for a file must be supplied in the same order as defined in the tables below. If metadata extension fields are used, then they must be added to the right of the defined data fields in the specification, as the last set of columns. This ensures that the sequence of the defined data fields remains fixed irrespective of the presence or not of metadata fields;
• Header field names must be uniquely named within a file;
• Files that contain no data rows are NOT permitted;
• For ALL fields that are optional and which have a defined enumeration token set, then the value of NULL/blank (i.e. a blank value "") is permitted to denote that NO data is supplied;
• The support of ALL string-based data-types requires that a maximum length of at least 255 must be supported by implementations. If string lengths of greater than 255 are used then system may lose some part of the string without failing conformance.
When mapping from the data model [OneRoster, 17a] to the equivalent column headers the following special rules have been applied:
• When referencing an object in the data model (using the data-type GUIDRef) the name has been extended using SourcedId e.g. 'org' to 'orgSourcedId';
• When referring to a set of objects in the data model maps to a comma separated list, enclosed in quotation marks, under the corresponding column header (as per RFC 4180);
• The references to resources in the Class and Course data models have NOT been mapped.
CSV import/export is envisaged to work in one of two ways, bulk and delta:
• In BULK processing, the CSV files MUST NOT contain values in the "dateLastModified" and "status" fields. That is to say, for each row, there will be no data for either of these fields. 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 'tobedeleted' with the 'dateLastModified' value set to the time of the bulk files import processing. If said records subsequently appear in a future bulk file, then those records should be updated and made active. When a set of Bulk files are created they must be semantically complete i.e. every object referenced by another object MUST also be defined in the corresponding source CSV file and included with the manifest.csv file;
In DELTA processing, the incoming CSV file MUST contain values 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.
• Field Header - the name for the Column Header;
• Required - denotes if data must be present. The meanings for this field are:
- 'Yes' for fields which are always required
- 'Yes for Delta' for fields which must be populated for Delta processing, and which must be blank for Bulk processing
- 'No' for fields which do not require to have values;
• Format - the data-type for the data.
- 'GUID' denotes some form of globally unique identifier (this must be less than 256 characters). This is not restricted to the 128 bit UUID format
- 'GUID Reference' denotes the GUID of an object defined in some other CSV data file
- 'List of GUID Reference' denotes a comma-delimited list of GUID References of objects defined in another CSV data file
- 'ID' denotes an identifier defined outside of the OneRoster specification, e.g. identifiers generated by vendors for their resources in the Resources dataset
- 'String' denotes a sequence of characters that should follow the description. Generally this is aimed to be human-readable, e.g., "Science Lesson"
- "List of Strings" denotes a sequence of Strings. The individual strings within the list must not contain commas, and the list is a comma separated list, e.g., "1,2,3". If a List of Strings contains more than one element, then it must be double-quote encapsulated
- 'Enumeration' denotes a fixed set of values that will be referred to in the description. In the case of fields which are not required, an empty field denotes no value
- "Enumeration List" denotes a list of Enumerations. The list is comma separated, e.g., "teacher,student,aide".
- "Float" denotes a floating point number
- "DateTime" denotes a timestamp format. DateTimes MUST be expressed in W3C profile of ISO 8601 and MUST contain the UTC timezone and MUST have a resolution of milliseconds, e.g., "2012-04-23T18:25:43.511Z"
- "Date" denotes a date format. Dates MUST be expressed using ISO 8601 format (http://tools.ietf.org/html/rfc3339), more commonly formatted as "YYYY-MM-DD" e.g., "2002-04-23"
- "Year" denotes a date format of year only. Years MUST be expressed using ISO 8601 format (http://tools.ietf.org/html/rfc3339), more commonly formatted as "YYYY" e.g., "2002"
• Example/Description - an example of the expected data, and a brief statement of the nature of the content.
A number of important changes have been made from v1.0, namely:
• The 'dateLastModified' in v1.1 had been changed from the v1.0 the resolution 'YYYY-MM-DD' to 'YYYY-MM-DDTHH:MM:SS.sssZ'. For backwards compatibility, a v1.0 format of 'YYYY-MM-DD' should be transformed to 'YYYY-MM-DDT23:59:59.999Z' in a v1.1 context;
• In the 'status' field the enumeration value of 'inactive' has been removed. For compatibility with v1.0, a value of 'inactive' should be interpreted as 'tobedeleted';
• When delta-processing information is being exchanged and records are marked 'tobedeleted' then all fields that have been identified as mandatory (with the exception of the object 'sourcedId') should be considered optional.
NOTE: New data model features are denoted by 'yellow' shading whereas features that have been altered are denoted by 'blue' shading.
3.1. manifest.csv
The manifest file will consist of just two columns with the headers 'propertyName' and 'value'. Each row will contain a single property/value pair. The list of supported property/value pairs is described below. |
|||
Property Name |
Required |
Format |
Value Description |
manifest.version |
Yes |
String |
The version of the manifest. For an initial value this must be "1.0". |
oneroster.version |
Yes |
String |
The OneRoster version supported by this file set. This must be "1.1". |
file.academicSessions |
Yes |
Enumeration |
Each field is enumerated as: { "absent" | "bulk" | "delta" } with the values denoting: • absent - this CSV file is not supplied; • bulk - this CSV file contains only bulk data; • delta - this CSV file contains only delta data. These processing mode hints should be consistent with the data held within the accompanying CSV files but in cases of conflict the values in the data CSV files must take precedence. |
file.categories |
Yes |
Enumeration |
|
file.classes |
Yes |
Enumeration |
|
file.classResources |
Yes |
Enumeration |
|
file.courses |
Yes |
Enumeration |
|
file.courseResources |
Yes |
Enumeration |
|
file.demographics |
Yes |
Enumeration |
|
file.enrollments |
Yes |
Enumeration |
|
file.lineItems |
Yes |
Enumeration |
|
file.orgs |
Yes |
Enumeration |
|
file.resources |
Yes |
Enumeration |
|
file.results |
Yes |
Enumeration |
|
file.users |
Yes |
Enumeration |
|
source.systemName |
No |
String |
The name for the system producing the set of files. |
source.systemCode |
No |
String |
Identification code for the system producing the set of files. |
3.2. academicSessions.csv
This file represents the AcademicSessions dataset from the OneRoster specification.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
SourcedId of this academicSession. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
title |
Yes |
String |
Name or title of the academic session. |
type |
Yes |
Enumeration |
See section 4.13.7 [OneRoster, 20a] for the enumeration list. |
startDate |
Yes |
Date |
Inclusive start date for the academic session. |
endDate |
Yes |
Date |
Exclusive end date for the academic session. |
parentSourcedId |
No |
GUID Reference. |
SourcedId of the parent of this academic session. |
schoolYear |
Yes |
Year |
The school year for which the academic session contributes. This year should be that in which the school year ends. |
The dependencies of this file on other files when supporting bulk processing are:
1) Other academicSessions could be referenced in the SAME file through the 'parentSourcedId' attribute. Therefore, for semantic consistency there are NO dependencies on OTHER files.
3.3. categories.csv
This file represents the Line Item Categories from the OneRoster specification.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique ID for the category. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
title |
Yes |
String |
The title assigned to the set of lineItems to denote its nature e.g. homework, essays, etc. |
There are NO dependencies of this file on other files when supporting bulk processing.
3.4. classes.csv
This file represents the Class dataset from OneRoster specification.
Column Field Header |
Required |
Format |
Description |
Yes |
GUID |
Unique ID for the class. SourcedId is used in other files and must be unique across all classes. |
|
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
title |
Yes |
String |
Name of this class. |
grades |
No |
List of Strings |
Grade(s) for which the class is attended. The permitted vocabulary is from CEDS (Version 5) and the 'Entry Grade Level' element https://ceds.ed.gov/CEDSElementDetails.aspx?TermId=7100 |
courseSourcedId |
Yes |
GUID Reference. |
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 |
Enumeration |
See section 4.13.1 [OneRoster, 20a] for the enumeration list. |
location |
No |
String |
Human readable description of where the class is physically located. |
schoolSourcedId |
Yes |
GUID Reference. |
SourcedId of the Org that teaches this class of OrgType "school". |
termSourcedIds |
Yes |
List of GUID References. |
SourcedId of the terms (the academicSessions) in which the class is taught. |
subjects |
No |
List of Strings |
Subject name(s) in human readable form. If the 'subjectCodes' attribute is present then the subjects and subjectCodes lists must have the same length and have order significance The vocabulary is from SCED (School Codes for the Exchange of Data) (Version 4) for the "Course Title" field: http://nces.ed.gov/forum/SCED.asp If the value of the "Course Title" contains commas, then those commas must be removed. F example, the "Course Title" for "SCED Course Code" "03210" is "Science, Technology and Society". This must be represented as "Science Technology and Society". |
subjectCodes |
No |
List of Strings |
Subject codes(s) in machine readable form. If more than one subject code is needed, use double quotes, and separate with commas (per RFC 4180). If the 'subjects' attribute is present the two lists must have the same length and have order significance. For deployments in the USA this vocabulary SHOULD be a School Courses for the Exchange of Data (SCED) code: http://nces.ed.gov/forum/SCED.asp. |
periods |
No |
List of Strings |
The time slots in the day that the class will be given. If more than one period is needed, use double quotes, and separate with commas (per RFC 4180). Examples: 1; "1,3,5" |
The dependencies of this file on other files when supporting bulk processing are:
1) This requires a reference to the associated terms (academicSessions) using the 'termSourcedIds' attribute. This produces a dependency on the academicSessions.csv;
2) This requires a reference to the associated course (course) using the 'courseSourcedId' attribute. This produces a dependency on the courses.csv;
3) This requires a reference to the associated school (org) using the 'schoolSourcedId' attribute. This produces a dependency on the orgs.csv.
3.5. classResources.csv
This file represents the association between the classes.csv and the resources.csv files.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique ID for the class/resource association. SourcedId is used in other files and must be unique across all class resources. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
title |
No |
String |
Name of the related class. |
classSourcedId |
Yes |
GUID Reference. |
SourcedId of the reference Class. |
resourceSourcedId |
Yes |
GUID Reference. |
SourcedId of the Resource associated with the Class. |
The dependencies of this file on other files when supporting bulk processing are:
1) This requires a reference to the associated class (class) using the 'classSourcedId' attribute. This produces a dependency on the classes.csv. This dependency on the class leads to the corresponding dependencies on the academicSessions.csv, courses.csv and orgs.csv files;
2) This requires a reference to the associated resource (resource) using the 'resourceSourcedId' attribute. This produces a dependency on the resources.csv.
3.6. courseResources.csv
This file represents the association between the courses.csv and the resources.csv files.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique ID for the course/resource association. SourcedId is used in other files and must be unique across all course resources. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
title |
No |
String |
Name of the related course. |
courseSourcedId |
Yes |
GUID Reference. |
SourcedId of the reference Course. |
resourceSourcedId |
Yes |
GUID Reference. |
SourcedId of the Resource associated with the course. |
The dependencies of this file on other files when supporting bulk processing are:
1) This requires a reference to the associated course (course) using the 'courseSourcedId' attribute. This produces a dependency on the courses.csv. This dependency on the course leads to the corresponding dependencies on the academicSessions.csv and orgs.csv files;
2) This requires a reference to the associated resource (resource) using the 'resourceSourcedId' attribute. This produces a dependency on the resources.csv.
3.7. courses.csv
This file represents the Course dataset from OneRoster.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique ID for the course. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
schoolYearSourcedId |
No |
GUID Reference. |
SourcedId of an AcademicSession with type of "schoolYear". |
title |
Yes |
String |
Name of the course. |
courseCode |
No |
String |
Human readable course code. |
grades |
No |
List of Strings |
Grade(s) for which the class is attended. The permitted vocabulary is from CEDS (Version 5) for the 'Entry Grade Level' element https://ceds.ed.gov/CEDSElementDetails.aspx?TermId=7100 |
orgSourcedId |
Yes |
GUID Reference. |
SourcedId of an org to which this course belongs. |
subjects |
No |
List of Strings |
Subject name(s) in human readable form. If the 'subjectCodes' attribute is present then the subjects and subjectCodes lists must have the same length and have order significance The vocabulary is from SCED (School Codes for the Exchange of Data) (Version 4) for the "Course Title" field: http://nces.ed.gov/forum/SCED.asp If the value of the "Course Title" contains commas, then those commas must be removed. For example the "Course Title" for "SCED Course Code" "03210" is "Science, Technology and Society". This must be represented as "Science Technology and Society". |
subjectCodes |
No |
String |
Subject codes(s) in machine readable form. If the 'subjects' attribute is present then the subjects and subjectCodes lists must have the same length and have order significance. For deployments in the USA this vocabulary SHOULD be a School Courses for the Exchange of Data (SCED) code: http://nces.ed.gov/forum/SCED.asp. |
The dependencies of this file on other files when supporting bulk processing are:
1) This requires a reference to the associated school year (academicSession) using the 'schoolYearSourcedId' attribute. This produces a dependency on the academicSessions.csv;
2) This requires a reference to the associated org (org) using the 'orgSourcedId' attribute. This produces a dependency on the orgs.csv.
3.8. demographics.csv
This represents the Demographics dataset from OneRoster. Demographics are optional, so data providers do not necessarily need to provide this information. If they do, this is the format to use.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID Reference |
SourcedId of the User to which the demographics refer. Typically this will be a student. Each user can have only one demographics record. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
birthDate |
No |
Date |
The date of birth. |
sex |
No |
Enumeration |
See section 4.13.2 [OneRoster, 20a] for the enumeration list. |
americanIndianOrAlaskaNative |
No |
Enumeration |
Permitted values: { "true" | "false" }. |
asian |
No |
Enumeration |
Permitted values: { "true" | "false" }. |
blackOrAfricanAmerican |
No |
Enumeration |
Permitted values: { "true" | "false" }. |
nativeHawaiianOrOtherPacificIslander |
No |
Enumeration |
Permitted values: { "true" | "false" }. |
white |
No |
Enumeration |
Permitted values: { "true" | "false" }. |
demographicRaceTwoOrMoreRaces |
No |
Enumeration |
Permitted values: { "true" | "false" }. |
hispanicOrLatinoEthnicity |
No |
Enumeration |
Permitted values: { "true" | "false" }. |
countryOfBirthCode |
No |
String |
Country where the user was born. The permitted vocabulary is from CEDS (Version 5) for the "Country of Birth" element https://ceds.ed.gov/CEDSElementDetails.aspx?TermxTopicId=20002 |
stateOfBirthAbbreviation |
No |
String |
State where the user was born. The permitted vocabulary is from CEDS (Version 5) for the "State of Birth" element https://ceds.ed.gov/CEDSElementDetails.aspx?TermxTopicId=20837 |
cityOfBirth |
No |
String |
|
publicSchoolResidenceStatus |
No |
String |
An indication of the location of the users legal residence relative to (within or outside) the boundaries of the public school attended and its administrative unit. The permitted vocabulary is from CEDS (Version 5) for the "Public School Residence Status" element https://ceds.ed.gov/CEDSElementDetails.aspx?TermxTopicId=20863 |
The dependencies of this file on other files when supporting bulk processing are:
1) This requires a reference to the associated user (user) using the 'sourcedId' attribute. This produces a dependency on the users.csv. This creates a corresponding dependency on the orgs.csv file.
3.9. enrollments.csv
This represents the Enrollment dataset from OneRoster.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique identifier of this enrollment. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
classSourcedId |
Yes |
GUID Reference. |
SourcedId of the Class. |
schoolSourcedId |
Yes |
GUID Reference. |
SourcedId of an Org with type 'school'. |
userSourcedId |
Yes |
GUID Reference. |
SourcedId of the User. |
role |
Yes |
Enumeration |
See section 4.13.5 [OneRoster, 20a] for the enumeration list. The ONLY permitted values are: { administrator | proctor | student | teacher }. |
primary |
No |
Enumeration |
Permitted values: { "true" | "false" }. Applicable only to teachers. Only one teacher should be designated as the primary teacher for a class in the period defined by the begin/end dates. |
beginDate |
No |
Date |
The start date for the enrollment. This date must align with the associated academic session (term) identified in the class. |
endDate |
No |
Date |
The end date for the enrollment (exclusive). This date must align with the associated academic session (term) identified for the class. |
The dependencies of this file on other files when supporting bulk processing are:
1) This requires a reference to the associated class (class) using the 'classSourcedId' attribute. This produces a dependency on the classes.csv. This creates a corresponding dependency on the academicSessions.csv and courses.csv files. The courses dependency creates a further set of dependencies on the on the academicSessions.csv and orgs.csv files;
2) This requires a reference to the associated school (org) using the 'schoolSourcedId' attribute. This produces a dependency on the orgs.csv.
3) This requires a reference to the associated user (user) using the 'userSourcedId' attribute. This produces a dependency on the users.csv. This creates a corresponding dependency on the orgs.csv file.
3.10. lineItems.csv
This represents the LineItem dataset from OneRoster.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique ID for the lineItem. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
title |
Yes |
String |
The title assigned to the lineItem. |
description |
No |
String |
Short description of the role of the lineItem. |
assignDate |
Yes |
Date |
Date the associated activity was assigned. |
dueDate |
Yes |
Date |
Date the associated activity is due to be completed. |
classSourcedId |
Yes |
GUID Reference. |
SourcedId of the Class. |
categorySourcedId |
Yes |
GUID Reference. |
SourcedId of the Category. |
gradingPeriodSourcedId |
Yes |
GUID Reference. |
SourcedId of the academicSession that defines the grading period. |
resultValueMin |
Yes |
Float |
The minimum value permitted for the score (inclusive) e.g. 0.0. |
resultValueMax |
Yes |
Float |
The maximum value permitted for the score (inclusive) e.g. 100.0 |
The dependencies of this file on other files when supporting bulk processing are:
1) This requires a reference to the associated class (class) using the 'classSourcedId' attribute. This produces a dependency on the classes.csv. This creates a corresponding dependency on the academicSessions.csv and courses.csv files. The courses dependency creates a further set of dependencies on the on the academicSessions.csv and orgs.csv files;
2) This requires a reference to the associated category (category) using the 'categorySourcedId' attribute. This produces a dependency on the categories.csv.
3) This requires a reference to the associated grading period (academicSession) using the 'gradingPeriodSourcedId' attribute. This creates a dependency on the academicSessions.csv file.
3.11. orgs.csv
This represents the Org dataset from OneRoster.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique id for the organization. SourcedId is used in other files and must be unique across all organizations. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
name |
Yes |
String |
Name of the organization. |
type |
Yes |
Enumeration |
See section 4.13.4 [OneRoster, 20a] for the enumeration list. |
identifier |
No |
String |
NCES ID National Center for Education Statistics) for the school/district. |
parentSourcedId |
No |
GUID Reference. |
SourcedId of an Org representing the Parent organization. |
The dependencies of this file on other files when supporting bulk processing are:
1) Other orgs could be referenced in the SAME file through the 'parentSourcedId' attribute. Therefore, for semantic consistency there are NO dependencies on OTHER files.
3.12. resources.csv
This represents the Resources dataset for OneRoster.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique ID of this resource. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
vendorResourceId |
Yes |
ID |
Unique ID of this resource as allocated by the vendor. It is unique in the context of resource identifiers allocated by the vendor. |
title |
No |
String |
Name of this resource. |
roles |
No |
Enumeration List |
See section 4.13.5 [OneRoster, 20a] for the enumeration list. |
importance |
No |
String |
See section 4.13.3 [OneRoster, 20a] for the enumeration list. |
vendorId |
No |
ID |
Identifier of the vendor responsible for this resource. This unique ID will be assigned by 1EdTech during the OneRoster conformance process. |
applicationId |
No |
ID |
Identifier of the application associated with this resource. This identifier is assigned by the creator/vendor of the resource. |
There are NO dependencies of this file on other files when supporting bulk processing.
3.13. results.csv
This represents the Results dataset from OneRoster.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique ID for the result. SourcedId is used in other files and must be unique across all results. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
lineItemSourcedId |
Yes |
GUID Reference. |
Unique identifier of the lineItem. |
studentSourcedId |
Yes |
GUID Reference. |
Unique identifier of the student (user). References a record that is/was created in the users.csv file with type of "student". |
scoreStatus |
Yes |
Enumeration |
See section 4.13.6 [OneRoster, 20a] for the enumeration list. |
score |
Yes |
Float |
A floating point number (the range should be consistent with that defined in the associated lineItem resultValueMin and resultValueMax fields). |
scoreDate |
Yes |
Date |
The date the result was submitted. |
comment |
No |
String |
Human readable comment about the result. |
The dependencies of this file on other files when supporting bulk processing are:
1) This requires a reference to the associated lineItem using the 'lineItemSourcedId' attribute. This produces a dependency on the lineItems.csv. This creates a corresponding dependency on the academicSessions.csv, classes.csv, courses.csv, orgs.csv and categories.csv files
2) This requires a reference to the associated student (user) using the 'studentSourcedId' attribute. This produces a dependency on the users.csv. This creates a corresponding dependency on the orgs.csv file.
3.14. users.csv
This represents the Users dataset from OneRoster.
Column Field Header |
Required |
Format |
Description |
sourcedId |
Yes |
GUID |
Unique ID for the user. SourcedId is used in other files and must be unique across all users. |
status |
Yes for Delta |
Enumeration |
See section 4.13.8 [OneRoster, 20a] for the enumeration list. This MUST NOT be used for the Bulk mode. |
dateLastModified |
Yes for Delta |
DateTime |
The date that this record was last modified. This MUST NOT be used for the Bulk mode. |
enabledUser |
Yes |
Enumeration |
Permitted values: { "true" | "false" }. 'false' denotes that the user is an active record but system access is curtailed according to the local administration rules. |
orgSourcedIds |
Yes |
List of GUID References. |
SourcedIds of the Orgs to which this user belongs. (Note in most in cases, it is expected that users will belong to a single school). |
role |
Yes |
Enumeration |
See section 4.13.5 [OneRoster, 20a] for the full enumeration list. |
username |
Yes |
String |
User name. |
userIds |
No |
List of Strings |
External machine-readable ID (e.g. LDAP id, LTI id) for this user. The ID must be accompanied by a type to indicate the nature of the Identifier. The Type and ID values are enclosed in '{}' with a colon used to separate the values. If more than one userId is needed, use double quotes, and separate with commas (per RFC 4180). Examples: {LDAP:Id} "{LDAP:Id},{LTI:Id},{Fed:Id}" |
givenName |
Yes |
String |
User's first name. |
familyName |
Yes |
String |
User's surname. |
middleName |
No |
String |
User's middle name (s). If more than one then they are separated by a space. |
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. |
agentSourcedIds |
No |
List of GUID References |
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. |
grades |
No |
String |
Grade(s) for which a user with role 'student' is enrolled. The permitted vocabulary is from CEDS (Version 5) for the 'Entry Grade Level' element https://ceds.ed.gov/CEDSElementDetails.aspx?TermId=7100. |
password |
No |
String |
The password for the user. This may or may not be an encrypted string. If encrypted the systems processing must be aware of the encryption method. |
The dependencies of this file on other files when supporting bulk processing are:
1) This requires a reference to the associated orgs (orgs) using the 'orgSourcedIds' attribute. This produces a dependency on the orgs.csv;
2) There may be reference to agents (users) in the SAME file through the 'agentsSourcedIds' attribute but this creates no new file dependencies.
Appendix A - File Dependencies Matrix
Table A1 is the dependency matrix for the CSV files. This shows the set of CSV files that MUST be supplied for BULK exchange; this dependency is created because the set of supplied CSV files must be semantically complete and the data model defines certain fields must be present within a CSV file. To read the matrix select the CSV file of interest in the left hand column. The entries along that row indicate if some other CSV file is required (R) or may be required (O) if an associated optional field has been included in the source CSV file.
Color Key for Rows
• Green - denotes a source file that has NO dependencies on the other CSV files (the manifest will include a single data file, i.e., either "categories.csv" or "resources.csv");
• Yellow - denotes a source file that has a dependency only on itself i.e. another row in the same CSV file (the manifest will include a single data file, i.e., of either "academicSessions.csv" or "orgs.csv");
• Orange - denotes a source file that has a dependency on some other file that itself is self contained, i.e., a CSV file that is color-coded Green or Yellow. "courses.csv" and "users.csv' have dependencies on the Yellow and/or Green coded files;
• Pink - denotes a source file that a dependency on some other file that itself has other dependencies. Pink coded files are "classes.csv", "classResources.csv", "courseResources.csv" and "demographics.csv";
• Red - denotes a source file that as a further depth of dependency when compared to the files that are color coded Pink. Red coded files are "enrollments.csv", "lineItems.csv" and "results.csv".
Key Conclusions
• No CSV file is dependent on the "demographics.csv", "enrollments.csv" and "results.csv" files;
• The Gradebooks-oriented CSV file-set ("categories.csv", "lineItems.csv" and "results.csv" files) is NOT independent of the other CSV files;
• The Rostering-oriented CSV file-set must consist of "academicsessions.csv", "classes.csv", "courses.csv", "enrollments.csv", "orgs.csv" and "users.csv". Note that for OneRoster 1.0, CSV conformance also requires the 'demographics.csv' file to be supplied.
NOTE: An implementation may impose further constraints on the set of files required for semantic consistency, i.e., more files may need to be supplied than the strict minimum.
Table A1 - CSV file dependency matrix for 'bulk' processing.
|
AcademicSessions |
Categories |
Classes |
Classresources |
Courseresources |
Courses |
Demographics |
Enrollments |
LineItems |
Orgs |
Resources |
Results |
Users |
academicSessions |
O(P) |
|
|
|
|
|
|
|
|
|
|
|
|
categories |
|
|
|
|
|
|
|
|
|
|
|
|
|
classes [C] |
R(T), |
|
|
|
|
R |
|
|
|
R(S), |
|
|
|
classResources |
R[C] |
|
R |
|
|
R[C] |
|
|
|
R[C] |
R |
|
|
courseResources |
O[S] |
|
|
|
|
R |
|
|
|
R[S] |
R |
|
|
courses [S] |
O(SY) |
|
|
|
|
|
|
|
|
R |
|
|
|
demographics |
|
|
|
|
|
|
|
|
|
R[U] |
|
|
R |
enrollments |
R[C], |
|
R |
|
|
R[C] |
|
|
|
R(S), |
|
|
R |
lineItems [L] |
R(GP), |
R |
R |
|
|
R[C] |
|
|
|
R[C] |
|
|
|
orgs |
|
|
|
|
|
|
|
|
|
O(P) |
|
|
|
resources |
|
|
|
|
|
|
|
|
|
|
|
|
|
results |
R[L], |
R[L] |
R[L] |
|
|
R[L] |
|
|
R |
R[L] |
|
|
R(S) |
Users [U] |
|
|
|
|
|
|
|
|
|
R |
|
|
O(A) |
About This Document
Title: 1EdTech OneRoster®: CSV Tables
Editor: Phil Nicholls (Oracle) and Colin Smythe (1EdTech)
Sspecification Version: 1.1.1
Document Version: 1.1.1
Version Date: 22nd August 2022
Status: Final Release
Summary: This document contains the information regarding the CSV file structure for OneRoster comprising the 1EdTech OneRoster V1.1 specification. This document contains the description of how the OneRoster data should be stored within a set of CSV files. It does not address how the CSV files are exchanged between systems.
Purpose: This document is made available for public implementation.
Document Location: http://www.imsglobal.org/lis
List of Contributors
The following individuals contributed to the development of this document:
Revision History
Version No. |
Release Date |
Comments |
V1.0 Final |
3rd June 2015 |
First formal release to the 1EdTech OneRoster Final document. |
V1.1 Final |
17th April 2017 |
Second formal release to the 1EdTech OneRoster Final document. |
V1.1.1 Final |
31st December 2020 |
Minor revision of this specification in response to the identification of a number of clarifications. These clarifications are:
|
V1.1.1 Final / Document 1.1 | 1st November 2021 | Minor revision of this documentation to include in Section 2.2 a recommendation to exchange of the CSV files using a secure mechanism. This inclusion completes an issue raised in the 1EdTech Security Audit 2021 (recommendation 2020-SPEC-25). |
V1.1.1 Final / Document 1.1.1 | 29th April, 2022 | Clarification that the permitted set of roles for an enrollment is limited to: { administrator | proctor | student | teacher }. |
V1.1.1 Final / Document 2 | 22nd August, 2023 | Update of data type for the ‘grades’ field on the users.csv file to be a list of strings as per intent. |
1EdTech Consortium, Inc. ("1EdTech") is publishing the information contained in this document ("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.1edtech.org.
Please refer to Document Name: 1EdTech OneRoster® 1.1.1 CSV Tables
Date: 22nd August, 2023
This page contains trademarks of the 1EdTech Consortium including the 1EdTech Logos, Learning Tools Interoperability® (LTI®), Accessible Portable Item Protocol® (APIP®), Question and Test Interoperability® (QTI®), Common Cartridge® (CC®), AccessForAll™, OneRoster®, Caliper Analytics™ and SensorAPI™. For more information on the 1EdTech trademark usage policy see trademark policy page
© 2001-2022 1EdTech Consortium Inc. All Rights Reserved. Privacy Policy