This document describes how to prepare the data file for importing account information into Escape prior to startup. It can also be useful when converting a districts accounts to the new Standardized Account Code Structure (SACS). Some Purchasing customers may want to import updated account balances on a daily or weekly basis, so that users see account balances.
There are two ways to import accounts:
Both of these file layouts are described below.
TIP: We recommend that you place the import file on your work station or other local hard drive. This causes the error log to be written locally without having to write data to the network, substantially improving the speed of the import. After the import is complete you can copy the files back to the network for historical purposes.
Following is a description of the two supported file formats.
The following are the rules to follow when importing account data, using the standard format:
The following are the rules to follow when importing account data, using the customized format:
NOTE: For complete instructions on how to import accounts using a DEF file, see the Escape Setup & Maintenance Guide.
The following table lists the account fields imported when a standard format file is used. For importing account data using the standard format, you MUST use the format below. When using this standard format, no .DEF file is needed - in fact a .DEF file should NOT be specified when importing accounts!
For your convenience, we have a column designating the position number in the ASCII file and a column heading if you are creating or editing this file in Microsoft Excel.
| Field | Size | Pos Num | Excel Col Heading | Description |
|---|---|---|---|---|
| Trans Status | 1 | 1 | A | Leave this field blank |
| Organization ID | 6 | 2 | B | Use only if you are in a multi-organizational setting, like a county office. Otherwise, leave blank. |
| Fiscal Year | 4 | 8 | C | Fiscal year of the account being imported. Must be YYYY. Example: 2001 |
| Account Number | 60 | 12 | D | Left justified. It must match your account structure, including delimiters. |
| Long Description | 60 | 72 | E | Account description. If you are using auto-descriptions (automatic generation of account descriptions based on components), leave this field blank. |
| Account Balance | 14 | 132 | F | Current Account Balance. This field must be zero-filled. It has an implied decimal with two places. Use a leading sign if negative. For example, if you enter -0000012345678, this will result in -123456.78, and 00000012345678, this will result in 123456.78. |
| Budget Amt | 14 | 146 | G | Budget Amount. This field must be zero-filled. It has an implied decimal with two places. Use a leading sign if negative. For example, if you enter -0000012345678, this will result in -123456.78, and 00000012345678, this will result in 123456.78. |
| Rev Budget | 14 | 160 | H | Revised Budget Amount. This field must be zero-filled. It has an implied decimal with two places. Use a leading sign if negative. For example, if you enter -0000012345678, this will result in -123456.78, and 00000012345678, this will result in 123456.78. |
| Encumber Amt | 14 | 174 | I | Current Encumbrance Total. This field must be zero-filled. It has an implied decimal with two places. Use a leading sign if negative. For example, if you enter -0000012345678, this will result in -123456.78, and 00000012345678, this will result in 123456.78. |
| Expense Amt | 14 | 188 | J | Current Expensed Amount. This field must be zero-filled. It has an implied decimal with two places. Use a leading sign if negative. For example, if you enter -0000012345678, this will result in -123456.78, and 00000012345678, this will result in 123456.78. |
| Expired Date | 8 | 202 | K | Expiration date (YYYYMMDD). Leave blank if account is still active. |
| Short Description | 25 | 210 | L | Short Account Description |
| Lookup Account # to link to | 60 | 235 | M | Link new account to what old account. Left justified. |
| Lookup Fiscal Year | 4 | 295 | N | Fiscal year (YYYY) for Lookup Account # above |
NOTE: The only two required fields are Fiscal Year and Account Number. The Account Number must include delimiters.
ACCTSTD.IMP
The record contains Budget, Revised Budget, Encumbered, Expensed and Actual balances. Using this file as an example, you can build your own standard import file. You may wish to edit the sample file, change the account number to an account number that matches your district's account structure, then import it into your database as a test.
The following table lists the account fields available for import when a DEF file is used.
| Name | Data Type | Format | Field Desc |
| Account Link # | String(9) | DDDDDDDDD | Account Link Number |
| Fiscal Year | String(4) | YYYY | Fiscal year of the account being imported |
| Account Number | String(60) | see account structure | Account # (left justify) |
| Account Type | String(1) | See Note below. | |
| Restricted Flag | String(1) | Y or blank | blank |
| Short Description | String(25) | any character | Short Account Descrip. |
| Long Description | String(60) | any character | Long Account Descrip. |
| Account Key #1 | String(28) | blank | Account Key #1 |
| Account Key #2 | String(28) | blank | Account Key #2 |
| Account Key #3 | String(28) | blank | Account Key #3 |
| Account Key #4 | String(28) | blank | Account Key #4 |
| Account Key #5 | String(40) | blank | Account Key #5 |
| Account Key #6 | String(40) | blank | Account Key #6 |
| Budget Amt | String(15) | -DDDDDDDDDDD.dd | Budget Amount |
| Rev Budget | String(15) | -DDDDDDDDDDD.dd | Revised Budget Amount |
| Encumber Amt | String(15) | -DDDDDDDDDDD.dd | Current Encumbrance Total |
| Expense Amt | String(15) | -DDDDDDDDDDD.dd | Current Expensed Amount |
| Account Balance | String(15) | -DDDDDDDDDDD.dd | Current Account Balance |
| Budgeted FTE | String(9) | DDDDDD.ddd | Budgeted FTE |
| Actual FTE | String(9) | DDDDDD.ddd | Actual FTE |
| Custom Field #1 | String(10) | blank | blank |
| Custom Field #2 | String(10) | blank | blank |
| Last Edit Date | D10 | YYYY/MM/DD | Last Edited Date |
| Creation Date | D10 | YYYY/MM/DD | Date Account Created |
| Lookup Account # to link to | String(60) | see account structure | Link new account to what old account. |
| Trans Status | String(1) | blank | blank |
| Organization ID | String(6) | blank | Optional |
| Expired Date | String(8) | YYYYMMDD | Expiration date, leave blank if still active |
| Lookup Fiscal Year | String(4) | YYYY | Fiscal year for Lookup Account # above |
NOTE: The only two required fields are Fiscal Year and Account Number. The Account Number must include delimiters.
If the account type field is not imported or is imported with a blank, Escape will set the account type based on the account type records' object range. The valid Account Types are E=Expense, R=Revenue, A=Asset, B=Fund Balance, L=Liability, S=Beginning Fund Balance
When you import accounts that do not exist in Escape, they are created for you. A log file is created in the \ESCAPE\CHART folder and gives a summary of the date and time that the import began and ended, as well as any errors that occurred during the import process.
Below are two links to files that you may right click and download as samples. One is a sample data file and the other is a sample definition file.
ACCOUNTS.IMP This file is a sample account import data file with one record in it. The account balances are random dollar amounts for example use only. Right click and choose Save as to save to your PC.
ACCOUNTS.DEF This file is a sample account definition (DEF) file to go with the ACCOUNTS.IMP file above. Right click and choose Save as to save to your PC.
The sample data file contains 1 record with Budget, Revised Budget, Encumbered, Expensed and Actual balances. Using this file as an example, you can build your own import file to test. You may wish to edit the sample file, change the account number to an account number that matches your district's account structure, then import it into your database as a test.