Import Guide - Sage 50

[ Home ] [ Sage Forum ] [ Sage 50 Paid Support ] [ Sage 50 Guides ] [ Sage 50 Software ] [ Sage Integration ] [ Sage 50 Overview ] [ Sage 50 Import ] [ Sage 50 Add ons ] [ Sage 50 Manual ] [ Sage 50 Books ]

Importing

This page is now obsolete.

The GOOD NEWS is that we have created an entire mini site on importing into Sage 50 to replace it

please go to http://import.makingithappen.co.uk

Import CSV Data into Sage 50

Sage 50 includes import routines these can be used to import externally generated transactions or just to make setting up a new system easier.

Import is a little dated and does not support the same types of entries as Line 50 does natively, for example characters such as & are fine in accounts but create problems in importing.

The CSV format itself also places restrictions, you can not use " or , in any fields unless you properly double delimit them.

One of the biggest problems is that because Excel handles the CSV format and we ar eused to excel we tend to use it to manage the data in our CSV files, unfortunately Excel is a litte too clever, it makes a few mistakes in opening files, for example if it sees the Customer account reference JAN001 it will think thie is a date and change it to 1st Jan, thing like nominal codes with leading zeros, it will convert to a number and loose the leading zeros.

With the advent of V2011, there are a couple of changes, first you can now use XLS format, this gets rid of a lot of the problems of Excel and CSV files, however, if your data source is CSV you still need to be careful. The other change is that the columns no longer have to be in a fixed order, you can now choose any order you like and save a template for that format. This actually makes the process not so easy to do on a day to day basis as you now have extra steps to go through to select the template, if you do regular importing then investing is a 3rd party tool is going to help smooth the process a lot, see advanced importing below.

Note than only some fields can be imported, so this import not a perfect solution, although it will save a lot of work.

Overview of the Sage 50 import process
====================================

1/ Make sure that the CSV data is valid and complies with the rules

For data to be valid it must NOT contain any " or , unless properly escaped

For account references they can contain only A to Z and 1 to 0 - extended characters such as & can cause problems as can foreign characters.

The fields must also be in the correct order.

2/ Get the fields in the right order There are a lot of field in the different tables the link will show you them all. Unfortunately Sage changed the layout several times and it is best to consult the help file in your program to be sure you have the right order for the fields.

3/ Run the Import process in Sage 50, this is a two part process, Sage runs a test the first time and if everything is okay it does a second pass, during which it import all the data into sage 50. The process is ALL or NOTHING, if it fails on one record all the records will fail to import.

4/ Fix any problems highlighted by the import. It is quite possible that you have missed something even though you were really careful with your data preparation, so be prepared to go back fix mistakes and run step 3 again.

How to open a CSV file in Excel without having it auto convert data and mess things up
===============================================================================

First you need to set windows to not hide file extensions if it is not already set.

In windows explorer go to Tools>Folder Options>View and untick the box next to "Hide extensions for known file types"
Rename your file and change the extension from CSV to PRN.
Open your file buy using the Open or Crtl-O option in Excel, you will need to change the file tyPe filter to all files to be able to see the file now the extension has changed.
The Text import wizard will now start, this varies a bit from version to version of Excel, however,
1. the format is delimited rather than fixed length,
2. the delimiter is a comma
3. the final stage allows you to select the format for each column of data, do NOT use General, this is automatic formatting, select Text for all the important columns (Nominal codes, Account Codes, Descriptions and so on.) Setting text for ALL columns if you are not sure will work fine if you are unsure.

More Advanced Importing and Integrating with Line 50

Advanced custom import/integration features:-

Import from command line

Import whilst users are still logged in

Import into other modules including

	Invoicing
	Sales Order Processing
	Purchase Order Processing
	Price Lists.

Import Receipts and Payments (and allocate!)

More extensive import, import just about every field there is in every module.

Import from anything, import using existing formatted reports saved to disk, no need for CSV formats, xml, psv just about anything.

Scripting commands, we can collect data form ftp, server, web pages, email and process it into sage.

Export Data from Sage.

If you need to do advanced importing, then you need to use a 3rd party tool to find out how we can help you do advanced import including linking existing and bespoke applications with Line 50 get in touch.

Click here for more information.