Menu

Blog

Search

Category

AutoUpdated

Save time updating

Sage 50

Stock/Product Creation
Customer/Supplier Prices
Bill of Material
Project Creation
Location Bins

Sage 200

Simply extract, edit and import

Xero

Import from Excel

50 Trans

Nominal Journals
Quotes/Invoices/Orders
Remote Orders
Product/Stock Adjustments
Sales Receipts
Quick OrderPad

200 Trans

Transaction from Manual Input

Image Alt TextImage Alt Text

Multiple Transactions from CSV file

Xero

Solve the Puzzle?

Sage 50

Help on every type of import
Demo Video of all functionality
Loads of Examples included
Online Training
We can help too…

Sage 200

Image Alt Text

Xero

Great Customer Satisfaction

Customer Comments

Full Trial with Demo Data
Low cost Subscription
Cancel anytime
From a £1 a day
Saving you hours

Simply extract, edit and import

Power of Excel

Sage 50

Quick Template Creation
Customise with:
    Formula
    Macros
    VBA

Sage 200

To reach your goals quickly

Sage 200

Sage 200 Bins

Sage 200 Warehouse & Bins

Create Sage 200 Bins
Stock Take Bins
Move Bin Stock

Fast, cost effective, easy…

Image Alt TextImage Alt Text

Manufacturing

200 Manufact.

Create BOM Stock Easily
Create Bill of Materials
Labour, Machine, Tools

Image Alt Text

Complex tasks quicker...

50 BOM

Project Accounting

200 Projects

Create Project Structure
Update Budgets
Simple Project Entry

Make life easier...

Image Alt Text50 Projects

PostTrans Blog

Why not sign up to recieve news, tips, ideas, training.  Sign up for bi-weekly newsletter.

Blog Search

Import Sage 200 Orders from Web Cart Export

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



   Sage 200 ֠Import CSV file with Payments Amazon/Ebay
  0:00 Introduction
  0:40 Template Design Overview
  1:30 Additional Charges
  3:20 What forces New Transaction
  3:40 How Tax is handled
  4:10 Adding new mappings using Tags
  5:40 Clear and File import buttons
  6:20 Importing the Transactions
  9:00 The proof in Sage 200
  10:00 Settings - File Import settings


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:


image

which represents 6 Orders.

image

So in Excel we press File button, on the PostTrans toolbar to read the Web Cart Orders.  Then we get:

image

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:

image


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:

image


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

with payment details of:

image

And Delivery address:

image

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:

image

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


image


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



 

Categories: Transaction Data | Tags: | Return

Post a Comment

Blog Categories