Share via


SDS V2.1 CSV File Format

You can use CSV (comma-separated value) files in the SDS V2.1 format to sync your School Information System (SIS) with Office 365. Using the SDS V2.1 CSV file format to ingest the data categories enables you to light up core SDS capabilities for provisioning. It also helps you enhance experiences for the Microsoft 365 products and features. The SDS V2.1 CSV file format will continue to expand the data categories it supports.

If you're using the SDS V2.1 format, the files must be appropriately formatted. Upload the files, named exactly as detailed. Column headers are case-sensitive. CSV files must be in UTF-8 format and RFC compliant. Also we don't accept line breaks in incoming data. To review and download sample set of SDS V2.1 CSV files, see the SDS GitHub Repository.

Important

After your initial upload during the creation of your inbound flow, you'll need to always supply the same set of files for subsequent uploads, regardless of data changes made in the optional files or not. If you do not the system will determine that the records are no longer active and will mark them as such.

Required: The following three files must be provided. If you're only managing users and not classes, you only need to supply these files. 

  • orgs.csv 

  • users.csv 

  • roles.csv 

Optional/ Required: The following two files aren't required if you're only managing users. If you're also managing classes and class enrollments, you need to provide the two files listed below, along with the three files above.

  • classes.csv 

  • enrollments.csv

Optional: The following are optional files and are used to enhance experiences. These files aren't required, unless the files above have data references based on the notes below. 

  • academicSessions.csv (If your roles.csv, classes.csv, or courses.csv contains academic session data, the corresponding data should be provided to avoid error messages when processing data.) 

  • courses.csv (If your classes.csv data contains links to courses, the corresponding data should be provided to avoid error messages when processing data.) 

  • demographics.csv 

  • userFlags.csv 

  • relationships.csv

Each CSV file must contain data for the fields noted 'Yes' under 'Required', the fields noted 'No' are 'Optional' data. The field headers marked as 'Required' must exist in the supplied files or they won't be accepted. The field headers marked as "Optional' aren't required to be present in the files if not passing the corresponding optional data.

The fields with defined enumerated values as marked in the 'Data Types' column have a specific set of values they support. The supported set of default values for Enum fields see Default List of Values.

orgs.csv

Field Required? Example Data type
sourcedId Yes 10001 Unique ID
name Yes College of Engineering String
type Yes college Enum
parentSourcedId No 10000 Unique ID

users.csv

Field Required? Example Data Type
sourcedId Yes 14001 Unique ID
username Yes [email protected] String
familyName Yes (only if creating users) Craig String
givenName Yes (only if creating users) Beane String
activeDirectoryMatchId No [email protected] String
email No [email protected] Email
phone No +1234567890 Phone
sms No +1234567890 Phone
userNumber No 14001 String
  • familyName and givenName are required for users if using the option for ‘Create unmatched users’.
  • familyName, givenName, and email are required for users if using the option for 'Student contact associations' for users that have a contact relationship role in relationships.csv. Users that are staff, but also contacts, it's recommended that they have two user records listed, with the contacts user record associated with their private email address.
  • Expect phone and sms fields to be formatted based on the E.164 standard where the plus character is used and succeeded by the phone number, or rather the plus character followed by country code, area code, telephone prefix, and line number.
  • password is no longer used to supply default password for creating new users.

roles.csv

Field Required? Example Data Type
userSourcedId Yes 14001 Unique ID
orgSourcedId Yes 10001 Unique ID
role Yes student Enum
sessionSourcedId No FS2021 Unique ID
grade No PS1 Enum
isPrimary No true Boolean
roleStartDate No 2020-08-24 Date
roleEndDate No 2020-12-11 Date
  • Requires a reference to the associated user using the 'userSourcedId' field. This produces a dependency on the users.csv file.
  • Requires a reference to the associated org using the 'orgSourcedId' field. This produces a dependency on the orgs.csv file.
  • If passed: This requires a reference to the associated terms (academic sessions) using the 'sessionSourcedId' field. This produces a dependency on the academicSessions.csv file.
  • isPrimary indicates if this role is the primary (true) or secondary (false) role for that org. If passing multiple roles, for the same user and same org, there SHOULD be one, and only one, primary role set to (true) for each org.
  • Date values to be supplied in ISO8601 format.
  • Leading zeros ‘0’ missing on incoming value for Grade Level ENUM, for example ‘1’, will match defined List of Value (ENUM) ‘01’ and will be stored as ‘01’.

classes.csv

Field Required? Example Data Type
sourcedId Yes 12001 Unique ID
orgSourcedId Yes 10001 Unique ID
title Yes Computer Science 101 String
sessionSourcedIds No FS2021 Unique ID[]
courseSourcedId No C12001 Unique ID
code No 12001 String
  • This requires a reference to the associated school (org) using the 'orgSourcedId' field. This produces a dependency on the orgs.csv file.
  • If passed: This requires a reference to the associated terms (academic sessions) using the 'sessionSourcedIds' field. This produces a dependency on the academicSessions.csv file.
  • If passed: If a class is associated to multiple sessions in the same Academic Year, for example Fall Semester and Spring Semester, you can supply both of the sourcedId for the sessions on the record. Values should be combined into a single string with the values surrounded by quotes and separated by commas.
    • The association to multiple sessionSourcedIds is stored in the Data Lake only.
    • The Microsoft 365 Group extension attribute for session IDs doesn't support multiple associations.
  • If passed: This requires a reference to the associated course using the 'courseSourcedId' field. This produces a dependency on the courses.csv file.

enrollments.csv

Field Required? Example Data Type
classSourcedId Yes 12001 Unique ID
userSourcedId Yes 14001 Unique ID
role Yes student Enum
  • This requires a reference to the associated class using the 'classSourcedId' field. This produces a dependency on the classes.csv file.
  • This requires a reference to the associated user using the 'userSourcedId' field. This produces a dependency on the users.csv file.

academicSessions.csv

Filed Required? Example Data Type
sourcedId Yes FS2021 Unique ID
title Yes Fall semester String
type Yes semester Enum
schoolYear Yes 2021 Enum
startDate Yes 2020-08-24 Date
endDate Yes 2020-12-11 Date
  • Expect date values to be supplied in ISO8601 format.

courses.csv

Field Required? Example Data Type
sourcedId Yes C12001 Unique ID
orgSourcedId Yes 10001 Unique ID
title Yes Computer Science 101 String
code No CS101 String
schoolYearSourcedId No Y2021 Unique ID
subject No 10 Enum
grade No undergraduate Enum
  • This requires a reference to the associated organization (org) using the orgSourcedId field. This produces a dependency on the orgs.csv file.
  • If passed: This requires a reference to the associated academic session year record using the 'schoolYearSourcedId' field where the type = schoolYear. This produces a dependency on the academicSessions.csv file.
  • Leading zeros ‘0’ missing on incoming value for Grade Level ENUM, for example ‘1’, will match defined List of Value (ENUM) ‘01’ and will be stored as ‘01’.

relationships.csv

Field Required? Example Data Type
userSourcedId Yes 14001 Unique ID
relationshipUserSourcedId Yes P14001 Unique ID
relationshipRole Yes guardian Enum
  • If you're also syncing Contact Relationships (guardians), you must include a record for them in the users.csv.
  • This requires a reference to the associated user using the 'userSourcedId' field. This produces a dependency on the users.csv file.
  • This requires a reference to the associated user using the 'relationshipUserSourcedId' field. This produces a dependency on the users.csv file.
  • Additionally, familyName, givenName, and email are required for users that have Contact Relationship roles.

demographics.csv

Field Required? Example Data Type
userSourcedId Yes 14001 Unique ID
sex No male Enum
birthDate No 2001-07-20 Date
birthCity No Woodenville String
birthState No WA Enum
birthCountry No USA String
ethnicityCodes No hispanicOrLatinoEthnicity Enum[]
raceCodes No demographicRaceTwoOrMoreRaces Enum[]
  • This requires a reference to the associated user using the 'userSourcedId' field. This produces a dependency on the users.csv file.
  • Multiple values mapping to 'raceCodes' should be combined into a single string with the values surrounded by quotes and separated by commas.

userFlags.csv

Field Required? Example Data Type
userSourcedId Yes 14001 Unique ID
flag Yes lep Enum
  • This requires a reference to the associated user using the 'userSourcedId' field. This produces a dependency on the users.csv file.