Appendix K - EYE Licensed Child Care Facilities Import

Early Years Experiences Licensed Child Care Facilities Import Instructions

Following are the Instructions for importing Licensed Child Care Facilities.

Download and Save Licensed Child Care Facilities File

To ensure your current and future data is not negatively impacted, ensure you save the Licensed Child Care Facilities file in the format Text (Tab delimited).

IMPORTANT: 
Do not save the child care list file in Excel format. Excel might treat the license number field as a number and remove the leading zeros, which will cause serious issues with your existing and future data.

1. Go to https://data.ontario.ca/dataset/licensed-child-care-facilities-in-ontario. The Licensed child care facilities in Ontario webpage opens.

2. Download the Licensed Child Care Facilities file.

3. In Excel, use Format Cells to change the format of Original Issue Date (Col  I) and Closure or Terminate date (Col K) to Date - MM/DD/YY.

4. Click File > Save as and save the file in Text (Tab delimited) format. 
   

     

Delete Records from the [S_ON_EYE_CARECENTER_S] Table

The existing records in the table [S_ON_EYE_CareCenter_S] must be deleted before you import the Early Years Experiences Child Care Facilities file.

IMPORTANT: Make sure this table is empty before you import a new file; failure to do so will result in the generation of duplicate records.

There are two ways to delete all rows from the [S_ON_EYE_CareCenter_S] table.

1. Have your IT department run the script file on your server.

2. Use PowerSchool Importing and Exporting to delete to data.

Note for hosted clients: Hosted customers must either follow method 2 to delete the records or contact support.

Method 1 - Run Script File to Delete Existing data

The purpose of the script file is to delete all records from the S_ON_EYE_CareCenter_S] table.
Note: This procedure must be performed by your IT department.

1. Download and save the script file DeleteAll_ON_EYE_CareCenter_S.sq

2. Run the script file to delete all records from the [S_ON_EYE_CareCenter_S] table.

Method 2 - 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_EYE_CareCenter_S] table.

Export the existing IDs:

1. Sign in to 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 Export section:Choose Database Extensions from the Category pop-up menu.Choose S_ON_EYE_CARECENTER_S from the Export From pop-up menu.Select the ID field [Data Source] checkbox.

5. Click Next.

6. In the Select/Edit Records to Export from [Data Source] section, select Export All Rows.

7. Click Next.

8. In the Export Summary and Output Options field, choose Export.

9. Save the IDs file.

Add the header row to the IDs file:

1. Open the IDs file in a text editor.

2. Download and open the header row file S_ON_EYE_CareCenter_Header.txt, which lists every column in the table.

3. Copy and paste the header row into the IDs file.

4. Save the IDs file.

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

Import to delete the records:

1. Sign in to the PowerSchool Admin portal.

2. Choose District from the School pop-up 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.
   
   

   Field	Description
   Source	Enter the file path and name of the file that you saved above, with the list of ID's to be deleted, and click Open.
   Import Into	Set to S_ON_EYE_CARECENTER_S
   Field Delimiter	Set to Tab
   Character Set	Use the pop-up menu to choose the character set for the import file. This selection is specific to the operating system where the import file was created. Options are: Unicode Windows ANSI (Windows) Mac Roman (Mac) ISO 8859-1 (Unix)

6. Click Next or click the Map Columns header.

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

   Import Field Columns	PowerSchool Fields
   S_ON_EYE_CARECENTER_S.ID	S_ON_EYE_CARECENTER_S.ID
   S_ON_EYE_CARECENTER_S.CCC_HCCA_NAME	S_ON_EYE_CARECENTER_S.CCC_HCCA_NAME
   S_ON_EYE_CARECENTER_S.CITY	S_ON_EYE_CARECENTER_S.CITY
   S_ON_EYE_CARECENTER_S.closureTerminationDate	S_ON_EYE_CARECENTER_S.closureTerminationDate
   S_ON_EYE_CARECENTER_S.CMSM_DSSAB	S_ON_EYE_CARECENTER_S.CMSM_DSSAB
   S_ON_EYE_CARECENTER_S.licenseeName	S_ON_EYE_CARECENTER_S.licenseeName
   S_ON_EYE_CARECENTER_S.licenseeNumber	S_ON_EYE_CARECENTER_S.licenseeNumber
   S_ON_EYE_CARECENTER_S.licenseStatus	S_ON_EYE_CARECENTER_S.licenseStatus
   S_ON_EYE_CARECENTER_S.modificationDate	S_ON_EYE_CARECENTER_S.modificationDate
   S_ON_EYE_CARECENTER_S.originalIssueDate	S_ON_EYE_CARECENTER_S.originalIssueDate
   S_ON_EYE_CARECENTER_S.postalCode	S_ON_EYE_CARECENTER_S.postalCode
   S_ON_EYE_CARECENTER_S.programOption	S_ON_EYE_CARECENTER_S.programOption
   S_ON_EYE_CARECENTER_S.programType	S_ON_EYE_CARECENTER_S.programType
   S_ON_EYE_CARECENTER_S.Province	S_ON_EYE_CARECENTER_S.Province
   S_ON_EYE_CARECENTER_S.Region	S_ON_EYE_CARECENTER_S.Region
   S_ON_EYE_CARECENTER_S.streetName	S_ON_EYE_CARECENTER_S.streetName
   S_ON_EYE_CARECENTER_S.streetNumber	S_ON_EYE_CARECENTER_S.streetNumber
   S_ON_EYE_CARECENTER_S.streetType	S_ON_EYE_CARECENTER_S.streetType

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 rows have been deleted" in the Imported field.

Import Records into [S_ON_EYE_CARECENTER_S]

The purpose of this import is to insert the licensed childcare facilities from the file downloaded from the ministry site into the [S_ON_EYE_CARECENTER_S] table.

1. Sign in to the PowerSchool Admin portal.

2. Choose District from the School pop-up 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:

   Field	Description
   Source	Enter the file path and name of the file to import or click Choose the text File: and choose the file you downloaded from the ministry site and saved as text. Click Open.
   Import Into	Set to S_ON_EYE_CARECENTER_S
   Field Delimiter	Set to Tab
   Character Set	Use the pop-up menu to choose the character set for the import file. This selection is specific to the operating system where the import file was created. Options are: Unicode Windows ANSI (Windows) Mac Roman (Mac) ISO 8859-1 (Unix)

6. Click Next or click the Map Columns header.
   Note: The Next button and the Map Columns header appear shaded until the Source and Import Into are populated.

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

   Import Field Columns	PowerSchool Fields
   Report Snapshot Date	leave blank
   Licensee Name	S_ON_EYE_CARECENTER_S.licenseeName
   Program Type Desc	S_ON_EYE_CARECENTER_S.programTyp
   Region Display Name	S_ON_EYE_CARECENTER_S.Region
   CMSM DSSAB Name	S_ON_EYE_CARECENTER_S.CMSM_DSSAB
   Child Care Center Name	S_ON_EYE_CARECENTER_S.CCC_HCCA_NAME
   License Number	S_ON_EYE_CARECENTER_S.licenseNumber
   Program Option	S_ON_EYE_CARECENTER_S.programOption
   Original Issue Date	S_ON_EYE_CARECENTER_S.originalIssueDate
   Licence Status	S_ON_EYE_CARECENTER_S.licenseStatus
   Closure or Termination Date	S_ON_EYE_CARECENTER_S.closureTerminationDate
   Language of Service Desc	leave blank
   Street Number	S_ON_EYE_CARECENTER_S.streetNumber
   Street Name	S_ON_EYE_CARECENTER_S.streetName
   Street Type	S_ON_EYE_CARECENTER_S.streetType
   City	S_ON_EYE_CARECENTER_S.City
   Province	S_ON_EYE_CARECENTER_S.Province
   Postal Code	S_ON_EYE_CARECENTER_S.postalCode

   Note: You can save the mapping information into a template for future use. For information about templates, see PowerSchool Help > System Help.

   
   

8. Click Next or the Select Options header. 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. Deselect 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. 
    Note: The total number of records in the table after successful import should be equal to the total number of records in the S_ON_EYE_CareCenter_Header.txt file.

IMPORTANT: If errors are encountered and you need to re-import the file, you must first delete the records from the S_ON_EYE_CARECENTER_S table; failure to do so will result in the generation of duplicate records.