Making I.T. Happen
Accounting and Information Technology Assistance and Support
Custom integration - Importing Files to Sage 50 - How to import

Custom integration does not require any specific format, it is able to deal with data in any format.

However, there is a basic format that "work straight from the box" this is headed csv.  The first row of data tells the software what data appears in that column, subsequent rows are data.

Some imports like invoices contain two type of data , the headers for the invoices and the details for each invoice.

Where there are two data types, you can either use two files or you can use one file and repeat the header information for each item and the program will work it out.

You can omit information and the program will "intelligently" work things out.

For example if you create and invoice for a customer but do not include any prices on the line items, then the program will apply pricing to that item, just as if you entered it into Sage 50 manually, so customer discounts, volume discounts, pricelists and so on all work "as normal". If you don't give an invoice number it will use the next available invoice number, if you don't give a department it will use the default department, if you use an S1 code and don't give a nominal it will use the default for the customer, if you don't give a tax code it will use the tax code of the product and obey any override tax codes set on the customer.

You can also specify everything if you want and force the price. discounts, nominal codes, tax codes and so on to be what you want.

For a minimal import you could use just a Customer Account reference in the header and just a qty and a product code in the items like this (in a spreadsheet)

As a csv file

INVOICE.ACCOUNT_REF,INVOICE_ITEM.QTY_ORDER,INVOICE_ITEM.STOCK_CODE
CGS001,5,BOOKS001
CGS001,7,PAPER002
CGS001,3,CALC003

To see all the fields you can import for each type of transaction click here

Some operations normally use "human" intelligence, for example when allocating a customer receipt. Through various options, you can have the import program allocate if they match the amount, allocate against the oldest regardless, post as a payment on account and so on, the program can do quiet well most of the time.

Developers should note that some transactions such as Sales Payment and Purchase Receipt (which were introduced in L50 2007) are not valid for older version, it is wise therefore to avoid these transactions in order to maintain compatibility with older versions of Sage 50.