NSLC Report Submission

The purpose of this process is to showcase how members of the Registrar’s office is to import the data from CAMS, the current Student Information System (SIS), then fix any mistakes and verify it in Excel before sending to the report to the National Student Loan Clearinghouse (NSLC). This report is essential to accurate recordkeeping of student and alumni credentials, as well as following federal regulations we are inclined to meet as an accredited public university. Security access to the report is managed by a user’s Active Directory membership in the group, CAMSReports-RegistrarFunctions. Other members in this group will have more knowledge on any issues faced during this process.

Step 1. Using the Microsoft Edge browser, login to CAMS with your credentials. Next, select the Registrar option on the menu. The next menu after that, you will select Registrar Functions and click on the item, ‘NSLC Report’. This should be what you see on your screen now.

Step 2. Click the ‘Term’ dropdown and select the appropriate semester you wish to report on (We’ll use the Fall 2023 term [FA 2023] as an example). Next, click the ‘<Select a Value>’ dropdown and select the appropriate option (We’ll use Subsequent-of-Term as an example).

Step 3. Then, click the View Report button on the right-hand side.

Step 4. After the NSLC Report is generated, a new toolbar will pop up. Click the Save button and select the Excel option. This is so you can keep the leading zeros in certain cells.

Step 5. Open File Explorer on your PC and go to your Downloads folder. Look for your most recently downloaded Excel file (ending in .xlsx) and open the NSLC Report in Microsoft Excel. Once it’s open, click on the first column, ‘Student ID’. Hold down Ctrl on your keyboard and press the A key twice. Your entire sheet is highlighted now. Input Ctrl + C to copy the data.

Step 6. Create a new sheet by clicking the ‘+’ button on the bottom left of the screen.

Step 7. Select the top-left most cell in the new sheet and right-click. Then, select the Paste Values option.

Step 8. Remove all the special characters you find in the address columns.

Step 9. Use this linked Excel file, NSLC Report Test, and the latest linked Enrollment Reporting Programming & Testing Guide to refine the new sheet you made with all the data you copied over. The Color-Coding Key in the Excel file is how you should edit the report you are editing. The next steps will help you make the right adjustments. Here is further elaboration on the highlighted columns:

Step 10. Below is a list of the columns that should be _deleted_ so you can identify them more easily. Click on the very first cell on Row 1 of the first column named, delete the data in that cell, and press the right arrow key

on your keyboard until you get to the next cell of the second column mentioned. Delete that data as well and repeat the same process for the next columns.

Step 11. Once finished with erasing the data on Step 10, click on Row 1 on the left part of the screen and press Ctrl + G on your keyboard.

Step 12. Click the Special… button and select the Blanks option then press OK. Right-click the first cell next to Row 1 and delete the entire column. All the columns needed to be deleted will be gone. Double check the updated sheet for verification.

Step 13. Remove extra spaces in cells by using =trim() function in each yellow highlighted column mentioning it. Create a new column next to the column needing the trim. When writing out the Trim function, put the exact cell at the top of the column you’re correcting in between the parentheses ( ). In this case, it is cell J2 that has additional spaces which we’re trimming. We are typing ‘=trim(J2)’ in the next cell to the right, K2. Click, hold, and drag from the bottom right corner of the cell you just trimmed with your cursor as a black crosshair. Drag it all the way down until it reaches the last cell adjacent to the end of column J. It will automatically trim unnecessary spaces in your new column.

Step 14. Click on column K to select the entire column of data. Press Ctrl + C to copy it.

Step 15. Right-click column J and select the Paste Values option. Then delete column K. Delete the : Trim text at the top of the column which are the editing instructions. Repeat this process for all columns highlighted yellow with “Trim” in them.

Step 16. Find the column, Prog2CIPCode (in this case it’s column AV) and click where it specifies the column. Press Ctrl + Shift + Right Arrow Key (

) to highlight the remaining columns.

Step 17. Press Ctrl + F to use the Find and Replace function. Press Space once in the Find text box. Go to the next tab, Replace.

Step 18. Make sure the Replace with: text box is empty. Select Replace All. Allow Excel to fully perform the command and then the extra spaces should be deleted from all the columns.

Step 19. Return to CAMS and run the Student Term Status Report to retrieve certificate students. Filter the results by Student Level of Certificates and Cohort Group of Internal Transfer. Create a new sheet on your NSLC Report File (see Step 6). Copy the certificate student data and paste the values on the new sheet you just made.

Certificate information is as follows:

Step 20. The Begin Date is found on Student Term Status Report as Entrance Term and Status Dates correspond with Major 1 Dates.

Step 21. This is a demonstration of the VLOOKUP tool in Excel:

When you input =VLOOKUP(12345,A1:B7,2,FALSE) into a blank cell in this sheet below, it automatically becomes Florida according to the data.

So instead of scrolling through various columns and rows, use the VLOOKUP Tool to identify the values you’re looking for based on the values you already know.

Utilize the VLOOKUP Tool to determine who the Certificate Students are. Once designated, use CAMS to enter the appropriate data in the Program 2 columns, which are seen in Step 19.

Step 22. Locate the correct data in CAMS and input it in the columns below (they are the same value):

Enter ‘Coding for Data Analytics’ for the correct student in Second Major (Column X)

Step 23. Click the Data tab at the top of the menu and then click the Filter button.

Step 24. Find columns Prog1BeginDate (Column AR) and Prog1EnrollmentStatusEffectiveDate (Column AU). Click the dropdown at Prog1BeginDate (Column AR).

Step 25. Select the Date Filters option. Select Custom Filter at the bottom of the next list.

Step 26. Change the left dropdown to does not equal and the right dropdown to the first day of classes of the term you selected when generating the original report from CAMS (in this case, we will use Fall 2023 term’s first day of classes, 8/22/23 [August 22, 2023]). The leftover data in the columns Prog1BeginDate (Column AR) and Prog1EnrollmentStatusEffectiveDate (Column AU) needs to be matching. If they are not, the data needs to be corrected to the earlier date of the two based on each student.

Step 27. Utilize the text file from the drive, M:\Registrar\NSC Upload Files_Secured, to retrieve the header and footer you will use for the report. Utilize the NSLC Guide for formatting cues.

Step 28. Once the footer and header have been successfully added, copy the entire sheet (see Step 5) and paste the entire document as text only (see Step 7) to be certain all formatting was removed. Find and replace all apostrophes (‘) (see Steps 16 -18) to hold leading zeroes.

Step 29. Click File in the top left of your screen. Then, click Save As… or Save a Copy. Name your NSLC Report appropriately and change the file type to Text (Tab delimited) (*.txt). Then, save your file. Login to the National Student Clearinghouse and upload the file you just saved correctly.