Page tree

 

 


 

 

Article ID: 2780

Accountants Office Suite
Australia
AO Classic General Ledger (AU)

ARTICLE LAST UPDATED: 07 August 2018 16:08:00 PM

MYOB General Ledger has automatic import routines for a wide variety of compatible software. To see what options are available check under the File menu > Import while a client ledger is open.

If you are unable to locate the program you want to import from, another option is to identify what export options are available in the source program that contains the data you want to bring into the MYOB Accountants Office ledger. For example, there may be an option to Save (i.e.Save As) or Export data from the program in a format that matches one of the import options, e.g. Comma or Tab-delimited Text (*.TXT or *.CSV), or dBase (*.DBF).

If neither of these options are suitable, then you may want to consider using a spreadsheet file.

To do this, you must first open the source program's data file and export transaction data to a format that can be opened in Microsoft Excel using the File > Save As... or Export command if it exists. The GL Import File Format table below describes the fields that can be imported, their field type, and size, etc.

Using the table below, rename the column headings in the Excel spreadsheet to match the field name required by MYOB (e.g. for the amount column, change the heading row to "ext_amt").

When you have changed the headings, save the spreadsheet as a comma delimited file (*.CSV) and import using the MYOB specified format option (File menu > Import > Other Cashbooks).

Optionally, save the attached GL import format.xls Excel spreadsheet to your computer by right-clicking on the link and choosing Save Target As...

This spreadsheet already contains these field names entered.

Check that Numeric fields (N) do not contain commas and that Date fields (D) do not contain spaces.
File format for importing transactions into AO General Ledger

Transactions can be imported into Accountants Office General Ledger from either a Comma Delimited or DBF (dBase) file. Both types of file have the same structure. Below is a list of data fields that can be used to import into Accountants Office General Ledger via either CSV or DBF. If you would like an MYOB Consultant to prepare the data to import for your practice, contact your Client Manager or submit a support request.

Field Name

Type 

Length

Decimals

Mandatory

Notes

Ext_AccNo

Varies

Yes 

Account Code. This size of the account code is configurable.

Ext_Date

Yes 

Date. DD/MM/YY or DD/MM/YYYY

Ext_Ref

12 

Yes 

Reference

Ext_Desc

Varies

Yes 

Description / Comment. The maximum length is configurable.

Ext_Amt

 

Yes 

Amount. This is the NET amount, excluding any GST (if applicable).

Ext_Qty

 

Yes 

Quantity

Ext_GSTAmt

 

Yes 

GST Amount (if applicable).

Ext_GSTAcc

 

Yes 

GST Account. Account to which GST portion should be posted. Must not be blank if GST amount is not zero.

Ext_Updt

Yes 

Set to FALSE to indicate transaction has not been posted to GL.

Ext_Perd

No 

Period. Format is YYYYMM. If left blank will calculated from Ext_Date.

Ext_Open

No 

Opening Balance flag. Set to any nonblank value if this is an Opening balance transaction. Trn_Perd must be the first period of the financial year.

Ext_Etype

No 

Split Transaction indicator.
'M' = Header record. It contains the Total amount of the transaction.
'S' = Detail record
Only used for Split Transactions.

Ext_SpCd

No 

Split Transaction reference. An arbitrary unique number that links the components of a Split Transaction together. Detail record.

Ext_Bank

Varies

No 

Account code for Bank Account.

Ext_Stat

No 

Status
'R' = Reconciled
'U' = Unreconciled
'X' = System generated contra transaction

Ext_TrType

No 

Transaction Type
'Q' = Cheque
'P' = Deposit
'J' = Journal
'D' = Debit
'C' = Credit

Ext_GSTInd

No 

GST Type indicator
'O' = Output tax
'I' = Input tax
'E' = Exempt
'Z' = Zero rated
'1' = Special GST Type 1
'2' = Special GST Type 2
'3' = Special GST Type 3
'4' = Special GST Type 4

Ext_Ncash

No 

TRUE if this is a 'Non Cash' transactions (eg. Opening Balance, Accrual).

Important information before importing
  • The total of the EXT_Amt + EXT_GSTAmt for all records with EXT_Etype <> "M", must equal zero. This may require a contra entry to be created to the Bank Account.
  • Credit values are negative numbers.
  • Non-mandatory fields may be omitted from the file if the features they support are not required. For example, if your process does not support Split Transactions, Opening Balances, Bank Reconciliation or Transaction Types, then you may omit all fields after EXT_Perd. As the comma delimited import process fields from left to right, you can only omit fields from the end of the record backwards.
  • If the General Ledger is configured to support Bank Reconciliation features, then Ext_Bank, Ext_Perd and Ext_TrType become mandatory.
  • The length of Account codes in EXT_AccNo, EXT_Bank, Ext_GSTAcc is dependant on Configuration options within the GL.
  • Split Transactions. These consist of a group of records - one Header (EXT_Etype = "M") and one or more Detail (EXT_Etype = "S"). They must obey the following rules:
    • All have the same value for EXT_Ref, Ext_SpCd., Ext_Date and Ext_Perd.
    • There must be one and only one record with an EXT_Etype = "M". All other records must have EXT_Etype = "S".
    • The values in the Header record must equal the sum of the values in the detail records.