How can we help?

[d] Importing Data to PowerSchool Learning from PowerSchool SIS

Follow

Looking for a better way to integrate with PowerSchool? Check out this article!

ATTENTION: Currently the Custom SQL Reports tab (sqlReports 4 tab) will not work for Powerschool version 7.8.0. (possible solution)

We have worked with several Schools using PowerSchool Learning and PowerSchool to develop reports that can be run inside PowerSchool to generate import data for PowerSchool Learning. To run these you will need to have access to the Custom SQL Reports tool, which is a PowerSchool customization.

These reports provide a good starting point for integrating PowerSchool with PowerSchool Learning, but you will need to be customized to your schools data and needs. Particularly, the Users report users the teacher id and student number for the PowerSchool Learning login. If you are using Google Apps Integration with PowerSchool Learning, or wish to use different data for PowerSchool Learning Learning Logins you will need to modify these reports. Please contact integrations@haikulearning.com if you need assistance.

To create these reports:

  1. Log into PowerSchool and open the System Reports tools.

    1._Start_Page.jpg

  2. Click on the Custom SQL Reports tab, and then click Import a new sqlReport.

    2._SQL_Reports.jpg

  3. Copy the text from the box labeled users.csv below, paste it into the Import sqlReport field, and click Submit. Repeat this for each of the 5 reports to be created.

    3._sqlReports_Import.jpg

  4. Once the reports are created your data can be exported. To do this, click on the name of a report to run it. When it is finished running click the CSV button at the top of the results to save it as a CSV file. Repeat this for the rest of the files.

    powerschool_export_csv.png

  5. In order to import this data to PowerSchool Learning you need to convert these XLS files to CSVs. Open each file in Excel and use the File :: Save As option. Choose CSV for the File Type. Save all five files to your and then zip them into an archive file that can be uploaded to your PowerSchool Learning Domain.

 

users.csv

<ReportName>Users</ReportName>
<ReportTitle>Users</ReportTitle>
<ReportGroup>PowerSchool Learning Export</ReportGroup>
<ReportDescription></ReportDescription>
<SQLQuery>select t.id, t.first_name, '', t.last_name, '', '', t.id, '', t.email_addr, 't', s.id, '1', t.id, t.email_addr from teachers t join schools s on t.schoolid = s.school_number where status = 1 union select st.student_number, st.first_name, '', st.last_name, '', '', st.student_number, '', '', 's', sc.id, '1', st.student_number, '' from students st join schools sc on st.schoolid = sc.school_number where st.enroll_status = 0;</SQLQuery>
<ReportHeader><th>import_id</th><th>first_name</th><th>middle_name</th><th>last_name</th><th>suffix</th><th>nickname</th><th>login</th><th>password</th><th>email</th><th>user_type</th><th>organziation_id</th><th>enabled</th><th>display_id</th><th>google_id</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>

 

classes.csv

<ReportName>Classes</ReportName>
<ReportTitle>Classes</ReportTitle>
<ReportGroup>PowerSchool Learning Export</ReportGroup>
<ReportDescription></ReportDescription>
<SQLQuery>select s.id, c.course_name, '', '', s.course_number, t.yearid, s.teacher, sc.id from sections s join courses c on s.course_number = c.course_number join teachers te on s.teacher = te.id join terms t on s.termid = t.id and s.schoolid = t.schoolid join schools sc on s.schoolid = sc.school_number where t.yearid = 24 and te.status = 1;</SQLQuery>
<ReportHeader><th>import_id</th><th>name</th><th>shortname</th><th>description</th><th>code</th><th>year</th><th>teacher_id</th><th>organization_id</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>

 

roster.csv

<ReportName>Roster</ReportName>
<ReportTitle>Roster</ReportTitle>
<ReportGroup>PowerSchool Learning Export</ReportGroup>
<ReportDescription></ReportDescription>
<SQLQuery>select cc.sectionid, st.student_number, 'S' from cc join students st on cc.studentid = st.id join sections s on cc.sectionid = s.id join terms t on s.termid = t.id and s.schoolid = t.schoolid where t.yearid = 24 and st.enroll_status = 0;</SQLQuery>
<ReportHeader><th>class_id</th><th>user_id</th><th>role</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>

 

organizations.csv

<ReportName>Organizations</ReportName>
<ReportTitle>Organization</ReportTitle>
<ReportGroup>PowerSchool Learning Export</ReportGroup>
<ReportDescription></ReportDescription>
<SQLQuery>select s.id, s.name, '' from schools s;</SQLQuery>
<ReportHeader><th>import_id</th><th>name</th><th>parent_org_id</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>

 

users_levels.csv

<ReportName>users_levels</ReportName>
<ReportTitle>users_levels</ReportTitle>
<ReportGroup>PowerSchool Learning Export</ReportGroup>
<ReportDescription></ReportDescription>
<SQLQuery>select student_number, grade_level from students where enroll_status = 0;</SQLQuery>
<ReportHeader><th>user_id</th><th>level_id</th></ReportHeader>
<CreateStudentSelection>0</CreateStudentSelection>
<StudentSelectionQuery></StudentSelectionQuery>
<IncludeRowNumber>0</IncludeRowNumber>
<OpeninNewWindow>0</OpeninNewWindow>
Powered by Zendesk