Infoplex Ltd
home

Import/Post Financial Ledger Transactions - TRANS.csv format

The TRANS.CSV format has been in use with Sage (Line) 50 products since the 1990s and has undergone a fair amount of development over this period. Please note that the Cash Book Link module only supports postings to the TAS Cash Book using the traditional TAS MTADIF.DAT format. Unlike the MTADIF.DAT structure, where at least 2 records are required per journal transaction, one record per invoice can suffice for Sales & Purchase Ledger transactions. However, where analysis of the transaction is required covering more than one NL Account or VAT rate, a transaction can be described over multiple records.

Field/ColField Name/LabelData TypeMax SizeSample value 1Sample value 2Sample value 3TRANS.csv remarks
1TypeA2SISISAJournal Transaction type:
SI = sales invoice,
SC = sales credit,
SA = sales receipt,
PI = purchase invoice,
PC = purchase credit,
PA = purchase payment,
JD = NL Journal Debit,
JC = NL Journal Credit
2AccountA10CUST001CUST002CUST001Customer/Supplier Code - must exist in TAS Sales/Purchase Ledger; not required for NL and CB transactions, where any value is ignored. [Sage max is 8 characters]
3Nominal AccountN6320032001800Nominal Account Number. This, in conjunction with the Nominal Department value (field 4 below) must represent a valid record in the TAS Chart of Accounts. NB: must be digits for TAS. To analyse over multiple nominal accounts, use multiple records - see the example below. [Sage max is 8 alphanumeric characters]
4Nominal DepartmentA3100200100Nominal Department. This, in conjunction with the Nominal Account Number value (field 3 above) must represent a valid record in the TAS Chart of Accounts. NB: if this is blank (i.e. empty or space-filled) the default Department as set up in TAS, normally '100', is used instead. [Sage handles as an integer up to 999]
5DateA1020021821/02/201822/02/2018Document Reference date (e.g. Invoice date) AND Posting date, as DDMMYY; newer versions support DDMMYYYY (century ignored), DD/MM/YY, DD/MM/YYYY etc
6ReferenceA8145414551234Invoice/Credit Note Number/Journal document Reference (8 characters), or Payment Reference (Cheque Number, 6 characters). NB: see item 12 below for Payin Reference (Slip Number etc). [Sage max is 30]
7Description/DetailsA29SL Invoice (Sage)SL Invoice (Sage) Dept 200SL Payment (Receipt In)Description; blank defaults to Ledger (SL, PL etc) + Transaction Type description. [Sage max is 60 characters, was 30]
8NetN112004571950.7Net Amount in Base Currency (normally £ sterling for UK companies and Euros for companies in Ireland and other single currency countries); max 2 decimal places (will be rounded if more than 2 decimal places) and must be +positive. [Sage max is 8 + 2dp characters]
9Tax CodeA3T1T0Tax Code for VAT (per Sage); must be mapped to one of TAS VAT Rates (in range 1-20)
10VAT/TaxN11350VAT Amount in Base Currency (normally £ sterling for UK companies and Euros for companies in Ireland and other single currency countries) for the relevant Tax Code mapped to TAS VAT Rate; max 2 decimal places (will be rounded if more than 2 decimal places) and must be +positive. For transactions with multiple VAT rates see the VAT Calculation Example below. See also the section on the VAT Calculation Tolerance. [Sage max is 8 + 2dp characters]
11Exchange RateN111.956361Optional Extra field. Foreign Currency Exchange Rate with base currency. Unless the Foreign Currency is the Euro, it is the amount of Foreign Currency per 1 unit of Base Currency (e.g. USD 1.9563 per £1.00). Max 6 decimal places and, if used, must be > 0, e.g. 1.478534. For the Euro it is the inverse of the 'normal' rate, e.g. 0.689655 (i.e. 1 ÷ 1.45, where there are 1.45 Euros per £). For this field to be processed successfully, there must be a valid value in field 13. If blank the exchange rate for the specified FC Code in TAS Foreign Currency Rates is used. [Sage extended spec, max is 10 digits inc 2dp]
12Extra ReferenceA6PAYIN8Optional Extra field. Pay-In reference for Sales Ledger receipts and Purchase Ledger payments; used for reconciling bank statements. [Sage extended spec, allows 30 characters]
13Currency CodeA3USDOptional Extra field. ISO Currency Code, e.g. USD, EUR, GBP etc; must exist as the Currency Code in the TAS Currency Rates table. For this field to be processed successfully, there must also be a valid value in field 11. [Sage extended spec, normally this field is used by Sage for the User Name and its max is 32 characters].
Carriage Return (Hex = 0D, Dec = 13)
Line Feed (Hex = 0A, Dec = 10)

VAT Calculation Example

In this example, this is a UK company and TAS VAT Rates have been set up in the usual way, with mapping from the Sage VAT Rate codes, as follows:

Sage RateTAS Rate NumberName%
T11VAT Standard Rate20.0
T22VAT Lower Rate5.0
T03VAT Zero Rate0.0
T04VAT Exempt Rate0.0
T35Outwith the Scope of VAT0.0

On a Sales Ledger or Purchase Ledger invoice for a total of £130 (net of VAT), comprising an adult’s coat for £100 (net of VAT) and a child’s coat for £30 (net of VAT), Standard VAT is chargeable on the adult coat, but Zero Rate VAT on the child’s coat. Therefore 2 records are needed in the CSV file to analyse the breakdown of Net and VAT as follows (using a Sales Invoice)

Rec NoTypeAccountRefDateRate NoNetTax
1SICUST001149302/06/2016T1100.0026.00
2SICUST001149302/06/2016T030.000.00
Totals130.0026.00

As illustrated above, for the import program to be able to determine that both these records belong to the same transaction, the Type, Account, Ref and Date fields must be the same for both records.

Note that the VAT must be calculated separately for each record - you cannot simply take the total VAT and assign it to the first record. See also the section on the VAT Calculation Tolerance

VAT Calculation Tolerance

TASBooks uses a system of ‘VAT Calculation Tolerance’ to help control poor data entry. This system was originally developed between BASDA and the UK VAT authorities as an agreed standard. Transactions which contain VAT value(s) which is(/are) outwith preset limits also involve creation of exception reporting records which can be reviewed by VAT Inspectors (for fraud etc) if the business becomes subject to a VAT inspection.

The VAT Calculation Tolerance can be set by a supervisory user in the TAS main menu’s ‘Central’ function called ‘Maintain VAT Rates’ (program 031). The default tolerance is 0.50%. This can be varied up to 9999.0%, though such large tolerances are not recommended.

As a general rule, Infoplex transaction imports which involve VAT will reject transactions which are outwith the tolerance set up for the target business in its TAS dataset.




Last Updated: 21/02/2018

Posted to : Data Transfer File Specifications