The information below provides a few routes for setting up student data file extracts using Infinite Campus and PowerSchool. Please note that these steps may not apply to all districts, as system processes and software may differ. It may be necessary to contact your SIS's support team for additional guidance. Infinite Campus's support has often provided assistance in creating files for districts.
The template for the student data file can be found here.
For the purpose of this example, “Public Library” is used for the extract name, and “DIS” stands for the district prefix. The extract name can be changed to your preference. Please update instances of DIS with your designated prefix.
1. Create a Custom Tab to capture data for students who opt out.
2. Under System Administration – Data Utilities – Data Extract Utility: Create an extract, input your desired Extract Instance Name, and fill out the following fields:
The SFTP information will be provided directly by PINES. For opt-out files, the file name should be DISOptOut_
3. Write your SQL script and paste in the SQL/Prism box.
Please note: Some fields will be different depending on School Number, Custom Tab Attributes, Student or Parent Email, etc. Each school district can alter the script as appropriate for their data.
SELECT s.number AS School_ID, p.personID AS Student_ID, i.firstName AS Student_FName, (isnull (i.middleName, ' ')) AS Student_MName, i.lastName AS Student_LName, CONVERT (VARCHAR, i.birthdate, 23) AS Student_DOB, ' ' AS Student_Phone, ct.email AS Student_Email, ma.addressLine1 AS Address_Street1,' ' AS Address_Street2, ma.city AS Address_City, (isnull (ma.county, ' ')) AS Address_County, ma.state AS Address_State, ma.zip AS Address_Postal, ma.guardianname AS Parent_Guardian, CASE WHEN e.grade = 'PK' THEN 'GradePK' WHEN e.grade = 'KK' THEN 'GradeK' WHEN e.grade = '01' THEN 'Grade01' WHEN e.grade = '02' THEN 'Grade02' WHEN e.grade = '03' THEN 'Grade03' WHEN e.grade = '04' THEN 'Grade04' WHEN e.grade = '05' THEN 'Grade05' WHEN e.grade = '06' THEN 'Grade06' WHEN e.grade = '07' THEN 'Grade07' WHEN e.grade = '08' THEN 'Grade08' WHEN e.grade = '09' THEN 'Grade09' WHEN e.grade = '10' THEN 'Grade10' WHEN e.grade = '11' THEN 'Grade11' WHEN e.grade = '12' THEN 'Grade12' END AS 'Grade' FROM Enrollment e (nolock) JOIN person p (nolock) ON p.personID = e.personID JOIN [IDENTITY] i (nolock)\\ ON i.identityID = p.currentIdentityID JOIN Calendar c (nolock) ON c.calendarID = e.calendarID JOIN school s (nolock) ON s.schoolID=c.schoolID JOIN SchoolYear Y ON y.active = 1 AND y.endyear = e.endYear LEFT JOIN contact ct ON ct.personID = e.personID OUTER apply(SELECT top 1 ma.* FROM v_MailingAddress ma (nolock) WHERE ma.personID = p.personID AND ma.personID = e.personID AND ISNULL(ma.secondary,0) = 0 ) ma LEFT JOIN CampusAttribute ca1963 ON ca1963.object = 'PublicLibrary' AND ca1963.attributeID = 1963 LEFT OUTER JOIN CustomStudent cs1963 ON cs1963.attributeID = ca1963.attributeID AND cs1963.personID = e.personID LEFT JOIN CampusAttribute ca1964 ON ca1964.object = 'PublicLibrary' AND ca1964.attributeID = 1964 LEFT OUTER JOIN CustomStudent cs1964 ON cs1964.attributeID = ca1964.attributeID AND cs1964.personID = e.personID WHERE e.enddate IS NULL AND s.number = 0195 AND cs1964.value IS NULL ORDER BY 2 ASC
Data extracts from PowerSchool require more manual intervention, as this software has formatting limitations (e.g., modifying field formats, updating headers, and exporting with quotes). Generally, the AutoSend function is unfortunately not an option, as it does not allow for changing of headers and field formatting.
The following processes were provided by various school district IT staff who created the files for the PLAY program. These may need to be adjusted accordingly based on your district's resources. The PINES PLAY program contact will assist in reviewing and resolving any issues with data files.
*Ex: CyberDuck, FileZilla, WinSCP
The two most common issues with the manual method is that the date of birth formatting and quoting of text fields can revert or not format properly when converting an Excel file to CSV. To confirm quotes and date of birth are correctly formatted, open the CSV file in Notepad or Text Edit.
Here is an example of what the data string should look like when opened as a txt file:
“999”,“123456”,“First”,“Middle”,“Last”,“2015-08-24”,“555-555-5555”,“parent@gmail.com”,“123 Street Road”,“Street2”,“City”,“County”,“GA”,“30006”,“Parent Name”,“Grade01”
Excel does not have a simple functionality to quote text fields. There is a workaround, but it tends to result in the CSV file having three sets of quotes instead of one. Here are the steps for the workaround. The DOB formatting should be changed last or it may get reverted to MM/DD/YYYY once saved as a CSV file.
This process involves programming knowledge and connecting to the Open Database Connectivity (ODBC) interface by Microsoft.
1. In PowerSchool, use the Data Export Manager to
2. Extract file to open in Microsoft Access; when exporting,
3. In Microsoft Access, create and employ SQL queries to
4. Export from Microsoft Access to PINES SFTP