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

Importing CSV/Fixed file into Sage 50, 200 and Xero

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

 
 
   Sage 50 - Import Customer and then Orders from File
  0:00 Video Introduction
  1:25 Opening File Import Example
  2:00 Import Sales Orders into Sage 50
  5:00 How to setup file import
  7:15 How to find more information and Training
 
 

 

 

 

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:

image

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

image

So in Excel we press File button, on the PostTrans toolbar, 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, 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:

image

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:

image

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.

image

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:

image

Results in:

image

 

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:

image

Results in:

image

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:

image

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:

image

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:

image

Results in:

image

 

And SC-1

image

Results in:

image

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.

image

results in:

image

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.

image

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.

image

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:

 

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

 

Importing a fixed length file

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

image

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

 

image

Which then gives:

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

 

 

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

 

Categories: Sage Line 50, Sage Line 200, Static Data, Transaction Data, Nominal | Tags: | Return

Post a Comment

Blog Categories