OneRoster CSV tables
CAPIT supports data imports using v1.1 OneRoster CSV BULK updates. The specification can be viewed in full here, however the following summary should be sufficient for most users.
An example OneRoster CSV upload is also available here.
Files
A OneRoster CSV BULK update is a .zip file containing one or more .csv files (also referred to as tables). The manifest.csv file contains information about the .zip archive, including what other .csv files it contains. The data .csv files contain the information that is imported into CAPIT and they have special names to describe what information they contain. There can be up to 13 data .csv files in the .zip, but CAPIT only uses 4 of them.
All files must be correctly named and at the top level of the .zip file (they cannot be contained within a sub-directory). The .zip file can have any name, so long as it as a .zip extension.
Values
All file names, columns and field values are case-sensitive and cannot be accepted if they do not match the correct capitalization described here.
The first line of each .csv file must be a comma-separated list of column names. All of the fields listed as required for a .csv table must be present. The columns can be in any order, so long as each subsequent line has the values in that same order. There can be more columns than those mentioned below - any extra columns will be ignored and will not affect the data import.
Each line in the .csv files must be a comma-separated list of values with the same number and order of values as the lines around it.
Values that include commas must be enclosed in double quotation marks (e.g. "value1,value2"). This applies to when the value is itself a comma-separated list, such as grades in the users.csv. If you are using Microsoft Excel or a similar spreadsheet program to create or edit your .csv files, do not use double quotation marks as these will be automatically added for you when you save the .csv file. Values that do not have commas can also be enclosed in double quotation marks, but do not need to be.
All fields mentioned in the tables below are required to be in the .csv files. Please consult the individual tables below for whether those .csv columns are required to have a value or can be left blank.
An empty value, '', "" or NULL are all interpreted as a field having no value.
The full list of requirements for the CSV format can be viewed here.
Warning: Using Excel and similar spreadsheet programs
Please be aware that we do not recommend using Microsoft Excel or similar spreadsheet programs to create or open .csv files.
These programs can alter values without your knowledge, which may cause your CSV files to no longer be compatible with the OneRoster standard. Particularly, Excel may change the capitalisation of reserved values, such as grades or user types, and remove the leading or trailing 0's from number and grade values.
manifest.csv
The manifest.csv file contains information about which of the other (data) .csv files are included in the .zip and the .zip's version. The manifest version must be greater than any previously submitted .zip file, or it will be rejected as an update.
It is different from the other .csv files in that it only has two columns propertyName and value and its values go down the file, rather than across it. The first row must still be a comma separated list of columns, (e.g.) propertyName,value
All the values included in the following table are required, but the important ones are in green.
| propertyName | Required | Accepted values | Description |
|---|---|---|---|
| manifest.version | Yes | The initial value should be 1.0 and increment with each new upload. | The version of the .zip. Updates that have a manifest version equal to or less than the last upload will not be processed. Increments can be major (i.e. moving from 1.0 to 2.0) or minor (i.e. moving from 1.0 to 1.1). |
| oneroster.version | Yes | 1.1 | This is version of the OneRoster CSV specification that should be used to interpret the .zip file. |
| file.academicSessions | Yes | absent or bulk | Whether the academicSessions.csv file (not discussed here) is included in the .zip. Regardless of the value, the academicSessions.csv will not be included in the import. |
| file.categories | Yes | absent or bulk | Whether the categories.csv file (not discussed here) is included in the .zip. Regardless of the value, the categories.csv will not be included in the import. |
| file.classes | Yes | bulk | Whether the classes.csv file is included in the .zip. The classes.csv should always be included in the .zip for the import to work correctly. |
| file.classResources | Yes | absent or bulk | Whether the classResources.csv file (not discussed here) is included in the .zip. Regardless of the value, the classResources.csv will not be included in the import. |
| file.courses | Yes | absent or bulk | Whether the courses.csv file (not discussed here) is included in the .zip. Regardless of the value, the courses.csv will not be included in the import. |
| file.courseResources | Yes | absent or bulk | Whether the courseResources.csv file (not discussed here) is included in the .zip. Regardless of the value, the courseResources.csv will not be included in the import. |
| file.demographics | Yes | absent or bulk | Whether the demographics.csv file (not discussed here) is included in the .zip. Regardless of the value, the demographics.csv will not be included in the import. |
| file.enrollments | Yes | bulk | Whether the enrollments.csv file is included in the .zip. The enrollments.csv should always be included in the .zip for the import to work correctly. |
| file.lineItems | Yes | absent or bulk | Whether the lineItems.csv file (not discussed here) is included in the .zip. Regardless of the value, the lineItems.csv will not be included in the import. |
| file.orgs | Yes | bulk | Whether the orgs.csv file is included in the .zip. The orgs.csv should always be included in the .zip for the import to work correctly. |
| file.resources | Yes | absent or bulk | Whether the resources.csv file (not discussed here) is included in the .zip. Regardless of the value, the resources.csv will not be included in the import. |
| file.results | Yes | absent or bulk | Whether the results.csv file (not discussed here) is included in the .zip. Regardless of the value, the results.csv will not be included in the import. |
| file.users | Yes | bulk | Whether the users.csv file is included in the .zip. The users.csv should always be included in the .zip for the import to work correctly. |
Data csv files
For simplicity, only the four .csv files used in the import are described here (see the full list).
orgs.csv
The orgs.csv file is where educational organisations are created. This where districts and schools are established in the import.
If you are importing data for a district, that district and all of its schools must be included (no reference to a district is required). If you are importing data for a school, only that school needs to be included. In either case, the sourcedId of the organization you are importing data for must match the value you have provided CAPIT.
| Column | Required | Description |
|---|---|---|
| sourcedId | Yes | A unique ID for the organization, used to refer to the organization in other tables. Must be less than 256 characters. |
| type | Yes | The type of the organization; only organizations that are either district or school are imported. |
| name | Yes | The name of the organization. |
| parentSourcedId | No | The sourcedId of the parent organization. Only schools with a district parent organization are observed. |
users.csv
The users.csv table is where users of all types are created. It is how students and teachers are established and how administrators are linked to schools and districts. Only the first administrator user for a school or district is imported.
| Column | Required For Administrators | Required For Teachers | Required For Students | Description |
|---|---|---|---|---|
| sourcedId | Yes | Yes | Yes | A unique ID for the user, used to refer to the user in other tables. Must be less than 256 characters. |
| givenName | Yes | Yes | Yes | User's given name. |
| middleName | No | No | No | User's middle name. |
| familyName | Yes | Yes | Yes | User's family name. A single initial may be used for students. |
| Yes | Yes | No | User's email address. | |
| orgSourcedIds | Yes | Yes | Yes | A comma-separated list of the sourcedId values of the organizations the user belongs to (this is usually only one organization). This is used to associate administrators with the organizations they manage. Teachers and students are connected to organizations through their enrollments in enrollments.csv but should still have their school's sourcedId listed here. |
| role | Yes | Yes | Yes | The role the user has in the organization. Only users that are a teacher, student or administrator are imported. |
| grades | No | No | Yes | A comma-separated list of grades for student users. |
classes.csv
The classes.csv table is where classes are created and linked to the schools that hold them.
| Column | Required | Description |
|---|---|---|
| sourcedId | Yes | A unique ID for the class, used to refer to the class in other tables. Must be less than 256 characters. |
| title | Yes | The name of the class. |
| schoolSourcedId | Yes | The sourcedId of the school the class is held in. |
enrollments.csv
The enrollments.csv table is where teachers and students are connected to classes. Both are enrolled in a particular class (or classes), according to their role.
| Column | Required | Description |
|---|---|---|
| sourcedId | Yes | A unique ID for the enrollment. Must be less than 256 characters. |
| userSourcedId | Yes | The sourcedId of the user enrolled in the class. |
| role | Yes | The role the user has in the class: either teacher or student. |
| classSourcedId | Yes | The sourcedId of the class the user is enrolled in. |
| schoolSourcedId | Yes | The sourcedId of the school that holds the class. |
Types
Organization Types
Organizations that appear in orgs.csv must have one of the following types. All values are case-sensitive.
| Type | Imported |
|---|---|
| district | Yes |
| department | No |
| local | No |
| national | No |
| school | Yes |
| state | No |
Role Types
Users that appear in enrollments.csv must have one of the following roles. All values are case-sensitive.
| Type | Imported |
|---|---|
| administrator | Only the first administrator for each district or school. |
| aide | No |
| guardian | No |
| parent | No |
| proctor | No |
| relative | No |
| student | Yes |
| teacher | Yes |
Grade Types
student users that appear in users.csv must have one of the following grades. All values are case-sensitive.
| Type | Imported |
|---|---|
| IT | Yes |
| PR | Yes |
| PK | Yes |
| TK | Yes |
| KG | Yes |
| 01 | Yes |
| 02 | Yes |
| 03 | Yes |
| 04 | Yes |
| 05 | Yes |
| 06 | Yes |
| 07 | Yes |
| 08 | Yes |
| 09 | Yes |
| 10 | Yes |
| 11 | Yes |
| 12 | Yes |
| 13 | Yes |
| PS | Yes |
| UG | Yes |
| Other | Yes |