Appendix P - Ontario Schools Reference File Import Instructions

Ministry Schools Data Source File Import Instruction

Ministry Schools Contact information data source files are updated frequently, and it is up to the board on how often they need to update the data stored in the PowerSchool table. The data is stored in S_ON_MINISTRYSCHOOLS_S table. This page is created to provide detailed instructions on how to import these files into the table.

Download Data Source Files

The Ministry provides two data source files for the school's contact information

1. Ontario Public School Contact Information (Publicity_funded_schools.xlsx). This file holds elementary and secondary public school information. Download this Data Source file from: https://data.ontario.ca/dataset/ontario-public-school-contact-information
2. Private Schools Contact Information (private_school_contact_list.xlsx). This file has 2 sheets (Private schools in Ontario and Overseas Private schools). Both sheets can be imported. Download this Data Source file from: https://data.ontario.ca/dataset/private-school-contact-information

Prepare Data Source Files for Import

To ensure your current and future data is not negatively impacted, ensure you save each Sheet in a separate file in the format Text (Tab delimited). Do not Save the files in spreadsheet format. Create a total of three files, one for Publicity Funded Schools, one for Private Schools in Ontario, and one for Overseas Private Schools.

1. Open Publicity_funded_schools.xlsx.
2. Click File > Save as and save the file in Text (Tab delimited) format.
3. Open private_school_contact_list.xlsx.

1. Add 2 new columns Board Number and School Language columns to both sheets.
2. Update board number to B87106 for all records.
3. Set all records School Language column to the language of the school.
4. Click File > Save as and save each sheet in a separate file in Text (Tab delimited) format.

Import Records to the [S_ON_MINISTRYSCHOOLS_S] Table

If this is not the first time you are importing the data source files, follow the steps below on the Delete Records from the [S_ON_MINISTRYSCHOOLS_S] Table section. The table must be empty before you attempt to import the new files

Steps to Import

1. Sign in to the PowerSchool Admin portal.
2. Choose District from the School drop-down menu in the navigation toolbar.
3. On the Start Page, under Functions, choose Special Functions. The Special Functions page appears.
4. Click Importing & Exporting. The Importing & Exporting page appears.
5. Click Data Import Manager. The Data Import Manager page appears. Complete the fields as follows:

6. Click Next or click the Map Columns header.

• The Next button and the Map Columns header appear shaded until the Source and Import Into are populated.
• Not all columns from the ministry file are stored in the table. Only the columns mentioned in the below mapping table are stored.

7. Use the following table to map columns for Publicly funded schools:

8. Click Next or the Select Options Note. The Next button and the Select Options header appear shaded until at least one column is mapped, or an identifier (required field) is mapped. The same primary column is not mapped more than once.

9. Select Check to exclude the first row.

10. Select Do not process record, if a record in the import file matches an existing record in the database, how would you like that record processed?

11. Clear the Check to confirm that records should be deleted if blank rows exist in the import file... checkbox to retain all data in the specified column.

12. Click Import. The Import Results page displays a summary of the processed records. Select "Refresh" periodically until all records have been imported.

The total number of records in the table after successful import should be equal to the total number of records in the imported file.

13. Repeat steps 1-12 to import the Private Schools in Ontario and the Overseas Private Schools file.

Use the following table to map columns for Private Schools in Ontario:

Use the following table to map columns for Overseas Private Schools:

You can save the mapping information into a template for future use. For information about templates, refer to System Help under PowerSchool Help.

Delete Records from the [S_ON_MINISTRYSCHOOLS_S] Table

The existing records in the [S_ON_MINISTRYSCHOOLS_S] table must be deleted before you import the files.

Make sure this table is empty before you import new files. If this table is not empty, duplicate records will be generated.

Use PowerSchool Importing and Exporting to Delete Existing Data

The purpose of exporting the existing IDs and then importing them with no other data is to delete records from the [S_ON_MINISTRYSCHOOLS_S] table.

Export the existing IDs:

1. Sign into the PowerSchool Admin portal.
2. On the Start Page, under Functions, click Special Functions. The Special Functions page appears.
3. Click Importing & Exporting. The Importing & Exporting page appears.
4. Under Data Export, click Data Export Manager. The Data Export Manager page appears. In the Select Columns to the Export section:

1. Choose Database Extensions from Category.
2. Choose S_ON_MINISTRYSCHOOLS_S from Export From.
3. Select all the fields in the [Data Source] checkbox.

• Click Next.
• In the Select/Edit Records to Export from [Data Source] section, select Export All Rows.
• Click Next.
• In the Export Summary and Output Options field, choose Export.
• Save the IDs file.

• After exporting the file, Keep the header row and clear the data from all columns except for the Id column.

  

  Row 1 will show every column title as Tab Delimited. All other rows contain the exported IDs.

Import to delete the records:

1. Sign into the PowerSchool Admin portal.
2. Choose District from the School dop-down menu in the navigation toolbar.
3. On the Start Page, under Functions, choose Special Functions. The Special Functions page appears.
4. Click Importing & Exporting. The Importing & Exporting page appears.
5. Click Data Import Manager. The Data Import Manager page appears.

6. Click Next or click the Map Columns header.

7. Use the following table to enter information in the Map Columns fields:

8. Click Next or the Select Options header.

9. Select Check to exclude the first row.

10. Select Update existing record.

11. Select Check to confirm that records should be deleted if blank rows exist in the import file....

12. Click Import. The Import Results page displays. All records are deleted.

13. Verify there are 0 Failed records.