Working With Gainful Employment Files – For Batch Submittal, .CSV Layout

January 2012

James Dergay, Consultant, The Higher Education Assistance Group, Inc. jdergay@heag.us

Many of us working at schools offering gainful employment programs have recently completed the first, annual painful employment; I mean gainful employment (GE) reporting requirements under the new federal regulations. Through trial and error, I learned a number of steps that could be helpful to anyone submitting their gainful employment data by batch file through their school’s SAIG TG mailbox. Here are the steps:

I. Working with the Gainful Employment (GE) Student Submittal File

Once you have logged on to the National Student Loan Database System (NSLDS) professional access website and notified them of your intent to use batch submittal, the first step is to build your student detail records in an Excel spreadsheet according to the layout explained in the “Gainful Employment User Guide”, Appendix A, page 33, which can be found on the IFAP website here: http://ifap.ed.gov/nsldsmaterials/NSLDSGainfulEmploymentUserGuide092211….. Name each column of your spreadsheet, left to right, according to the field names in the user guide, the first being, “record type”, followed by “award year,” so on, and so forth.

At this point you will have gathered the required student data from your school’s data base software using a query function (best case) or manually (worst case), and begun using the data to complete the detail records of your gainful employment report. By applying the excel auto-filter to the row of field names you can easily sort and update your detail records. This is especially helpful for school reporting on multiple gainful employment programs, or multiple award years (as was required this year). Always remember to save your work in excel.

Once the detail records are complete, it’s time to add the header and trailer records to the report. Adding the header record will require deleting the row of field names, and replacing it with the header record, formatted according to the GE user guide, Appendix A, page 32. In the row beneath your final detail record add the trailer record, the format for which can be found in Appendix A, page 35 of the GE user guide. The content of your report is now complete. In order to submit it through the SAIG mailbox, the report must now be saved in the comma separated value (.CSV) format. Before transmitting your report, view it in Notepad* in order to make sure there are no formatting errors. If you make changes save the file before closing Notepad. Common mistakes to watch out for include:

Header Record:

  • record type not in the “000” format.
  • submittal date not in the “CCYYMMDD” format.
  • school OPEI number missing a leading 0.
  • no space in the “filler” fields, field codes 801, 804, 807

Detail Records:

  • record type not in the “001” format.
  • award year not in the correct format (for example, the award year July 1, 2008 through June 30, 2009 should be formatted as “20082009.”
  • social security numbers missing leading zeros.
  • dates of birth and/or program dates not in the “CCYYMMDD” format.
  • school OPEI number missing a leading 0.
  • if program attendance status is “E”, program attendance end date not in the “00000000” format.
  • if program attendance status is “E”, data reported for “private loans amount” and/or “institutional financing amount.” These fields should be left blank.
  • credential level not in the “00” format.

Trailer Record:

  • record type not in the “999” format.
  • detail record count includes the header and trailer records (it shouldn’t).
  • no space in the “filler” fields, field codes 901, 903, 905

*Notepad may add unnecessary commas to the ends of the header and trailer records. When viewing the report in Notepad, scroll as far over to the right as possible. If you see commas hiding there delete them.

If there are errors in the header or trailer records, the report will not process. If you do not receive a response file within 24 hours of transmitting your file, you can safely assume that such an error has occurred. Review your header and trailer records again for a formatting mistake. If you are unable to identify any, call the NSLDS customer support center for assistance (800-999-8219, option 4).

II. Working with a Response File

If you receive a gainful employment response file within 24 hours, NSLDS has successfully processed your file. The response file includes a header and trailer record, as well as detail records containing errors that must be corrected. After transmitting your response file, open it in Notepad to view the detail records containing errors. If the response file only contains the header and trailer records, CONGRATULATIONS!/FAT CHANCE! – all of your detail records have successfully processed, and you are done! If it contains detail records, however, it means there are one or more errors in each of the detail records within the response file. By scrolling to the right you will see the error codes for each record in a six digit format such as “000,000”. The first three digits refer to the field code containing the error. The last three are the error code itself. Appendix A of the GE user guide includes descriptions of each error code.

To make your corrections, open the response file in a new Excel spreadsheet. The error codes will appear in columns to the right. Format these cells in the access format for clarity. Using the GE user guide you can now refer back to the detail records and make all the necessary corrections. Sorting with auto-filter can help with large files. Once you have made all your corrections, delete the columns containing the error codes and save your document as a .CSV file. In effect, you have created a new student submittal file. Prior to submission, be sure to follow all the formatting guidelines for the student submittal file, and the tips from part I above. Pay special attention to the header record, as the one you received in the response file is different from the one you return in a submittal file: the file name must equal COLLEGE STUDENT SUBMITTAL, and file type must be access. The process of submittal and response will be repeated until all your records have been successfully processed, and you have completed your gainful employment reporting for the year.

This new regulation represents a new responsibility, and more work, for many financial aid offices and staff. As with anything else, the best way to successfully administer this new responsibility is to roll up your sleeves and do the work. I hope you’ll let us know if these tips are helpful, as well as share any of your own. What have you learned? Is anyone working in formats other than the one covered here? I can be reached at jdergay@heag.us.