Import Template Spreadsheets

When importing data it is good practice to start with a fresh copy of the required template from the central Merge Template repository with each new upload. These spreadsheets are updated with every new release and will contain the correct column headers which are necessary to successfully import the data because the application relies on the exact name contained in each column title.

If a column is removed from an import template the application ignores it and doesn't attempt to import any values for this field (so you can delete columns or leave them blank). Conversely if a new column is created by a user with a name that the application isn't expecting any data entered in that column will be ignored on import.

To import transaction data

1. From the job toolbar > Add or Import Data > Import From File > Import Transactions.
2. Locate the import spreadsheets

Download all formats here

Spreadsheets are available for download here or you can download the specific spreadsheet you need from the links below

Note

• You are unable to import Secured Creditors because they need to be linked to an Asset.
• Changes to the importing process (including Account Payables, Account Receivables, including FX imports and bulk import for large data sets, were introduced in the May 2020 update and are detailed below.

Payroll 

Download (Sample) Transactions Pay Payroll.xlsx here

When importing payroll transactions, if a transaction group relates to a single employee and that employee has EFT details entered, the payment transaction will be created as an EFT payment using the employee's details. 

Accounts Payable Import

Download (Sample) Transactions Import Accounts Payable.xlsx here

The transaction import now supports the importing of accounts payable ("AP").   

Counterparty Lookup ID

The coding line in the spreadsheet that relates to the AP account must either specify an existing Counterparty ID to link the AP to, or alternatively specify a Lookup ID that references counterparty information on the second sheet of the import spreadsheet (see Figure 1 under Accounts Receivable Receipts below). 

All counterparties that are imported with the AP transactions will pass through the standard counterparty matching routine. Counterparty details are separated on a separate sheet to preserve a single common lookup and ensure unique counterparty records are created by the import. 

The above spreadsheet highlights the header rows that reference the AP account and illustrate where to place the Lookup ID or the Counterparty ID.

Counterparty - Sheet 2

The counterparty Sheet 2 contains many columns representing the counterparty data:

Similarly, if you are matching to an existing counterparty via the Counterparty ID (entered on sheet one) and that counterparty has BPAY details entered in their Banking & Advanced tab then the Account Payable will be created as a BPAY invoice as will the Payment of the invoice.

BPAY

If you are using the Lookup ID and the Counterparty details in Sheet 2 of the (Sample) Transactions Import Accounts Payable, you may use an EFT Method of BPAY then the Account Payable will be created as a BPAY invoice and the payment of that invoice will be prepared as a BPAY.

EFT or other bank details

The same applies for EFT payments when EFT is entered as the EFT Method or the existing counterparty has EFT details entered in their Banking & Advanced tab.

Accounts Payable Payments

Download (Sample) Transactions Pay Accounts Payable.xlsx here

When importing accounts payable payment transactions, the transaction payment will be created as an EFT payment using the EFT method specified for the counterparty if that information is entered.   

When importing a Payments AP the only way to link the transaction to the AP is to enter the Transaction Id of the invoice.

Accounts Receivable Import

Download (Sample) Transactions Import Accounts Receivable (Post Appointment).xlsx for AUD debtors here

Download (Sample) Transactions Import Accounts Receivable (FX)(Post Appt).xlsx for Foreign currency debtors here

The transaction import can now import accounts receivable ("AR"), both pre and post appointment as well as foreign currency.  The coding line in the spreadsheet that relates to the AR account must either specify an existing Counterparty ID to link the AR to, or alternatively specify a Lookup ID that references counterparty information on the second sheet of the import spreadsheet (see Figure 1 below).   All counterparties that are imported with the AR transactions will pass through the standard counterparty matching routine.  

Accounts Receivable Receipts

Download (Sample) Transactions Receipt Accounts Receivable.xlsx for AUD debtors receipts here

Download (Sample) Transactions Receipt Accounts Receivable (FX).xlsx for foreign currency debtor receipts here

The transaction import now allows the importing of receipts against both native and foreign currency accounts receivable ("AR").  As previously the coding line in the spreadsheet that relates to the AR must contain a Trans ID that identifies the capture for the AR.  If the AR is foreign currency then the coding line in the spreadsheet that relates to the AR account must specify a FX rate. 

Figure 1.  On the AP or AR coding line, either enter a Counterparty ID or a Lookup ID relating to the Lookup ID in Sheet 2

Locating the Counterparty ID.

Exporting the following job reports in raw will report the counterparty ID

• Aged payables summary and detail (as counterpartyID)
• Supplier transaction detail (As supplierID)
• Creditor Listing report (As CounterpartyID)

OR you can export in raw the Counterparty list from Practice Reports. This list will contain all the counterparties in the practice.

To import debtors:

1. Add or Import Data > Import From File > Import Debtors

Download (Blank) Debtors.xlsx for AUD debtors here

Download (Sample) Import Debtors FX.xlsx for foreign currency debtors here

The debtor import spreadsheets now support importing home and foreign currency debtors.  If the debtor is in foreign currency, the import line must specify a FX rate. 

Using the debtor import spreadsheets is the simplest way to import debtors where posting to only a single revenue contra account (eg: sales)  is required.  If posting to multiple revenue contra accounts, the accounts receivable import transactions spreadsheet should be used as detailed above.  Pre-appointment debtors are only ever posted to a single contra account so this is the preferred option.   

Creditor Import Spreadsheets

If a payment amount is specified in a creditor import spreadsheet and the creditor has EFT details entered into the spreadsheet then the payment transactions created will use the creditor's EFT details.

Creditor payments

You may use the custom creditor import format to create payments to creditors. Populate these columns to create payment entries:

• Payment Amount
• Payment Date
• Cheque Number

Custom Import Routines

The application now supports custom data imports. 

These are routines that can be developed and shipped to clients without requiring an installation of the application user interface.  Currently there is a bulk creditor import routine.  Additional import routines may be requested by clients according to their requirements.

To import custom data

1. From the job or practice toolbar select Add or Import Data > Import From File > Import Custom Data.

Creditor Bulk Import

This custom routine allows the rapid importing of large numbers of creditors.  The format is the same as the standard creditor import spreadsheet and payment amounts are also supported.  This process skips the counterparty matching process and is intended to be used where large numbers of creditors (5000+) need to be imported quickly.  It is recommended that the normal creditor import routine is used in all other circumstances.   Note that the bulk import routine does not permit the re-importing of data exported from the creditor register.  If you want to export, amend and re-import creditor data, please use the standard creditor import procedure. Further instructions on the bulk import routine can be found in this article 

Other standard Import Spreadsheets, that are also shipped with upgrades, can be accessed from this area are:

Download (Blank) Assets.xlsx and (Sample) Assets here

Download (Blank) Creditors.xlsx and (Sample) Creditors.xlsx here

Download (Blank) Debtors.xlsx and (Sample) Debtors.xlsx here

Download (Blank) Employees.xlsx and (Sample) Employees.xlsx here

Download (Blank) Shareholders.xlsx and (Sample) Shareholders.xlsx here

Download (Blank) Transactions.xlsx here  and (Sample) Transactions.xlsx here  .Instructions on importing transactions and wages can be found in this article Importing Transactions (including wages)