1EdTech Final Release

1EdTech Logo

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.

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. Introduction

1.1. OneRoster Overview

1.2. Context

1.3. Structure of this Document

1.4. References

1.5. Nomenclature

2. CSV Overview

2.1. Binding of the Data 5

2.2. Exchanging the CSV Files

2.3. Compatibility Between V1.0 and V1.1

2.4. Bulk/Delta Behavior Constraints

3. CSV Format

3.1. manifest.csv

3.2. academicSessions.csv

3.3. categories.csv

3.4. classes.csv

3.5. classResources.csv

3.6. courseResources.csv

3.7. courses.csv

3.8. demographics.csv

3.9. enrollments.csv

3.10. lineItems.csv

3.11. orgs.csv

3.12. resources.csv

3.13. results.csv

3.14. users.csv

Appendix A - File Dependencies Matrix

About This Document

List of Contributors

Revision History

 

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

[LIS, 11]

1EdTech Learning Information Services v2.0 Learning System: Profiles Overview v1.0, M.Feldstein and C.Smythe, 1EdTech, December 2011.

[LIS, 13]

1EdTech Learning Information Services v2.0 Overview, C.Smythe, 1EdTech, July 2013.

[LTI, 10]

1EdTech Learning Tools Interoperability (LTI) v1.0, S.Vickers 1EdTech, May 2010.

[OneRoster, 15a]

OneRoster Specification v1.0, P.Nicholls, 1EdTech, July 2015.

[OneRoster, 15b]

OneRoster Conformance v1.0, L.Mattson, 1EdTech, July 2015.

[OneRoster, 20a]

OneRoster Specification v1.1.1, P.Nicholls and C.Smythe, Version 1.1.1 Final Release 1.01, 1EdTech, December 2020.

[OneRoster, 20b]

OneRoster v1.1 Best Practices and Implementation Guide, C.Smythe and P.Nicholls, Version 1.1 Final Release 2.0, 1EdTech, December 2020.

[OneRoster, 20c]

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.
A new file added in V1.1.

academicSessions.csv

No

A data file. The 'academic sessions' data model content.
Data model revised in v1.1.

categories.csv

No

A data file. The definition for a set of lineItems.
A new file added in V1.1.

classResources.csv

No

A data file. The set of resources assigned to classes.
A new file added in V1.1.

classes.csv

No

A data file. The 'classes' data model content.
Data model revised in v1.1.

courseResources.csv

No

A data file. The set of resources assigned to courses.
A new file added in V1.1.

courses.csv

No

A data file. The 'courses' data model content.
Data model revised in v1.1.

demographics.csv

No

A data file. The 'demographics' data model content.
Data model revised in v1.1.

enrollments.csv

No

A data file. The 'enrollments' data model content.
Data model revised in v1.1.

lineItems.csv

No

A data file. The set of lineItems.
A new file added in V1.1.

orgs.csv

No

A data file. The' orgs' data model content.

results.csv

No

A data file. The set of results.
A new file added in V1.1.

resources.csv

No

A data file. The 'resources' data model content.
A new file added in V1.1.

users.csv

No

A data file. The 'users' data model content.
Data model revised in v1.1.

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
(One per row)

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.
(Format is: YYYY).

 

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

sourcedId

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.

email

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),
O[S]

 

 

 

 

R

 

 

 

R(S),
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],
O(SY)

 

R

 

 

R[C]

 

 

 

R(S),
R(U)

 

 

R

lineItems [L]

R(GP),
R[C]

R

R

 

 

R[C]

 

 

 

R[C]

 

 

 

orgs
 

 

 

 

 

 

 

 

 

 

O(P)

 

 

 

resources
 

 

 

 

 

 

 

 

 

 

 

 

 

 

results

R[L],
O(SY)

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:

  1. Clarification that for systems deployed in USA, School Courses for the Exchange of Data (SCED) codes SHOULD be used for the values of the property 'subjectCodes' in the 'Class' and 'Course' payloads;
  2. Clarification that the Boolean data types are enumerations with permitted values of ‘true’ and ‘false’ ONLY. The properties clarified are:
    • Demographics.americanIndianOrAlaskaNative
    • Demographics.asian
    • Demographics.blackOrAfricanAmeircan
    • Demographics.nativeHawaiianOrOtherPacificIslander
    • Demographics.white
    • Demographics.demographicRaceTwoOrMoreRaces
    • Demographhics.hispanicOrLatinoEthnicity
    • Enrollments.primary
    • Users.enabledUser
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