Import File Records

Regulatory provides a data table for records about payments students made to a collection agency and received by an institution for past due tuition and related expenses. This table is used to import transactions that were recorded on a system other than Anthology Student. The imported collections records will be integrated into the batch for 1098-T processing as long as the batch is unlocked.

Collections transactions are imported via a comma-delimited file. You can use the attached template file Collections Import File Template.xlsx or create a file in Microsoft Excel with the following columns:

You cannot import collection records into a locked batch.

Column Column Header Format Required
A TaxReportingYear 4 numeric characters Yes
B TCC 5 characters Yes
C StudentNumber

Up to 10 characters

The Student Number must match the Student Number in Anthology Student and must be unique within the TCC.

 Yes
D TransactionNumber External System Transaction Number: Up to 5 numeric characters, no spaces No
E TransactionType

P - Payment

R - Refund

 Yes
F PaymentType

C - Cash

H - Check

R - Credit Card

E - EFT

N - Non-Cash

I   - ACH

 Yes
G TransactionDate mm/dd/yyyy OR yyyy-mm-yy Yes
H Reference Check Number or Reference Number: Up to 75 characters No
I Description Up to 100 characters (no commas) Yes
J Amount Between 0.00 and 100000.00 digits and decimal point only Yes
K IncludeSa1098T Include in 1098-T: Y or N Yes

Set Up the Import File

  1. Open Microsoft Excel and select Blank workbook or press Ctrl+N.

  2. In the first row of the spreadsheet, specify the column headers listed in the table above and shown in the image below (exact match, case sensitive).

    Collections Import

  3. Add the collections data to the Excel file (one record per row). Refer to the format requirements in the table above.

    • Note that some TCC numbers may be interpreted by Excel as scientific notation instead of numbers, e.g., “07E34” translates to “7.00E+34”. If this occurs, use the Format Cells option to format the TCC column as “Text” instead of “General” before entering the TCC number.

    • Do not use commas in the Excel file to prevent errors when the file is saved as a comma-delimited file.

  4. After you have added collections data to the spreadsheet, save it in Excel format (XLSX).

  5. Use the Save As option and select the file type as CSV (Comma-Delimited) (CSV).

    Use the CSV file as the import file. For more information, refer to Import Collections.

Regulatory:

  • Validates the imported CSV file and checks for errors. For example, if required information is not found, the record is rejected. A report is created listing each error and the record numbers in the .CSV file that contain the error.

  • Imports the .CSV file into the database when:

    • No errors are found in any of the records in the CSV file.

    • Every record in the import file contains a Student Number that can be matched with a Student Number in the Anthology Student database.

  • If any errors are found, then none of the records in the CSV file will be imported into the database. The import file will need to be corrected and imported again.

  • Loads the import file for an entire calendar year into the database.

If the imported data needs to be corrected or updated:

  1. Update the original Excel file (XLSX). Do not edit the comma-delimited file.

  2. Use the Save As option and select the file type as CSV (Comma-Delimited) (*.CSV).

  3. Re-import the updated .CSV file. Refer to Import Collections.

    Regulatory will delete all previously imported records from the database for that calendar year/TCC combination and load the records from the revised .CSV file into the database.

The database that stores external payments from students records the date/time when each record was imported and the name of the user who imported it. The Anthology Student database administrator can access the table used to store imported information about payments received from students that were imported from other systems.

The imported collections records will be integrated into the batch for 1098-T processing. After a 1098-T batch is locked, the collections transactions can no longer be modified.