CSV files for School Data Sync

Note: This topic is pre-release documentation and is subject to change in future releases.

You can use CSV (comma separated value) files with School Data Sync (SDS) to synchronize your School Information System (SIS) users with Office 365. If you use this method, you'll need six separate CSV files to set up the synchronization profile. This topic describes the CSV files that are required and the information that must be included in each one.

You must include the required attributes for the required CSV files for School Data Sync, and you can also include optional attributes into each CSV file.

Required CSV files for School Data Sync

CSV Import requires administrators to provide six CSV files, containing the required and optional attributes for SDS. For detailed steps for how to complete the sync, see How to deploy School Data Sync by using CSV files.

School Data Sync has a number of required attributes that must be included when you're provisioning users in Classroom. There are also a number of optional attributes for School.csv, Section.csv, Student.csv, and Teacher.csv which you can choose to import, but that are not required for Classroom provisioning.

The required attributes for School Data Sync varies based on the deployment options you choose when you create the Sync Profile.

Note: Make sure your CSV files don't contain invalid characters. Also, CSV files must not contain more than 550,000 rows of data. If you exceed 550K rows, you must split the additional rows into another set of 6 CSV Files, and create an additional sync profile to upload and sync them.

You can create the CSV files manually, extract them from the SIS database, or create them with help from the SIS vendor. The CSV content and formatting must be the same, regardless of how you create the files. If you need assistance with the data extraction, check with your SIS provider for support.

The six required CSV files are:

Note: These CSV files must be named exactly as shown, and all be contained within the same folder or directory on the local machine. No other files should exist in this directory. Also, each attribute name shown below for each CSV file will be configured as a header, within each of the respective CSV files.

All CSV files must be contained in the same directory

School.csv

The School.csv file must contain these attributes with corresponding values:

  • SIS ID   – SIS ID of the School. This value must be unique

  • Name   – Name of the School.

An example of a School.csv file

Optional attributes for School.csv

In addition to the Required Attributes listed above, the School.csv may include any of the optional attributes listed below. While these attributes are not required for SDS, they will provide additional identity details in Azure AD for each of the respective object types. When setting up your sync profile you will be able to select and deselect any of the optional attributes below. Any of the optional attributes selected in the sync profile must be reflected in the corresponding CSV file as a column header, and must also be an exact match for spelling and syntax (including spaces and upper/lower case characters).

The optional attributes are:

  • School Number

  • School NCES_ID

  • State ID

  • Grade Low

  • Grade High

  • Principal SIS ID

  • Principal Name

  • Principal Secondary Email

  • Address

  • City

  • State

  • Country

  • Zip

  • Phone

  • Zone

In the following example figure, the attributes shown in grey reflect the required attributes. The optional attributes are shown in blue.

Optional attributes for School.csv

Section.csv

The Section.csv file must contain these attributes with corresponding values:

  • SIS ID   – SIS ID of the Section. This value must be unique, cannot contain any spaces, and must contain only alpha-numeric characters. The only allowed special character in this field is the dash (-) character.

  • School SIS ID   – SIS ID of the School.

  • Section Name   – Name of the Section.

An example of Section.csv file

Optional attributes for Section.csv

In addition to the Required Attributes listed above, the Section.csv may include any of the optional attributes listed below. While these attributes are not required for SDS, they will provide additional identity details in Azure AD for each of the respective object types. When setting up your sync profile you will be able to select and deselect any of the optional attributes below. Any of the optional attributes selected in the sync profile must be reflected in the corresponding CSV file as a column header, and must also be an exact match for spelling and syntax (including spaces and upper/lower case characters).

The optional attributes are:

  • Section Number

  • Term SIS ID

  • Term Name

  • Term StartDate

  • Term EndDate

  • Course SIS ID

  • Course Name

  • Course Number

  • Course Description

  • Course Subject

  • Periods

  • Status

In the following example figure, the attributes shown in grey reflect the required attributes. The optional attributes are shown in blue.

Optional attributes for Section.csv

Student.csv

The Student.csv file must contain these attributes with corresponding values:

  • SIS ID   – SIS ID of the Student. This value must be unique.

  • School SIS ID   – SIS ID of the School.

  • First Name   – Not available for use if either synced identity option is selected in the profile creation.

  • Last Name   – Not available for use if either synced identity option is selected in the profile creation.

  • Username   – Should match the Alias or UserPrincipalName attribute based on the sync profile option selected. See How to Deploy School Data Sync by using CSV Files for more detail on the appropriate value to use. Since match occurs on the UserPrincipalName, this value must be unique if it is set to the UserPrincipalName. If this value is populated with the Alias, the alias + domain match logic that forms the corresponding UserPrincipalName in Azure Active Directory must be unique.

  • Password   – Not available for use if either synced identity option is selected in the profile creation. Passwords included in the CSV files must adhere to Azure AD Password requirements defined in cloud user account password policies.

An example of Student.csv file

Optional attributes for Student.csv

In addition to the Required Attributes listed above, the Student.csv may include any of the optional attributes listed below. While these attributes are not required for SDS, they will provide additional identity details in Azure AD for each of the respective object types. When setting up your sync profile you will be able to select and deselect any of the optional attributes below. Any of the optional attributes selected in the sync profile must be reflected in the corresponding CSV file as a column header, and must also be an exact match for spelling and syntax (including spaces and upper/lower case characters).

The optional attributes are:

  • State ID

  • Secondary Email

  • Student Number

  • Grade

  • Status

  • Mailing Address

  • Mailing City

  • Mailing State

  • Mailing Zip

  • Mailing Latitude

  • Mailing Longitude

  • Mailing Country

  • Residence Address

  • Residence City

  • Residence State

  • Residence Zip

  • Residence Latitude

  • Residence Longitude

  • Residence Country

  • Gender

  • Birthdate

  • ELL Status

  • FederalRace

  • Graduation Year

In the following example figure, the attributes shown in grey reflect the required attributes. The optional attributes are shown in blue.

Optional attributes for Student.csv

Teacher.csv

The Teacher.csv file must contain these attributes with corresponding values:

  • SIS ID   – SIS ID of the Teacher. This value must be unique.

  • School SIS ID   – SIS ID of the School.

  • First Name   – Not available for use if either synced identity option is selected in the profile creation.

  • Last Name    –Not available for use if eithersynced identity option is selected in the profile creation.

  • Username   – Should match the Alias or UserPrincipalName attribute based on the sync profile option selected. See How to Deploy School Data Sync by using CSV Files for more detail on the appropriate value to use. Since match occurs on the UserPrincipalName, this value must be unique if it is set to the UserPrincipalName. If this value is populated with the Alias, the alias + domain match logic that forms the corresponding UserPrincipalName in Azure Active Directory must be unique.

  • Password   – Not available for use if either synced identity option is selected in the profile creation. Passwords included in the CSV files must adhere to Azure AD Password requirements defined in cloud user account password policies.

An example of Teacher.csv file

Optional attributes for Teacher.csv

In addition to the Required Attributes listed above, the Teacher.csv may include any of the optional attributes listed below. While these attributes are not required for SDS, they will provide additional identity details in Azure AD for each of the respective object types. When setting up your sync profile you will be able to select and deselect any of the optional attributes below. Any of the optional attributes selected in the sync profile must be reflected in the corresponding CSV file as a column header, and must also be an exact match for spelling and syntax (including spaces and upper/lower case characters).

The optional attributes are:

  • State ID

  • Teacher Number

  • Status

  • Secondary Email

  • Title

  • Qualification

In the following example figure, the attributes shown in grey reflect the required attributes. The optional attributes are shown in blue.

Optional attributes for Teacher.csv

StudentEnrollment.csv

The StudentEnrollment.csv file must contain these attributes with corresponding values:

  • Section SIS ID   – SIS ID of the Section.

  • SIS ID   – SIS ID of the Student.

An example of StudentEnrollment.csv file

TeacherRoster.csv

The TeacherRoster.csv file must contain these attributes with corresponding values:

  • Section SIS ID   – SIS ID of the Section.

  • SIS ID   – SIS ID of the Teacher.

An example of TeacherRoster.csv file

See Also

Overview of School Data Sync and Classroom

How to deploy School Data Sync by using CSV files

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×