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.

• Public Library – students who opt out are checked “No”

2. Under System Administration – Data Utilities – Data Extract Utility: Create an extract, input your desired Extract Instance Name, and fill out the following fields:

• File Name: DIS_
• File Extension: CSV (.csv)
• Column Delimiter: (CSV)
• Username: DIS
• Delivery Mode: SFTP
• Append Data to Filename: yyyymmddhhmmss
• Include header row?: Yes
• Include double quotes?: Yes
• SQL/Prism: (see step #3)

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.

1. Complete a Quick Export of students in PowerSchool with the “surround fields” box checked
2. Import txt file from PowerSchool into Excel
3. Remove data for opted-out students
4. Rename all columns in the file to the column names provided in the CSV template
5. Format DOB column to YYYY-MM-DD
6. Format phone number to 555-555-5555
7. Format grade column to GradePK, GradeK, Grade01 … Grade12
8. Name file DIS_YYYYMMDDHHMM.csv (replace DIS with correct district prefix)
9. Use third-party SFTP client* to add file to PINES SFTP

*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.

1. Open the file in Excel.
2. Highlight only the text columns.
3. Right click and go to: Format Cells → Tab: Number → Category: Custom
4. Paste the following into the Type field: \“@\”
5. Click OK.
6. Highlight only the DOB column.
7. Right click and go to: Format Cells → Tab: Number → Category: Date
8. Choose the YYYY-MM-DD option.
9. Click OK.
10. Click Save.
11. Open the .csv file with Notepad (or equivalent).
12. Replace all “”“ (triple quotes) with ” (single quote).
13. Save and add to SFTP.

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

• Change header names and order to match the CSV template
• Create custom field for opt-outs

2. Extract file to open in Microsoft Access; when exporting,

• Choose “include column headers”
• Choose “surround field values”

3. In Microsoft Access, create and employ SQL queries to

• Modify DOB formatting
• Modify integer for studentID (if applicable)
• Modify grade format

4. Export from Microsoft Access to PINES SFTP