Skip to content

Importing G/L transactions

Adapted from the Leahy Consulting technical notes "Specifications for the Macola® Progression G/L import ASCII text file" (December 2007), "How to import GL transactions into the Macola® ES software", "Importing GL transactions using a CSV file in Macola® ES" and "How to import and process GL transactions in Macola® ES".

Both generations of Macola® can post general ledger transactions from a file you create — payroll from an outside service, entries from a subsidiary system, month-end accruals kept in Excel. The two work very differently. Progression reads a fixed-width ASCII file with a fixed name. ES and 10 use the XML Server and accept CSV or XML, and need a header record as well as the detail lines.

Macola® ES and 10

Where the import lives

Go to System → Utilities → XML Server → Financial → Import → Financial Entries. Either CSV or XML format works.

During the import Macola® displays the number of records accepted and the number rejected. If records are rejected, click the note button next to Messages to see why. Correct the file and import again; a clean run shows zero rejected.

Processing what you imported

Imported entries are not posted. Go to G/L → Entries → Process. The transactions appear under the journal used on the import; zoom into the journal and the imported entries show with a package of origin of XML.

If nothing needs changing, process them as you would any other transaction. They then appear as processed transactions on the G/L account and on the customer or vendor cards.

To edit an entry before processing, highlight it and click the transaction button, select one of the entry lines and click Entry, then click Edit. Close the entry when you are done and process as normal.

Record layout

Every transaction needs one header record and one sub-line record per line. The header carries line number 0; sub-lines are numbered from 1. There is no limit on the number of lines, but the transaction must balance to zero or it will not import.

Header record

Field Value
Line Number 0 = header
Type of Journal M=General Journal. Others are B=Bank, I=Purchased, V=Sales, K=Cash
Journal Journal number in ES
Period Period number for the transaction
Financial Year Last two digits of the year — 2003 is 03
Entry Number Unique transaction number
Description Short description for the transaction

Sub-line record

Field Value
Number Line numbering starts at 1
Type of Journal As the header
Journal Journal number in ES
Financial Year Last two digits of the year
Financial Entry Same as the Entry Number in the header
Description Reference for the line
Date Transaction date
G/L account Main account number for the transaction
Amount Dollar amount. A credit is a negative number
CostCenter The account department number. Must be valid for the main G/L account
Cost Unit The account sub number. Must be valid for the main G/L account

These are the minimum fields for a general journal entry. Other transaction types need more, and depending on the accounts used you may also have to populate the customer (debnr) or vendor (crdnr) field.

CSV data types

A CSV file can only carry text values, and some of them have to be in a particular format to import as valid values.

Type Format
Text Text and numbers, left aligned
Numstr Numbers stored as strings, right aligned
Date Always DDMMYYYY — 29 August 2001 is 29082001
J/N or N/J Contains J (yes) or N (no) and nothing else, in upper case. The first letter is the default
Number Whole or decimal. The decimal separator is a full stop; negatives are preceded by a minus sign

Coming from Progression 7.x

The ES import needs more information than the 7.6 import did. These are the fields you already know, and what they became:

Progression 7.x Macola® ES
ACCOUNT MAIN NO General ledger account
ACCOUNT SUB NO Cost Unit
ACCOUNT DEPT NO Cost Center
TRX DATE Date
TRX SOURCE Header Description
TRX AMOUNT Amount — a credit is negative
TRX REFERENCE Line Description

Macola® Progression

File naming and location

The file must be called GLDISASC.DAT and must sit in the data file directory of the company that will use it.

How Macola® finds it

Choose Post from sub ledger under the G/L Processes menu. An option called ASCII interface appears alongside the normal subledger modules, and the file posts like any other subledger.

Record layout, version 7.5.102 and later

Field Length and format
ACCOUNT MAIN NO 8 chars, zero filled to the right
ACCOUNT SUB NO 8 chars, zero filled to the right
ACCOUNT DEPT NO 8 chars, zero filled to the right
TRX DATE YYYYMMDD
TRX SOURCE 10 chars — populate it
TRX AMOUNT 9(12)V99 with trailing +/− sign — 12 digits left of an implied decimal, 15 characters in total including the sign
TRX REFERENCE 30 chars — populate it
INTERFACE FLAG 1 char, Y or N
JOB NUMBER 12 chars, optional
ACCOUNT ID 12 chars, used only if Localization is in use; otherwise spaces
FILLER 26 blank spaces

The amount field is 15 bytes in three parts: the first 12 are whole numbers, the next 2 are cents with an implied decimal, and the last character is the sign. 00000000100015- is $1,000.15 credit; the same string with a + is $1,000.15 debit.

The interface flag tells the program whether a line has already been interfaced. Interfaced lines carry Y. New data must be set to N — it cannot be left blank.

Two things produce “Could Not Update ASCII File. Record Length Incorrect.”

Leaving the interface flag blank is one. The other is an alphanumeric account number formatted like a numeric one: alphanumeric accounts are left justified with spaces rather than trailing zeros, and leading zeros are wrong for the same reason. Correct the field and the data will post.

Posting, re-interfacing and validation

The file behaves like the distribution-to-G/L file of any subledger: the interface flag is set as it posts, and re-interfacing means setting the re-interface flag on the setup screen. It can interface to the General Journal transaction file or post directly into the general ledger. Interface rather than direct post — the data was created outside Progression, and interfacing gives you a chance to validate it before it lands.

Progression cannot report on, purge or maintain this file

There is no report against GLDISASC.DAT, no purge, and no rebuild/export/initialize/import maintenance. Deciding how to manage it is up to you, and so is agreeing with the provider of any third-party software — payroll, typically — on how the data will be extracted and how the file will be populated.

Decide the approach based on how often you will use it. For a one-off or occasional import, editing the file by hand to adjust the data and reset the interface flag is reasonable. For a regular feed of substantial volume, the choices are to edit the file each time, append to it each time — which makes it grow forever — or create a new file each time.

Tip

A simpler route for month-end journals. If the reason you are reading this is recurring journal entries kept in a spreadsheet, the Pulse Utilities General Ledger Journal Entry import validates account numbers against Macola® before importing, and reverses last month's accruals from the same worksheet. It works with Progression, ES and 10.

Support & contact

Our team is glad to help with anything from a quick question to a full implementation.