File Submission: Format Detail Rows from Exported Data (5 minutes)

Resources

• Sample Graduates File (XLS)
• Submit a Graduates File Guide (DocX)
• The Difference Between a Graduates File and a Request File
• Formatting and Submitting a Graduates File
• Common Graduates File Submission Errors

Transcript

Now that your file is ready to format, verify the column order matches the order in the graduates file submission guide. Also ensure the fields are in the correct column. For instance, student first names should be in column C, last names in column e, dates of birth and column I and so on. You may need to insert a blank column or two at the start of your file. To insert a blank column, right click the column letter and select insert. A new blank column will appear before the column you selected.

Column A should contain only the text PD3 in each detail row. The letters P and D should be capitalized. To do this quickly, type PD3 in column A on the first, second and third detail rows, highlight all three cells, then double click the bottom right of the set of cells.

For the social security number field in column B, if your technical person opted to not export Social Security numbers during the extract process, enter no SSN in all caps in each row. And you can type this in the first detail row and double click the bottom right of the cell to extend it to all detail rows. If Social Security numbers are included, ensure that all the social security numbers are formatted as text and any leading zeros are included. To format as text, highlight the entire column and select text from the format cells drop-down menu. For more information on this process, see graduates file formatting numbers as text in Excel.

Columns c, d and e which contain the first, middle and last names are all limited to 40 characters and should include only letters, spaces, apostrophes and hyphens. The last name should not include a suffix. If a suffix is used, put it in column f as letters only. No numbers or punctuation. If a name exceeds the 40 character limit, you can truncate the last name by removing spaces, apostrophes and hyphens or simply include the first 40 characters of the name.

Next, verify that the dates of birth in column I and the high school graduation date in column L are formatted correctly in the yyyy mm dd format. For example, if a student's birth date is June 1st 2005, it must be formatted as 20050601. And if a student's High School graduation date is June 1st 2022 it must be formatted as 20220601. For more information on formatting dates, see graduates file formatting dates in Excel.

Column J the students ID is not required but we recommend providing it if available. We will return this data to you in the student level detail report. The student ID can contain letters and/or numbers. If all student IDs at your institution contain letters, the entire column should already be formatted in text. However, if your student IDs have numerical values only, highlight the entire column and select text from the format cells drop-down menu. For more information on this process, see graduates file formatting numbers as text in Excel.

Column o is the six-digit ACT code for the students High School. To ensure any leading zeros for this column are kept, format the column as text. For more information on leading zeros, see graduates file formatting numbers as text in Excel.

Submitting the students demographic and academic data are optional but we recommend providing as much information as you can. Please make sure you submit the approved values per the instructions in the submit a graduates file guide. You'll receive StudentTracker outcome reports based on the data you provide. Columns s, t, u and v are all assessment scores and are optional fields. If you decide to include these in your file, all scores must be submitted on a one to three scale as follows:

• one does not meet standards,
• two proficient slash meets standards,
• three advances slash exceeds standards.

This may require some adjustments to your file as pulled from your SIS.

Finally, you should ensure all the detail rows have ED in all caps in column A. The code ED lets our system know where one record ends and the next one begins. Without it your file cannot be processed.

As a reminder .txt and .csv file types do not save formatting. Any formatting changes you will make will not be retained after closing and reopening the file. We recommend that you save a working version of your file as an Excel workbook so you don't need to reformat it every time you open the file. The final file submitted to Clearinghouse must be saved as text tab delimited txt. We've covered how to format your file join us next time to learn how to add the header row.