Requires the Pro version of PostTrans

This article explains how import Web Cart Orders from CSV, delimited, or fixed length, text file into Sage 200.  PostTrans has the following advantages:

• Data Validation of all data and easy to error correction
• Detailed Optional Confirmation before import
• Print or e-mail Orders during import PRO
• Easy to customise mapping and add custom mappings
• Archive Order file to processed directory after successful import, so use only new files which have not been imported
• Run batch file before selecting file to download files
• Easy User interface, 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
• Import non Stock item charges or Text easily

Demonstration Video

Download Web Order Import Template
see 'Order and Payment CSV' sheet

To use the template, with Sage 200, 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 to import orders from a file exported from a Web Cart system.   The CSV file contains Account, Product Code, Qty and Total Payment for each line.   PostTrans then imports this into Sage 200 as Inclusive TAX, thus calculating the Tax and Nett from the total line value and records the total payment for each transaction.  It overcomes a common problem where due to external system not using “Bankers” rounding, the Line Totals do not agree with Total Transaction Payment.

Importing a delimited file into Sage 200

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 file contains several Orders, demonstrating several principles of import:

which represents 6 Orders.

So in Excel we press File button, on the PostTrans toolbar to read the Web Cart Orders.  Then 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 B, C, and D, row 28.  See Sage 200 Tag Table

The first line represents an order for ‘PAINT/BEIGE/MATT’ and second line has no Stock Code, but has a value.  PostTrans will import this second value as a Free Text Items as it has a charge.   The sum of the ‘Additional Charges’ in columns J and K will be posted at the end of the transaction:

On the third line the Customer Reference changes to WO-7566, thus PostTrans starts a new Sage 200 Order.

Additional Charges

The ‘Additional Charges’ in Sage 200 will define the nominal these charges are posted to (defined in D4 and D5).   If the line  has a Tax element then the ‘Additional Charges’ Tax rate will be used unless specified in the sheet.  If the line Tax rate is zero rated then the additional charges will use the same rate as the line.   Unfortunately the Sage 200 Data Objects does not allow ‘Additional Charges’ to be posted with inclusive Tax, so the ‘Additional Charges’ is actually posted as Free Text.

Transaction Lines

In our example the data from CSV files are imported in columns B to U.

The Tags in row 20 designate to PostTrans the data mapping between the sheet and Sage 200.  A list of Transaction Lines, beginning with “TL”, can be added by simply pressing the Tags button on the PostTrans toolbar. See Sage 200 Tag Table

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

This Transaction has a line with no value or Stock Code, hence PostTrans imports this as a Comment line.

with payment details of:

And Delivery address:

Line Total (Inclusive of Tax) and Payment Total

The Web Cart has exported the Total for each line, thus this is added up to give the Payment Amount in column L, using Tag THPayAmt.   This value is therefore the summation of the TLTotIncluOfTax column (Amount paid for items on line), and TLExAddChrTotIncluOfTax1+TLExAddChrTotIncluOfTax2 (for the additional charges).

PostTrans then uses the TLTotIncluOfTax value to arrive at the line Net value, and Tax amount bases on the line Qty.  If the Line Qty is not expressed it is automatically set to 1.

Add extra columns

You can easily 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 200 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 column W onwards sets additional Sage 200 data via PostTrans.

Transaction Types

Column W (Transaction Type) has ‘SO’ in it, to denote import Sales Orders into Sage 200.   Transaction Types are SQ,Sales Quote,SP,Proforma,SO,Sales Order,SSO,Sales Service Order,SI,Sales Invoice,SIA,Sales Invoice And Adj,SSI,Sales Service Invoice,SC,Sales Credit,SOB,Sales Opening Bal,SOC,Sales Opening Bal Credit,PO,Purchase Order,PSO,Purchase Service Order,PI,Purchase Invoice,PIA,Purchase Invoice and Adj,PC,Purchase. Fields not appropriate for a particular type of transaction will be ignored.

Multi-Currency

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