Exporting data from Sage Line 50 into Excel #1. By David Carter

If you want to take Sage data and produce your own reports, you really have only one option - to get it into Excel. The Report Designer in Line 50 is about 15 years old and really a non-starter. Sage has recognised this and has worked hard in recent years to improve integration between Line 50 and Excel.

The key advance was the File'Send to Excel facility introduced in version 8 in 2002. However, there are still a number of traps for the unwary. None is insuperable, but you need to be aware of them in order not to be caught out. Briefly, the main ones are:

Have a look at a product called Fast Track it has a 7 day free trial and makes exporting data from line 50 a cynch, Sophisticated data linking and analyticsenable you to gather and analyse yourfinancial data by whatever criteria you require.If you are an Accounting professional with clients who use several versions of Line 50 you can view financial data from several sources with ease.More info and download at http://www.salespac.com/FT/ft.htm

I am totally mystified that this article does not even mention ODBC as a method of extracting data from Sage into Excel. It has been around in all versions of Sage so there is no need to upgrade to V11 to get it!

With a little bit of training it is really easy to use and can be such a useful tool, I am amazed that nobody seems to be aware of it and if they are that they are not using it. We are running half day workshops on it if anyone is interested - www.japeconsulting.co.uk

ODBC gives you a back door onto the Sage data files. These have been designed by the software developers purely for their own use, not for end users.

For example, there are three tables in Sage called Audit Header, Audit Detail and Audit Journal, all of which seem to be virtually identical. What's the difference and how do you work out which one to use?

ODBC and Report Designers are for power users who like tinkering. For the average user it is far better to get data out via File - Send to Excel because they can be confident that Sage have chosen the right fields to be exported.

Certainly, though, we'll have to have a look at the products that people have mentioned here.

To improve existing Microsoft integration, Sage Line 50 version 11 features Excel Integrated Reporting. This new feature integrates a Sage menu within Microsoft Excel allowing easy selection of a preformatted suite of reports.Once the required report is selected it opens directly into an Excel Spreadsheet with no extra formatting necessary. In additionto the reports provided any other standard or customised report created in Report Designer can be easily added to this list and opened within Microsoft Excel.

Excel Integrated Reporting also supports the easy selection and insertion of Sage Functions into a workbook. These functions are based on the existing Tables and Variables which have previously only been accessible through Report Designer and ODBC. Sage have provided sample workbooks to demonstrate what can be achieved with Sage Functions. These include tables, graphs, charts and pivot tables.

William, I think the problem you mention - each invoice can only accommodate one nominal analysis line - is standard in Sage. Certainly on the purchase invoicing side the Batch entry screen requires you to enter a second "invoice" if you want to analyse to more than one line.

In the past it produced some very strange side-effects, but Sage seem to have sorted most of them out now.

I don't feel that any of the problems I highlighted in the article are major problems. In fact I would say that Sage is better than most at exporting data into Excel. Try Navision, for example.

[Later note] I see your problem is specifically with importing sales invoices which have more than one item line. Again, I have to defend Sage here. It seems to me that their Import facilities are excellent for a product in this price range. Far better than QuickBooks, TAS etc. And I would not expect a sub £1k package to import sales invoices with multiple item lines - a single sales ledger header record is OK. Multiple item lines are a lot more complicated and require a mid-range package with a sophisticated transaction import module.

As someone who has made a mistake before in this area, I'd like to thank David Carter for a very helpful article.There is one little note of caution in the section on exporting the trial balance though that I have to sound. Use of File --> Send --> Contents to Microsoft Excel does not necessarily generate a trial balance. It looks like a trial balance, and it adds up like a trial balance but it is actually a list of nominal balances at no specific date and will therefore include future dated transactions. Particulary in versions 8 and 9 (where there are less filtering options of the list of nominal accounts) this could cause a real problem to the unwary.The solution? A fairly simple ODBC link will do it, but is a lot more trouble. Or upgrade to version 11!

An alternative that requires very little effort for the user and is FREE of charge is Data Capture which can be downloaded from http://www.excelforaccountants.com/downloads.htm If you do not have Access 2000 or above installed you will also need to download Access Runtime (but you will need a fast connection for this).

Data Capture merges data from 5 Sage tables into 40 columns, handles all the debit, credit, date and code description issues and is Excel Pivot ready data. All you have to do is enter the Sage Version Number, Browse for the Sage Accdata folder and select Convert to Excel.

We have included Deleted items in case the user needs to see what or why there were deletions but this column is easily excluded in a Pivot Trial Balance

For Practising accountants who choose not to buy Sage this offers an easy way to view client data electronically as the client can download this Freeware and send one Excel file by email or whatever, without having to know anything about Sage to Excel reports etc

This piece highlights serious and fundamental flaws in this immensely overpriced and troublesome software product.

Another fundamental flaw is its inability (as shipped) to import to the sales ledger invoices containing more than one nominal sales code (eg where labour and materials are billed on the same invoice). Sage claims this is possible, but the result is a sales ledger record containing two items, both bearing the _same_ invoice number, with the total VAT on the transaction split between these two entries! This simply does not comply with HMCE accounting requirements and is completely unworkable. Sage have been well aware of this problem for a considerable time, but have not fixed it. Does anyone have a workaround?

Recently Sage sold me an upgrade to version 9 telling me they had (at last) fixed this specific deficiency.

I should have known better than to trust a company whose software identifies errors in data (which its software created) and which it then seeks to charge its customers for rectifying!

I have been use to exporting data from Sage line 500 by having a ODBC link to the tables and 'pulling' the data from Sage.

The result is a spreadsheet that will update by simply pressing the 'Refresh' button in Excel without having to

I am now trying to pull data from Sage Line 50.

Is it possible to set up a connection to export so that it refreshes automatically rather than having to go through the export process each time you want the data refreshed, or is this not feasible on line 50?