TRS of Illinois http://www.trsil.org
TEACHERS' RETIREMENT SYSTEM OF THE STATE OF ILLINOIS


Import File Specifications

The files specified below may be imported into the Annual Report to save data entry time. TRS strongly recommends that employers verify the accuracy of the data prior to completing and submitting the Annual Report.
The required fields are:

Field Name Field Type Comments
Social Security Number Number Up to 9 digits in length
Name Text Maximum 30 characters
Gender Text M or F
Date of Birth Text MMDDYYYY, MM-DD-YYYY, MM/DD/YYYY
Type of Employment Text F, P, S, H, or E
Number of Days in Employment Agreement Number Up to 3 digits in length
Total Number of Days Paid Number Up to 3 digits in length
Annual Salary Rate Number
Creditable Earnings Number
Member TRS Contributions Number
Federal Funds Number

The web ARS import system will accept the following file types:

  • Microsoft Excel Spreadsheet (XML format)
  • Microsoft Excel Spreadsheet (.xlsx format)
  • XML data
  • Tab-delimited data
  • Comma-delimited data
  • Fixed-length data

Microsoft Excel Spreadsheet (XML Format)

  • File extension: .xml
  • This file is derived from the spreadsheet TRS provides when choosing the “Create Spreadsheet File” on the Import/Recover/Delete screen.
  • When opening the file in Microsoft Excel, the data will be formatted as a table.
  • The first row of the spreadsheet must contain column headers. The data rows begin on row two.
  • Note, when adding a new row, you must start from the last row in the table and tab to the next row or perform a table row insert within the table. If you do not add the new row within the formatted table, it will not be imported to TRS.
  • For example:
example Excel picture

Excel Spreadsheet (.xlsx format)

  • File extension: .xlsx
  • When opening the file in Microsoft Excel, the data will be formatted as a table.
  • The first row of the spreadsheet must contain column headers. The data rows begin on row two.
  • Note, when adding a new row, you must start from the last row in the table and tab to the next row or perform a table row insert within the table. If you do not add the new row within the formatted table, it will not be imported to TRS.
  • For example:
example Excel picture

XML Data

  • File extension: .xml
  • This is a standard XML format. Note the names of the each of the nodes in the following example (from Notepad++, with Show All Characters enabled):
example XML code

Tab-Delimited

  • File extension: .tab or .txt
  • Do not include a row for column headings.
  • The quotes around the fields are optional.
  • Example(from Notepad++, with Show All Characters enabled):
example Notepad Plus Plus tab delimited

Comma-Delimited

  • File extension: .txt or .csv
  • Do not include a row for column headings.
  • If commasare part of a column’s data, the field must be enclosed with quotes.
  • Examples(from Notepad++,with Show All Characters enabled):
example Notepad Plus Plus comma delimited

Fixed-length

  • Text file:.txt
  • Must follow the following length specifications:
Data Name From Thru Size Comments
Letter Code 1 1 1 Letter "A"
Social Security Number 2 10 9  
Name 11 40 30  
Gender 41 41 1 M or F
Date of Birth 42 49 8 MMDDYYYY
Type of Employment 50 50 1 F, P, S, H, or E
Number of Days in Employment Agreement 51 53 3  
Total Number of Days Paid 54 56 3  
Annual Salary Rate 57 66 10  
Creditable Earnings 67 76 10  
Member TRS Contributions 77 86 10  
Federal Funds 87 96 10 Do not include cents and 96th character must be a period

  • All characters beyond the 96th character are truncated and not processed as part of the row.
  • Example (from Notepad++, with Show All Characters enabled):