Prepare Your Import File
On this page:
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. Note: 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. Note: 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. Note: 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. Note: The combination of (ParentCodesetID, CodeType, Code) or (ParentCodeType, ParentCode, CodeType, Code) must be unique. |
DisplayValue | The value that displays in the code set pop-up 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 pop-up 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. Note: 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. Note: 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. Note: 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
- Participant_Behavior_Action
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:
Note: 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. Note: For field descriptions, see Incident Management. When importing, these fields may only be mapped once. For more information, see Step 6 of Import Data Using the Data Import Manager. |
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):
|
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_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):
|
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):
|
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):
|
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_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_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):
|
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_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_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_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_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_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):
|
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 pop-up 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.