Importing Journal Entries
This document describes the procedures for creating a journal entry in Escape via the import process, which can be performed from the Escape Ledger module.
The import file uses a standard Escape format, described in a section below.
NOTE: Please contact Escape Customer Care for help in preparing the import file and running the import menu choice.
Journal Entry Import Rules
The following are the rules to follow when importing journal entry data, using either the standard or customized format:
- Fixed Length ASCII file, without spaces, commas or other delimiters between fields.
- All transactions from the input file must be the same date.
- Import data will be summarized by account number.
- The program prompts for a user comment, which is used in the JE header.
- All of the dollar amounts should use leading signs if negative and should not contain any
commas.
- The program prompts for which of the import dollar fields should be used; defaults can
be passed as a parameter.
- Journal entries for budget and revised budget will be sorted by account number.
- Journal entries for expenses, encumbrances, revenue and debits/credits will be sorted in
the same order as the input file.
- The default location for the import file is \escape\import.
- Account number must include any delimiters as established in Escape account structure.
- Errors are written to the \ledger\acthist.log file.
- Missing account numbers are written to the \ledger\missing.lst file.
Import File Layout
The following table lists the standard format for the file used.
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 Date |
8 |
1 |
A |
Transaction date with the format of YYYYMMDD. |
| Account Number |
60 |
9 |
B |
Up to 60 characters. It must match your account structure, including delimiters |
| Description |
30 |
69 |
C |
JE detail line description. You may fill this field with blanks if you do not want a description. |
| Expense Amt |
13 |
99 |
D |
Expensed Amount. This field must be right-justified, have a decimal with two places, and a leading sign if negative. For example, -123456.78. |
| Encumber Amt |
13 |
112 |
E |
Encumbrance Amount. This field must be right-justified, have a decimal with two places, and a leading sign if negative. For example, -123456.78. |
| Budget Amt |
13 |
125 |
F |
Adopted Budget Amount. This field must be right-justified, have a decimal with two places, and a leading sign if negative. For example, -123456.78. |
| Rev Budget |
13 |
138 |
G |
Revised Budget Amount. This field must be right-justified, have a decimal with two places, and a leading sign if negative. For example, -123456.78. |
| Revenue Amt |
13 |
151 |
H |
Revenue Amount. This field must be right-justified, have a decimal with two places, and a leading sign if negative. For example, -123456.78. |
| GL Acct Debit Amt |
13 |
164 |
I |
General Ledger Account Debit Amount. This field must be right-justified, have a decimal with two places, and a leading sign if negative. For example, -123456.78. |
| GL Acct Credit Amt |
13 |
177 |
J |
General Ledger Account Credit Amount. This field must be right-justified, have a decimal with two places, and a leading sign if negative. For example, -123456.78. |
Sample Import Data File
To download a sample standard format import data file containing 1 record, right click on the link and choose Save as. IMPORTJE.IMP (Notepad) or IMPORTJE.XLS (Excel)
The record contains a payroll adjustment journal entry with a transaction date of 10/31/2002, an account number, a JE detail line description and a negative expense amount. Using this file as an example, you can build your own 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.
How to Run the Import Process
There are two ways to run this process. It can be run using a batch file or you can run it from a menu choice in Escape.
From a Batch File
Below is an example of a batch file for the journal entry import:
ledger batch=#7008"PRHELWEL"#132"YYYYYYY;\escape\import\escapehw.txt;Health & Welfare;"#10#102#6#6
Where:
- Ledger is the name of the Escape module.
- Batch indicates that this is a batch process.
- #7008 is the procedure for adding a new journal entry.
- PRHELHEL is the journal entry type.
- #132 is the procedure for the Account history import.
- YYYYYY is the same as above.
- \ESCAPE\IMPORT\ESCAPEHW.TXT is the import file.
- Health and Welfare is the comment for the journal entry.
- #10 opens the journal entry.
- #102 balances and posts the journal entry.
- #6 exits you to the main menu of the Ledger module.
- #6 exits you to DOS.
From a Menu Choice
The menu choice to be used for this procedure is 7008, from the main menu of the Ledger module - LEDGER screen. The parameter is type #132, where type = the journal entry type defined in the Escape Journal Entry setup.
Other optional parameters that can be included are “YYYYYY”, where the Ys mark the items to update. The Ys are for Budget, Revised Budget, Encumbrances, Expenses, Revenues, GL Debits/Credits, in that order.
For example, a menu choice that creates a GENERAL journal entry and pre-fills Budget fields only would contain the following in the PARAMETER:
GENERAL#132"Y "
|
