Departmental Administrators - External Transaction File Creation


Audience

This is for departmental administrators wishing to submit transactions other than tuition and mandatory fees to student accounts.

Contents

First, request Approval to Submit Entries to a Student Account.

After that is approved, follow with the below file layout and data requirements to submit your transaction files.

Separating transactions

Each file must contain transactions of exactly one of the following types:

Reversals must be preceded by their original transactions, and the original must be sent at least one day prior to its reversal. Reversals must be for exactly the original amount. If for example you submitted a payment for $5000 on January 1, but want to decrease that to $4500, you must submit a file with a reversal of $5000 after January 1, then in a separate file add a payment for $4500.

To reverse more than one transaction that share the same student ID, term, and item type, separate the reversals into separate files.

We describe how to use Excel or Notepad to create the file in the correct format. If you have another tool to create files in fixed width format, skip to "Creating with Notepad", which gives the text file layout.

Creating with Excel

Download the Excel Macro-Enabled Worksheet.

If using Windows

  1. first enable macros for this to work (only necessary when you first download the file):
    1. Open Windows File Explorer and go to the folder where you saved the worksheet.
    2. Right-click the file and choose Properties from the context menu.
    3. At the bottom of the General tab, select the Unblock checkbox and select OK.
  2. When you get the Security Warning, Enable Content
  3. Save this file where you'll remember it, so you don't have to re-enable macros (above steps).

Column layout

Column Width Contents of header Contents of transactions
A 8 Number of transactions 8-digit student ID
B 2 Blanks Blanks

C

 12 Sum of all item amounts in the file. It should be positive for any original charges, payments or waivers. It should be negative for reversals of charges, but positive for reversals of payments or waivers. Item amount (if reversal, then make negative).
D 2 Blanks Blanks
E 12 Blanks Your assigned item type
F 2 Blanks Blanks
G 30 Blanks Optional reference number 
H 2 Blanks Blanks
I 4 Blanks 4-digit Term code 

 

Converting .xlsx to .dat

If you have a Windows computer, press the Export File button and name your file with a combination of alphanumeric characters and underscores or hyphens, and end with an extension of .dat.

If you have a Macintosh, follow these steps.

  1. Finder > Settings... > Show all filename extensions
  2. In Excel
    1. make sure the font size is 9 
    2.  Set Column Widths (right click the column header, then Column Width) as follows
      1. A: 8
      2. B, D, F, & H: 2
      3. C & E: 12
      4. G: 30
      5. I: 4
    3. save the file as Formatted Text (Space delimited) (*.prn)
  3. In Finder, rename the file from. prn to .dat, then when it asks "Are you sure you want to change the extension", click "Use .dat"
  4. Filename must be named with a combination of alphanumeric characters and underscores or hyphens, and end with an extension of .dat.
  5. Check the file by right-clicking then opening With TextEdit.app.

Creating with Notepad

Header

The first record is a header.

Start Pos

Length

Value

1

8

Count of transactions – can be left-justified or 0 filled

9

2

blanks

11

12

Sum of all item amounts in the file. It should be positive for any original charges, payments or waivers. It should be negative for reversals of charges, but positive for reversals of payments or waivers.

 

Transactions

Transactions follow the header.

Start Pos

Length

Value

1

8

8-digit student ID

9

2

blanks

11

12

Item amount (if reversal, then make negative).

23

2

blanks

25

12

Item Type

37

2

blanks

39

30

Optional reference number 

69

2

blanks

71

4

4-digit Term code - see knowledge article

Sample charge file
2              2284.00
12345678       1142.00  143200000420                                  2222
12345677       1142.00  143200000420                                  2222 

 

Sample charge reversal file
1             -1142.00
12345678      -1142.00  143200000420                                  2222 

 

Sample payment file
2              2284.00
12345677       1142.00  720120000000                                  2222 
12345678       1142.00  720120000000                                  2222 

 

Sample payment reversal file
1              2284.00
12345678      -2284.00  720120000000                                  2222 

 

Sample waiver file
2              2284.00
12345678       1142.00  743110010010                                  2222
12345677       1142.00  743110010010                                  2222

 

Sample waiver reversal file
1              2284.00
12345678      -2284.00  743110010010                                  2222

 

Filename

Filename must be named with a combination of alphanumeric characters and underscores or hyphens, and end with an extension of .dat instead of the usual .txt.

Submit

Upload your files to your assigned Box folder. At 9PM all valid files will be sent to AIS, and you will receive email the next day confirming receipt. Transactions will be posted after tuition calc starts for the transaction's term. The day after we post your transactions you will receive email confirmation / error listing. 

Deadline

Send files before the deadline listed in the Bursar Operational Calendar.