Check Your PDP Files for Submission (4 minutes)
After pulling your data from your SIS or data lake, learn to prepare and review the PDP file for upload to the Clearinghouse.
Resource
Video Transcript
[Nick]: Welcome back to Behind the Postsecondary Data Partnership Submission. If you've been following along, you've already identified your data elements and figured out where your data lives. Today, we're focusing on file formats, specifically the course file.
[Screen shows dataset.csv file in Notepad++. It has two rows: the course header and a student data row. The student data row starts with 2010-11.]
[Vijaya]: Right, at this stage, most institutions and systems have pulled their student data into course and cohort CSV files using scripts and are ready to review their files. Quick tip: to review your files, use Notepad or Notepad++. Excel can strip leading zeros or make other changes you don't want.
[The student data row is highlighted in light gray.]
[Nick]: So, you've opened your course and cohort files. First step, review the student data detail rows. D1 needs to be at the beginning of every detail row. D1 tells the system it's a data row.
[Blue lower-third with white text: to add D1 to all detail rows, highlight all rows then open find and replace (Ctrl +H). The replace popup window appears. In find what add *, in replace with field add D1, Regular expression is checked and replace all is selected. Additional popup confirms that all occurrences were replaced. The ok button is selected.
Screen returns to the file and D1 is at the start of the student data row in bold. Blue lower-third with white text: column order needs to match the same order as the column header.]
[Vijaya]: In addition to D1 at the start of the row, the column order must follow the sequence outlined in the file submission section of the PDP knowledge base and must match the order of the column header row.
[Blue lower-third with white text: Dates need to be in YYYYMMDD. In the data row, four dates are highlighted in orange.]
Also, dates must be in YYYYMMDD format.
[The student data row is no longer highlighted and the column header row is highlighted with light gray. CH1 at the beginning of the row is highlighted in orange.]
[Nick]: Next, the column header row is above the detail rows. It starts with CH1.
[Lower-third includes the Data Files template page of the knowledge base.]
The order is the same as your student data rows and the names must match the data file templates exactly. They are case sensitive and need to be spelled correctly.
[A new line is added to the beginning of the file. DCE02,10027795,004781,00,2.0,,,,20171030,,TEST2010-11,,,,]
[Vijaya]: Above that is the header record. It identifies your organization and file type. Here's what goes in. Record type. The record type depends on the file you are submitting. Cohort is DCE01. Course is DCE02. Financial aid is DCE03. Service account number. Your up to 15-digit account number was provided during onboarding. Lost it? Reach out to your account manager. Organization ID. Six-digit OPEID for institutions; Org ID for systems. Branch code two digits. Use 00 if none. Version 2.0. Leave the filler fields blank. File certified date is the file submission date. Client file ID is a unique identifier up to 50 characters. Usually it is the internal system ID for your file.
[A row is added at the bottom of the file. T1,4 The line number and the number 4 in the row are highlighted.]
[Nick]: Finally, the trailer row at the bottom has two columns T1 and then the total number of rows including the trailer row itself. Don't forget this as it signals the end of the file.
[Vijaya]: Before uploading, double check the file is in order: header, column header, detail rows, trailer. All fields are under 255 characters.
[In the column header, CH1, Cohort Term, Academic Year, Term, Institution ID Type, Institution ID, First Name, Last Name, Current Street 1, Current City, Current Country, Date of Birth, CompleteDevMath, CompleteDevEnglish, Degree Type Sought, Semester GPA, Course Prefix, Course Number, Section ID, Course Name, Course...Type, MathorEnglish Gateway, Course Begin Date, Course End Date, Grade, Number of Credits Attempted, Number of Credits Earned, Purpose of course exchange are highlighted in orange. SSN has an orange dotted outline around it.
The corresponding fields in the student data row are highlighted. Both the SSN field and the student id field have a dotted line around them.]
Required fields are populated and do not have blank or null values. If allowed, use missing or UK for unknown values like gateway status or CIP code. The file is saved as .TXT or .CSV.
[Computer Monitor showing the Submitting Data section page. There's a data submission tools section that an includes a link to the PDP Mapping Tool.]
[Nick]: And always refer to the submitting data section of the knowledge base. Specifically, the PDP mapping tool.
[Email draft to Vijaya with a subject line of PDP Access Request. It states that they are getting ready to submit data, will be reviewing the PDP dashboards and Analysis ready file. It asks for permissions to be setup and share the FTP access information.]
When you're ready to submit your data, reach out to your user administrator to ensure that you have access to PDP file submission and have the credentials for the PDP FTP account.
[Screenshot of the Upload & Submit Your PDP files video thumbnail.]
Then watch our upload and submit PDP files video and try the error handling exercise.
Thanks for sharing these insights, Vijaya. As you're preparing for your first PDP submission, remember errors will happen and every school has a learning curve. You got this. And your Clearinghouse account manager is here to assist if you run into questions.
