File Specifications for General Ledger Import
The import file must conform to the following specifications:
- ASCII format
- Fixed- length fields
- 101 characters per record
- Records terminated by a carriage return or line feed
Each record has the following fields:
| Field | Start At | Length | Format | Optional? |
|---|---|---|---|---|
|
Branch |
1 |
4 |
Numeric |
|
|
Chart |
5 |
4 |
Numeric |
|
|
PostDate |
9 |
8 |
yyyymmdd |
|
|
Ref2 |
17 |
10 |
Alphanumeric |
Yes |
|
Ref3 |
27 |
10 |
Alphanumeric |
Yes |
|
CheckNum |
37 |
10 |
Alphanumeric Important: The width must be 10 for the check number but only the first 9 digits are imported. |
Yes |
|
ApDueDate |
47 |
8 |
yyyymmdd |
Yes |
|
Comment |
55 |
30 |
Alphanumeric |
Yes |
|
Amount |
85 |
17 |
Numeric |
|
Notes:
- The check number will be stored with padding zeros if it has all numeric characters. It does not have to be right-justified.
- The general ledger account (account branch plus chart number) must be a valid account in the Chart of Accounts. The branch must include leading zeros (for example, 0021).
- The Amount must be right-justified with decimal point and minus sign if negative. Positive amounts are imported as debits and negative amounts are imported as credits (regardless of the account's normal balance). You must enter a decimal point and cents (for example, 100.00, not 100).
- When importing the file, you can specify a default for Reference 2 and Reference 3. These values are stored for all transactions that do not have a value for those fields in the import file. For example, if you want PAYROLL stored as the Reference 2 on all imported transactions, you can specify that on the entry screen when you import.
Creating the General Ledger Import File
To create the general ledger import file, you can use a template as a starting point:
- Right-click the following link and save the template file to your computer: GLImportTemplate.xlsx.
-
Open the file.
A Microsoft Excel spreadsheet appears. The row across the top is the header row.
- In the first row below the header row, replace the provided template data with appropriate information for the first record of data to include in the import file. The information must comply with the file specifications indicated at the beginning of this topic.
- For each additional record, type the appropriate information in a separate row. (Be sure to replace or remove all row data provided in the template.)
-
When finished, remove the header row.
IMPORTANT: The general ledger import file must NOT include the header row.
- Save the file as a formatted text, space-delimited file with a .prn extension (for example, GLImportTemplate.prn).
- Rename the file as a text file (for example, GLImportTemplate.txt).
- To import the file into the general ledger, see General Ledger Import Procedure.
Sample File
The following is an example of an import file. The first line is given to assist you in viewing the character positions. (1 indicates position 10, 2 indicates position 20, and a plus sign indicates positions 5, 15, and so forth). If the lines wrap, expand the help window until they do not.
When imported into GlobalWare, the information displays as follows:
