Prepare Your Import File

General Information

Before importing data, you will need to create an import file. When creating your import file, it is important to note the following:

Do not use a double quote (") as the field delimiter.
The double quote is a reserved character and may only be used to ignore other delimiters.
Field delimiters are regarded as literals if the field value is surrounded by double-quotes.
Strings are regarded as literals if surrounded by double-quotes.
Double quotes that are surrounded by double quotes are regarded as part of the value.
The order of the fields is critical with many imports. Check online help or the export of the same data to see the proper order of fields.

Example 1

Field delimiter as a literal in the field value:

First Name, Last Name, AddressJohn, Smith, "123 Anyplace Dr, Ste 100"

Results:

FirstName	LastName	Address
John	Smith	123 Anyplace Dr, Ste 100

Example 2

Field delimiter as a literal in the field value surrounded by double quotes:

First Name, Last Name, AddressJohn, Smith, """123 Anyplace Dr, Ste 100"""

Results:

FirstName	LastName	Address
John	Smith	"123 Anyplace Dr, Ste 100"

Example 3

Double quotes used to escape a field delimiter:

First Name, Last Name, AddressJohn, Smith, 123 Anyplace Dr"," Ste 100

Produces the following results:

FirstName	LastName	Address
John	Smith	123 Anyplace Dr, Ste 100

Example 4

Quote within a field value:

First Name, Last Name, AddressJohn, Smith, 123 Anyplace """Dr,""" Ste 100

Produces the following results:

FirstName	LastName	Address
John	Smith	123 Anyplace "Dr," Ste 100

Example 5

Without the double quotes, the field delimiter is not escaped:

First Name, Last Name, AddressJohn, Smith, 123 Anyplace Dr, Ste 100

Produces the following results:

FirstName	LastName	Address
John	Smith	123 Anyplace Dr	Ste 100

Importing Code Sets

The Code Sets module provides the ability to import new code sets. The code sets can be hierarchical, meaning a code set can be a parent of another code set. Duplicates are ignored during the import. The imported code sets can be viewed on the district Code Sets page.

General

Entity	Rule
Boolean Flags	When specified, the values of 0, false, f, no, and n indicate false. 1, true, t, yes and y indicate true. Anything else is an error. Not case sensitive.
Dates	When specified, the date must be in either mm/dd/yyyy or mm/dd/yy format. Any date values not in that format result in an error.
Blanks	All Boolean flags have default values that are applied if no value is specified.

CodeSet Table

Entity	Rule
ParentCodesetID	The ID of the parent code set. When specified, the code set is created as a child of this parent code set. This field is optional. For example, if a code set (CodeSetID, CodeType, Code) exists for California state (292, State, CA) and you want to import a County, such as Sacramento, as a child code set for California state code set, then a new code set can be imported using ParentCodeSetID = 292, CodeType = County, Code = Sacramento.
ParentCodeType	The code type of the parent code set. Instead of ParentCodeSetID, a code set can specify its parent by ParentCodeType and ParentCode. This field is optional. For example, if a code set (CodeSetID, CodeType, Code) exists for California state (292, State, CA) and you want to import a County, such Sacramento, as a child code set for California state code set, then a new code set can be imported using ParentCodeType = State, ParentCode = CA, CodeType = County, Code = Sacramento or ParentCodeSetID = 292, CodeType = County, Code = Sacramento. If both parent code set and child code set are being imported in the same import file, parent code set must be placed before child code set in the import file.
ParentCode	The code of the parent code set. Instead of ParentCodeSetID, a code set can specify its parent by ParentCodeType and ParentCode. This field is optional. For example, if a code set (CodeSetID, CodeType, Code) exists for California state (292, State, CA) and you want to import a County, such as Sacramento, as a child code set for California state code set, then a new code set can be imported using ParentCodeType = State, ParentCode = CA, CodeType = County, Code = Sacramento or ParentCodeSetID = 292, CodeType = County, Code = Sacramento. If both parent code set and child code set are being imported in the same import file, parent code set must be placed before child code set in the import file.
CodeType	The code type of the code set. For example, County. This field is required. The combination of (ParentCodesetID, CodeType, Code) or (ParentCodeType, ParentCode, CodeType, Code) must be unique.
Code	The code value of the code set. For example, Sacramento. This field is required. The combination of (ParentCodesetID, CodeType, Code) or (ParentCodeType, ParentCode, CodeType, Code) must be unique.
DisplayValue	The value that displays in the code set menus, such as Home and Work for phone types and Male and Female for gender types. This field is optional. If not specified, the Code field value is used for display.
Description	A description of the code set. This field is optional.
UIDisplayOrder	The preferred order in which you want the code set to display.
IsVisible	Boolean flag. Indicates whether or not the code set can be viewed in menus. This field is optional. Defaults to true if a value is not specified and is the recommended value.
IsDeletable	Boolean flag. Indicates whether or not the code can be deleted from the District Code Sets page. This field is optional. Defaults to true if a value is not specified and is the recommended value.
IsModifiable	Boolean flag. Indicates whether or not user can modify the code from the District Code Sets page. This field is optional. Defaults to true if a value is not specified and is the recommended value.
ExcludeFromStateReporting	Boolean flag. Indicates whether a code set item is excluded from certain state and provincial reports. This field is optional. Defaults to false if not specified.
CodeOrigin	Indicates who created the code set. This field is optional. Enter User. Defaults to Import if not specified. Do not use System or Compliance, as they are already in use.
ReportedValue	Value used by State and Provincial Reporting, if populated, as an alternative to the Code value. This field can also be used when additional codes are added by the district to be sent to the state or provincial system. This field is optional. For example, if the state has a list of mandated languages and the district would like to add additional languages to the list, the district may do so if the ReportedValue is mapped to the state code to be reported.
AlternateCode1	A code that can be used to send to other systems as an alternative to Code. As an example, the code value might be "Mother", but another system might require "100". This field is optional.
AlternateCode2	A code that can be used to send to other systems as an alternative to Code. As an example, the code value might be "Mother", but another system might require "100". This field is optional.
EffectiveStartDate	The date the code becomes valid. This field is optional. Code start dates in the future display "Not yet available." The start date cannot be after the end date if both are specified.
EffectiveEndDate	The date the code is no longer valid. This field is optional. Code end dates prior to the currently selected term dates display "Expired." The end date cannot be before the start date if both are specified.

Importing Incidents

PowerSchool's Incident Management schema spans several complex tables that would otherwise require multiple separate imports to create a single incident. The incident data set in the Data Import Manager allows administrators to import incident records using a flat file import. The flat-file information is translated behind the scenes to fit the actual tables used for incident records.

A single incident can include multiple attributes, participants, actions, objects, or behaviors. As such, the incident import data set allows these column groups to be repeated in your import file to represent multiple groupings of the above items.

Incident Element Hierarchy

Within the incident element hierarchy, elements are categorized as a parent, sibling, or child element. Parent and sibling elements can be listed in any order. Child elements must be listed after a parent element in order for the fields to be valid.

Incident

Incident_Attribute
Action
- Action_Attribute
Object
Behavior
Participant
- Participant_Attribute
- Participant_Role
  - Participant_Object
  - Participant_Action
    - Participant_Action_Attribute
  - Participant_Behavior
    - Participant_Behavior_Action
      - Participant_Behavior_Action_Attribute

Data Element	Description
Incident	When creating your incident import file, you may include the following data (based on your PowerSchool incident management setup) for each incident you are importing: Incident_ID Incident_School_Number Incident_Title Incident_Description Incident_Prepared_By Incident_Incident_Type Incident_Incident_Date Incident_Incident_Time Incident_Time_Frame_Code Incident_Time_Frame_Subcode Incident_Time_Frame_Comment Incident_Financial_Impact Incident_Location_Code Incident_Location_Subcode Incident_Location_Description If Incident_School_Number is mapped, then the incident is associated with the mapped school or district office. If Incident_School_Number is not mapped, then the incident is associated with the current school. For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager. To delete an incident, include the Incident_Id and Incident_title fields in the import and add #delete instead of the title for any records to delete. This deletes the entire incident including behaviors, actions, and objects. You must be signed into the district level or the school where the incident was logged to delete the incident.
Incident_Attribute	Within the incident import file, these fields must be listed after an Incident in order for the fields to be valid. For each incident attribute in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Incident_Attribute_Code Incident_Attribute_Subcode (required if code is divided into sub codes) Incident_Attribute_Comment (may be forbidden, allowed or required based on configuration) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Action	Within the incident import file, these fields must be listed after an Incident in order for the fields to be valid. For each action in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Action_Code Action_Subcode (required if code is divided into sub codes) Action_Comment (may be forbidden, allowed or required based on configuration) Action_Begin_Date Action_End_Date Action Taken Detail Actual_Resolution_Date Action_Duration_Code Action_Duration_Subcode (required if code is divided into sub codes) Action_Duration_Comment (may be forbidden, allowed or required based on configuration) Action_Assigned_Duration Action_Actual_Duration Action_Duration_Notes Action_Change_Reason Action_Change_Code Action_Change_Subcode (required if Action Change Code is broken into sub codes) Action_Change_Comment (may be forbidden, allowed or required based on action change code configuration) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Action_Attribute	Within the incident import file, these fields must be listed after an Action in order for the fields to be valid. For each action attribute in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Action_Attribute_Code Action_Attribute_Subcode (required if code is divided into sub codes) Action_Attribute_Comment (may be forbidden, allowed or required based on configuration) Action_Attribute_Value (content of value field depends on configuration) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Object	Within the incident import file, these fields must be listed after an Incident in order for the fields to be valid. For each object in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Object_Code Object_Subcode (required if code is divided into sub codes) Object_Comment (may be forbidden, allowed or required based on configuration) Object_Description Object_Quantity For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Behavior	Within the incident import file, these fields must be listed after an Incident in order for the fields to be valid. For each behavior in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Behavior_Code Behavior_Subcode (required if code is divided into sub codes) Behavior_Subcode_2 (required if sub codes is divided into sub codes) Behavior_Subcode_3 (required if sub codes is divided into sub codes) Behavior_Subcode_4 (required if sub codes is divided into sub codes) Behavior_Comment (may be forbidden, allowed or required based on configuration) Behavior_Primary (use value Primary or leave blank) Behavior_Allegation (use value 1 or 0) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Participant	Within the incident import file, these fields must be listed after an Incident in order for the fields to be valid. For each participant in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Participant_Student_Number (cannot be populated if Teacher_Number is also populated) Participant_Teacher_Number (cannot be populated if Student_Number is also populated) Participant_Unknown (cannot be populated if Student_Number or Teacher_Number is also populated) Participant_First (cannot be populated if Student_Number or Teacher_Number is also populated) Participant_Middle (cannot be populated if Student_Number or Teacher_Number is also populated) Participant_Last (cannot be populated if Student_Number or Teacher_Number is also populated) Participant_Position (cannot be populated if Student_Number or Teacher_Number is also populated) Participant_Age (cannot be populated if Student_Number or Teacher_Number is also populated) Participant_Gender (cannot be populated if Student_Number or Teacher_Number is also populated) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Participant_Attribute	Within the incident import file, these fields must be listed after a Participant in order for the fields to be valid. For each participant attribute in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Participant_Attribute_Code Participant_Attribute_Subcode (required if code is divided into sub codes) Participant_Attribute_Comment (may be forbidden, allowed or required based on configuration) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Participant_Role	Within the incident import file, these fields must be listed after a Participant in order for the fields to be valid. For each participant role in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Participant_Role_Code (required if preceded by Participant) Participant_Role_Subcode (required if code is divided into sub codes) Participant_Role_Comment (may be forbidden, allowed or required based on configuration) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Particpant_Object	Within the incident import file, these fields must be listed after a Participant_Role in order for the fields to be valid. For each participant object in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Participant_Object_Code Participant_Object_Subcode (required if code is divided into sub codes) Participant_Object_Comment (may be forbidden, allowed or required based on configuration) Participant_Object_Description Participant_Object_Quantity For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Participant_Action	Within the incident import file, these fields must be listed after a Participant_Role in order for the fields to be valid. For each participant action in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Participant_Action_Code Participant_Action_Subcode (required if code is divided into sub codes) Participant_Action_Comment (may be forbidden, allowed or required based on configuration) Participant_Action_Begin_Date Participant_Action_End_Date Participant_Action_Taken_Detail Participant_Actual_Resolution_Date Participant_Action_Duration_Code Participant_Action_Duration_Subcode (required if code is divided into sub codes) Participant_Action_Duration_Comment (may be forbidden, allowed or required based on configuration) Participant_Assigned_Duration Participant_Actual_Duration Participant_Duration_Notes Participant_Action_Change_Reason Participant_Action_Change_Code Participant_Action_Change_Subcode (required if code is divided into sub codes) Participant_Action_Change_Comment (may be forbidden, allowed or required based on configuration) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Participant_Action_Attribute	Within the incident import file, these fields must be listed after a Participant_Action in order for the fields to be valid. For each participant action attribute in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Participant_Action_Attribute_Code Participant_Action_Attribute_Subcode (required if code is divided into sub codes) Participant_Action_Attribute_Comment (may be forbidden, allowed or required based on configuration) Participant_Action_Attribute_Value (content of value field depends on configuration) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Participant_Behavior	Within the incident import file, these fields must be listed after a Participant_Role in order for the fields to be valid. For each participant behavior in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Participant_Behavior_Code Participant_Behavior_Subcode (required if code is divided into sub codes) Participant_Behavior_Subcode_2 (required if sub codes is divided into sub codes) Participant_Behavior_Subcode_3 (required if sub codes is divided into sub codes) Participant_Behavior_Subcode_4 (required if sub codes is divided into sub codes) Participant_Behavior_Comment (may be forbidden, allowed or required based on configuration) Participant_Behavior_Primary (use value Primary or leave blank) Participant_Behavior_Allegation (use value 1 or 0) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Participant_Behavior_Action	Within the incident import file, these fields must be listed after a Participant_Behavior in order for the fields to be valid. For each participant behavior action in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Participant_Behavior_Action_Code Participant_Behavior_Action_Subcode (required if code is divided into sub codes) Participant_Behavior_Action_Comment (may be forbidden, allowed or required based on configuration) Participant_Behavior_Action_Begin_Date Participant_Behavior_Action_End_Date Participant_Behavior_Action_Taken_Detail Participant_Behavior_Action_Actual_Resolution_Date Participant_Behavior_Action_Duration_Code Participant_Behavior_Action_Duration_Subcode (required if code is divided into sub codes) Participant_Behavior_Action_Duration_Comment (may be forbidden, allowed or required based on configuration) Participant_Behavior_Action_Assigned_Duration Participant_Behavior_Action_Actual_Duration Participant_Behavior_Action_Duration_Notes Participant_Behavior_Action_Change_Reason Participant_Behavior_Action_Change_Code Participant_Behavior_Action_Change_Subcode (required if code is divided into sub codes) Participant_Behavior_Action_Change_Comment (may be forbidden, allowed or required based on configuration) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.
Participant_Behavior_Action_Attribute	Within the incident import file, these fields must be listed after a Participant_Behavior_Action in order for the fields to be valid. For each participant behavior action attribute in the incident data set, you may include the following data (based on your PowerSchool incident management setup): Participant_Behavior_Action_Attribute_Code Participant_Behavior_Action_Attribute_Subcode (required if code is divided into sub codes) Participant_Behavior_Action_Attribute_Comment (may be forbidden, allowed or required based on configuration) Participant_Behavior_Action_Attribute_Value (content of value field depends on configuration) For field descriptions, refer to Incident Management. When importing, these fields may only be mapped once. For more information, refer to Import Data Using the Data Import Manager.

Importing Custom Tables

The Data Import Manager allows administrators to import data into custom tables. These tables are not part of the core PowerSchool schema, but are created by PowerSchool custom development groups.

Custom Tables are listed in the Import Into menu on the Data Import Manager page using the name of the custom table.
Custom table fields are available for select data sets in the Data Import Manager when mapping fields from the import file to PowerSchool.
- If the custom table is a 1-to-1 child table, administrators must map one of the fields in the import file to the foreign key field in the custom table.
- If the custom table is a 1-to-many child table, administrators must map one of the fields in the import file to the foreign key field in the custom table when adding child records.
- When updating existing records in the custom table, administrators must map one of the fields in the import file to the primary key field. The foreign key field is ignored if the primary key field is mapped and present for the record. This means that it is not possible to update a foreign key value.
- If the custom table is a stand-alone table that does not have a parent table, administrators must map one of the fields in the import file to the primary key field only when attempting to update an existing record.
When mapping custom table fields, the fields appear in the following order:
- If the custom table is a 1-to-1 extension of a core table, after the last core field within the import module, the foreign key to the parent table appears first and then all the other custom table fields appear in alphabetical order.
- If the custom table is a 1-to-many child extension of a core table, after the core table, the custom child table fields are grouped and appear in alphabetical order. The foreign key to the parent table appears first, the primary key appears second, and then all the other custom table fields appear in alphabetical order.
- If the custom table is a stand-alone table that does not have a parent table, the primary key appears first and then all the other custom table fields appear in alphabetical order.
Standard import options are available when importing into a custom table.
- Administrators can choose whether or not to exclude the first row if it contains headers.
- Administrators can choose whether or not to update existing records if a match is found.
Data being imported into a custom table will be validated based on the data type and other field attributes. Data types include:
- Text must be a text value less than or equal to the database field's length property.
- Integer must be a whole number less than or equal to 19 digits long.
- Double must be a numeric value with up to one decimal point and be less than or equal 19 digits long.
- Boolean must be a numeric value of 0 or 1.
- Date must be a valid m/d/y format where m can be 1-12, d can be 1-31 and y can be two digits (05) or four digits (2005).
Import results for an import into a custom table are displayed in the Import Results page.