This article explains how import delimited, or fixed length, text file into Sage 50, using PostTrans which has the following advantages:

• Interactive Data Validation
• Optional Confirmation before import
• Print or e-mail transactions after import PRO
• Import SINGLE file or ALL files from directory
• Archive file to processed directory after successful import, so use only new files which have not been imported
• Run batch file before selecting file
• Easy to use interface for end users, simply press the File button
• Multi Currency PRO
• Data Transforms – Use simple Excel formula to manipulate the data before import, example below.
• Fix Rounding Issues – Can automatically round Up/Down a Transaction to match Payment Amount, or values specified in tag THRoundToGross
• Add additional Items in same row – Add additional items, such as carriag
• Negative Invoice Lines – From sage 50 2017 V24.1

Included are three examples of Import:

Import Transactions by specifying (Unit Price - Line Discount) * Qty to calculate totals

Examples : “Import File:Simple Multiple Orders from CSV”, “Import File:Customer and Multiple Orders from CSV” and “Import File:Multiple Orders from CSV with Currency PRO”

These three examples a covered in first section of this article, in which we specify the Qty and Unit Price and let PostTrans calculate the Transaction Totals.  Similar to manual entry in Sage 50.  If don’t specify the Unit price PostTrans will lookup the price based on Product Price and Customer Discounts, as if entered in Sage 50.

Import Transactions by specifying Payment/Line Total to calculate Line Unit prices

Example “Import File:Orders from CSV with Payment, Rounding and Extra Lines”

Shows how we can import data from a Web Cart system.   The CSV file contains Account, Product Code, Qty and Total Payment for each line.   PostTrans  then calculates the Tax and Nett from the total line value and records the total payment.  It overcomes a common problem where due to external system not using “Bankers” rounding, the Line Totals do not agree with Total Transaction Payment.

This example is in Import Sage 50 Transactions by specifying Payment/Line Total to calculate Line Unit prices

Demo Video

 

 

 

Importing a delimited file into Sage 50

A delimited file can easily be imported.

Included Example CSV file and template:

1. Press Help button on the PostTrans toolbar to access examples and type file in search box.
2. Select either “Import File:Customer and Multiple Orders from CSV” or “Import File:Multiple Orders from CSV with Currency PRO” (includes currency in two extra columns).   Note: example “Import File:Simple Orders from CSV” shows a similar example but with minimum of columns.
3. Then import the example customers in the “Customer” sheet
4. Next select “Order CSV”, and press File button on toolbar.
5. Select “import_data_CSV_for_Invoices_with_Currency.CSV”
6. The sheet will then be filled with data, as explained below.

Example CSV file:

The file contains several Invoice and Credit transactions, demonstrating several methods of import:

which represents 4 invoices and 3 Credit transactions demonstrating several different import principles:

So in Excel we press File button, on the PostTrans toolbar, we get:

When the Import button is pressed, PostTrans scans down the sheet, reading each line, validating the data as it goes.

Transaction Header

In the above example we have Account, Transaction date, Transaction Type and Cust Ref in columns B, C, D and E, row 28.  See Sage 50 Tag Table

So column E is imported into Customer Order No:

If we delete the tag in cell E28, and replace it with THOrderNo, using the Tags button, it is imported as Order No which is 7 chars long:

What causes a new transaction to start?

Changes in Account, Transaction date, Transaction Type , or Cust Ref will start a new Transaction. These Transaction Header values are just repeated in the file. Thus, account SI-1 will have a 4-line Sales Invoice created.

Transaction Types

If column D (Transaction Type) has ‘SO’ in it then it will be imported as a Sales Order.   Transaction Types are SQ,Sales Quote,SO,Sales Order,SI,Sales Invoice,SSC,Sales Service Credit Note,SSI,Sales Service Invoice,SC,Sales Credit Note,PO,Purchase Order,PI,Purchase Batch Inv,PC,Purchase Credit. Fields not appropriate for a particular type of transaction will be ignored.

Add extra columns

You can add, Delivery Address lines 1-4 and Postcode if required.  Move to appropriate column, and simply press Tags button, on PostTrans toolbar, to access the list of Tags. Add Tags THDelName,THDelAdd1-4 and THDelPostcode.

See tags beginning with TH in table Sage 50 Tag Table for full list.

Data Transforms

The data can easily be transformed while importing by adding formula to the template using the empty columns to the right of the data.  The formula is copied from the delete row 27, which is then copied down the sheet every time the Clear or File button is used.  More info copy down formula

Example – External Reference to Order No:

Above we discussed placing the External Transaction ref in the “Order No” field in Sage 50.  Using a formula we can extract the last 7 digits of that and map it to the “Order No” field using tag THOrderNO in row 28.

To get the last 7 chars we use =MID(E30,4,7), where E30 is the External Transaction Ref.

Using this technique we can recalculate and process the data very easily before import.  You can even write your own functions to manipulate the data, see http://www.wikihow.com/Create-a-User-Defined-Function-in-Microsoft-Excel.

Transaction Lines

In our example the lines are expressed in columns F to M.

The Tags in row 20 designate to PostTrans the data required in the columns below. A list of Transaction Lines, beginning with “TL”, can be added by simply pressing the Tags button on the PostTrans toolbar. See Sage 50 Tag Table

Product Transactions SI-1

Depending on a system setting the Product description is read from Sage (default setting). If not included Product Unit Price, Tax code, Sales GL, Tax Amount and Rate are calculated and filled in automatically as if entered into Sage itself. But if expressed in the columns such as in our example, these values are used.

Thus SI-1:

Results in:

 

Service Transactions SI-3

PostTrans will import Orders/Invoices using just Account Code, Transaction date, Description, Unit Price as a Product Invoice, but booked to S3 to give large comments, if no Product is specified.

Thus SI-3:

Results in:

Multi-Currency (Pro Version)

In the “Order CSV with Currency PRO” example we have two extra columns to define the currency, and exchange rate using tags THCurrency and THCurExch  to give:

If not specified the figures imported will be in default account currency.

Data from an External System

Tax Calculation SI-2

When importing data from an external system it is wise to import the transaction line with the amount of VAT as calculated by the external system using either TLTaxManAmount or TLTaxManTotAmount (recommended).    Where TLTaxManTotAmount is the total Tax for line, and TLTaxManAmount per single item.

So in SI-2 we have the total TAX for each line:

This imports the VAT as a manually amended tax amount as calculated by your external system.  Thus telling Sage 50 NOT to recalculate the Tax, and totals, which may cause the Total on the invoice, to disagree with the Invoice total from the external system due to differences in rounding rules used by the two systems, especially if the VAT rate is changed to an odd number like 17.5%.

Discount on a Transaction

Since the manually calculated VAT is expressed on each line, we cannot import into the “Net Value Discount”, which we could do for other types of import, as it will conflict with the VAT calculation.

If you wish to import an overall discount for a transaction you should import a credit note for the amount of discount, which also gives control over the Nominal account it is booked to, or spread the discount across the lines in TLDiscTotValue (column L)

Why not post a negative value on the Invoice Line?

The Sage 50 toolkit, which PostTrans uses to push data into Sage 50, may allow this, but if a user opens the invoice in Sage 50 it will change the values to a positive.

Option 1 – Discount imported as Credit Note SI-2 and SC-1

SI-2 Is an invoice for the full amount, with an extra comment on it which specifies the discount which has been applied to the invoice totals by external system. £5 pound in this example.

Thus SI-2:

Results in:

 

And SC-1

Results in:

Option 2 – Discount Applied to each line  SI-4

In this example we have spread the discount across the transaction lines, which is how Sage 50 calculates “Net Value Discount” in the system.

results in:

Fix Rounding Issues

Often when importing transactions from an external system the total do not agree with those from external system by a few pence.  PostTrans can automatically round Up/Down a Transaction to match Payment Amount (using switch in System Settings >> Switches >> Fix rounding Problems with extra line (+) or discount (-)), or value specified in tag THRoundToGross.

The difference is posted an additional line if rounded up, or adds discount to round down, to account for difference in transaction totals.  Use tag THRoundToNom, THRoundToDep to specify the Nominal/Department, else uses Sales Discount Nominal.

See Example “Import File:Orders from CSV with Payment, Rounding and extra lines”, using PostTrans Help button.

Adding additional lines in same row

Additional 4 transaction lines can be easily added in the same row by simply using Tags TLExLineProduct1,TLExLineNet1 to specify a stock code, which could be a non stock code for carriage.

See Example “Import File:Orders from CSV with Payment, Rounding and extra lines”, using PostTrans Help button.

Main import file options

Press Setup and select “Import File” tab to access the file import options.  Set the Column in which to import the file into, and tick option “Show File Import button on PostTrans toolbar” to enable the File button which the user presses to import a file:

 

 

The file, after selection having pressed File button, is imported into the specified column, in the row where data starts (4 or 30).  Any formula is copied down the sheet, thus you can use formula to easily manipulate the data before posting.  More about copy down formula

The file is then processed, into Sage 50 or 200, by pressing Import button.

If successfully imported, PostTrans moves the text file to the directory specified in “Move to directory”.   This requires tag THImportFullName in the template header to temporarily store the filename of the original file.

 

CSV File Options

Set “File delimited with" : Comma, and “text qualifier" : None and “skip first line" : Yes

 

 

Importing a fixed length file

If “Fixed Length Spec” is set then PostTrans will treat the file a fixed length file:

The field lengths of this file are 6,10,20,16,40,10,10,1,10   So we would enter:

 

Which then gives:

You should also have read the "Importing Transactional Data" on the Training page for a basic understanding of PostTrans basics.

Other useful articles

Other articles of interest are:

Sage 50 Discount in Transaction Lines and Multi-Transaction Import

Tags of interest

The following PostTrans tags allow the above functionality:

Tag Name	Data Type	Description
THImportFullName	Text	Full PATH and Filename of the file just imported using the Import button.  This is required if you set the directory to move file after posting transaction.
THImportFileName	Text	Filename of the file just imported using the Import button

 

 

Discount And Tax Tags

Tag Name	Data Type	Description
TLDiscPerc	Text	Additional Discount Percent to be applied to TLUnitPrice. Note if entered manually TLUnitPrice has already had any system discount applied on account record.
TLDiscTotValue	Text	Additional Discount Value to be applied to Line, Value.
TLDiscValue	Text	Additional Discount Value to be applied to TLUnitPrice.
TLTaxManAmount	Text	Tax Amount, per item, if calculated manually. Use with Caution as you are responsible for calculating Tax correctly.
TLTaxManTotAmount	Text	Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly.

 

With data type Yes/No fields, if cell starts with 'y' or 'Y' is taken as TRUE

Where ^ denotes a lookup field (Popup search), delete ^ at end of tag, to turn off lookup search