Requires the Pro version of PostTrans

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

• Interactive Data Validation
• Fix Rounding Issues – Can automatically round Up/Down a Transaction to match Payment Amount
• Add up to 4 extra charges per line for Carriage, Insurance, etc.
• Optional Confirmation before import
• Print or e-mail transactions after import Pro
• 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.
• Add additional Items in same row – Add additional items, such as carriage
• Negative Invoice Lines – From sage 50 2017 V24.1

Demonstration Video

Download Import from File Template

To use the template, with Sage 50, install PostTrans. Download
Trial with Sage demo company. Purchase a month subscription for Live Sage Company. Price
Watch the On-line Training course on how to use and customise the templates.

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

This article 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 inclusive of VAT.   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.

Additional columns allow Posting of Extra charges, to a non-stock item, on each line.   The sum value of these accounted for at the bottom of the transaction.   The default VAT for these items can be set on the non-stock Product record.

If a zero rated VAT code is set on each line, then all extra charges are set to that VAT code.

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:Orders from CSV with Payment, Rounding and Extra Lines”.
3. Then import the example customers in the “Customer” sheet
4. Next select “Order and payment CSV”, and press File button on toolbar.
5. Select “import_data_CSV_SO_Payments.csv”
6. The sheet will then be filled with data, as explained below.

Example CSV file:

The following example shows a columns containing Net Discount.  It was later removed as it cause rounding issues and is not compatible with Inclusive VAT calculations

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

which represents 5 Orders.   But could easily be Invoices/Credits:

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 (THAccCode), Transaction date (THTransDate), Cust Ref (THCustRef) in columns C, D, and E, row 28.  See Sage 50 Tag Table

Thus the WO-7566 Transaction gets imported as:

Transaction Lines

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

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

In our example, WO-7566 imports as 4 lines, to our DELIVERO account in Sage 50:

Column L contains the Total Payment for each line inclusive of VAT.   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, where the Line Totals do not agree with Total Transaction Payment.

Additional columns allow Posting of Extra charges, to a non-stock item, on each line.   In the header of the import template we can set the non-stock items to use for these extra charges:

Up to 4 of these can be defined.  The sum value of these accounted for at the bottom of the transaction.   The default VAT for these items can be set on the non-stock Product record within Sage 50.  If a zero rated VAT code is set on each line, then all extra charges are set to that VAT code.

If you external system uses different VAT codes (eBay/Amazon etc), then VAT Code could be moved to the right of the Template, and a formula used to translate to Sage 50 coding.

The payment side is then posted:

And a matching SA record:

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, Sales Order WO-7566 will have a 4-line Sales Order created, as shown above.

Add extra columns

You can add, Delivery Address e-mail address if required.  Move to appropriate column, and simply press Tags button, on PostTrans toolbar, to access the list of Tags, and type “Del” to see all Delivery Tags, and select THDelEmail.

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

Using Excel Formula to manipulate the data

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

This feature gives you a lot of power using VLOOKUP, and other formula if need be.

In our example sheet we have a section in column W onwards which manipulates the CSV file data, and sets other Sage 50 data fields via PostTrans.

Transaction Types

If column W (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.

Adding Discount

Sorry discount cannot be accounted for when using Inclusive of Tax Import

Multi-Currency (Pro Version)

To handle Multi Currency we would add Currency No and Exchange Rate using tags THCurrency and THCurExch.

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

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

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