Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

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

by
9th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

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:

1. Dates come over wrongly. They LOOK right, but they are still wrong!

2. Frequently, all amounts come over as positive. For example, an exported list of Sales Invoices will show both Credit Notes and Invoices as positive numbers. So your report totals will be wrong.

3. In some versions, unless you tell it not to, Sage will export deleted transactions as well. So your report totals will include them and be wrong.

4. Essential fields are lacking, especially code names. So, Sage brings over nominal codes, department codes and customer codes, but no code descriptions. For those without a phenomenal memory, this is irritating.

Use these notes to guide you through. Many thanks to AccountingWEB readers for tips and comments, in particular Charles Greenow, Tom Cadogan, Alastair Harris.

PART 1 ' Using File-Send'Contents To Excel to export data
There are two ways of exporting data into Excel from Sage. The first is to export the data as a CSV file via one of the standard reports in the Sage Reports menu. This facility has been in Sage since the beginning.

The second is to display the data on screen, then from the main menu choose File'Send'Contents To Excel. This feature has been available since version 8 and is much more convenient. It also has the advantage of inserting column headings, which the CSV export does not. These notes assume you are using the File'Send option.

Using File'Send to Excel to export a trial balance
You can use the File'Send option to export data from any Enquiry screen in Line 50. You can't use it to export reports produced by the Report Designer and then displayed in print preview.

For example, to export a trial balance into Excel simply:

Click onto Nominal. You see a list of Nominal Accounts together with a debit/credit balance (if you are on version 10, click the Expand All button).

From the main menu select: File'Send'Contents to Excel. That's it. Sage displays a 'copying to Excel' box and bits of paper start floating over.

In version 8 the Copying to Excel box shows how many transactions have been sent and how many are left to go. But in version 10 these have been removed and you now have no idea if the transfer is going to take 30 seconds or 30 minutes. Sage, bring the numbers back please.

After the export is complete, Sage starts up Excel and the TB appears in Excel, with Debit and Credit columns. To check that debits and credits agree, click on the C at the top of the Debit column to highlight it. At bottom right of the screen Excel shows the total. Then click on the D to see if the Credits add up to the same amount.

Other Screens you can export from
Other useful screens from which you can export in this way are:

  • From Invoicing, you can export a List of Sales Invoices. But note that both Invoices and Credits come over as positives.
  • From Bank double click on the Bank Account you want, then Activity. This screen lists all payments and receipts on the bank account and ' most importantly - includes the Reconciled date. If you have a bank reconciliation that has got out of sync, you can export this screen, then use Excel to sort the transactions into Reconciled Date order and compare the result against the bank statement.

    Limitations of some screens
    So, the screen export facilities in Line 50 are good, but there are some limitations. You can't get a list of purchase invoices, for example. And some screens omit key fields. So, if you export a list of transactions for a Nominal account, it doesn't include anything about the supplier.

    So on the whole I would suggest that, except maybe for the bank account, don't go to individual screens and export from there. Instead, do it from the 'Financials' screen. This is a little bit more complicated, but it allows you to export all types of transaction, and it contains more fields than any other screen.

    PART 2 - Exporting via the Financials screen
    The process for exporting from the Financials screen is the same as any other. Go into Financials. You see a list of transactions on the screen.

    Exclude Deleted transactions
    Generally speaking, you want always to exclude deleted transactions from reports. So before starting to export, first go into the Company'Preferences menu. Make sure that the Exclude Deleted Transactions box is ticked.

    Using the Search wizard to filter the data
    Usually you will not want to bring over the entire database, but to filter it, that is, bring over only records relating to one account, or this financial year etc.

    To filter the data, you need to click on the Search button at bottom left, then choose Wizard. This will help you to generate an 'expression' as Sage call it.

    If you have applied filters before, the wizard is a bit clunky, but straightforward enough. If you haven't, there's a worked example in How to Export Sage Data via Search

    If you want to select only sales invoices, journals etc, you may need to refer to the list of Transaction Type codes below.

    Alternatively, of course, you might find it easier just to export the entire database into Excel and chop out the bits you don't want there.

    So go ahead and export the data. Sage will automatically open Excel for you with the data in.

    Looking at the data in Excel
    Excel should have opened automatically and be displaying the transactions. From the left, Sage bring over the transaction type code, the supplier account code, the nominal code and the department code. In typical Sage fashion, though, they omit all the code descriptions and don't tell you what any of the codes actually mean.

    Step 1 Remove all blank rows and columns
    Between the column headings and the data Sage has inserted a blank line. This will mess up Autofilter, so remove it.

    Also, when you use the Sort function in Excel, it sorts on contiguous data only - any data on the other side of a blank row or column won't be sorted. So get into the habit of removing blank columns and rows. Personally, I would also get rid of columns where there is a column heading but no data. As a general rule, blank columns or rows can so easily cause you grief in Sorts ' get rid of them on sight.

    Step 2 ' Check whether Dates really are dates
    To the unsuspecting eye, the dates in column G look fine. But in most versions of Sage they are not true dates, but text.

    To find out whether they are real dates, see if they sort properly. Click onto G10 in the Date column, then click the AZ icon to sort it ascending.

    Now look closely at the column of dates. They are not in true date order, but ascend from 01 to 31!

    This is a common problem with Excel export (QuickBooks has the same fault). To check whether a column of dates has come over correctly, click onto the column heading and then the 'comma' icon.

    If the dates are real, they should change to look like a column of numbers around 38,000 or so. This is because Excel stores dates not as dates but as numbers, starting from 1st Jan 1900.

    If the dates are not real, they won't change but will keep looking like dates. They have to be corrected, as follows:

    Step 3 - correct the dates
    It is very easy to correct the dates via the Text to Columns option.

    Click on the top of the Date column once more to highlight it, then: Data'Text to Columns

    Change to Fixed Width'Next-Next in Step 3 at top right select Date DMY. Finish.

    Now click on the Comma icon. This time the dates all change to numbers ' they are now true dates.

    Thanks to reader Charles Greenow for this invaluable tip. If you use pivot tables, you will know that just one wrong date in 10,000 records is enough to disable the Group function. Personally, whenever I export transactions into Excel, I now run all the Date columns through Text to Columns as a matter of course.

    Step 4 ' Add a Month column
    Usually, when looking at data you will want to see all the entries for a particular month. To make this easy, you now need to copy the date column and then convert the dates into months. To do this:

    Make a copy of the Date column and put it next to the first Date column, so there are two Date columns next to each other. Change the heading of the second column to Month.

    Highlight the Month column. Then: Format'Cells
    [Excel 1997, Excel 2000 ' click on Dates ' Mar 97]
    [Excel 2003, click on Custom ' mmm/yy]

    Step 5 ' Use Autofilter to display and total by month
    Unclick to remove the highlighting. To see the effect, select: Data'Filter-Autofilter.

    Click on the down arrow to the right of the Month column. Choose any month you like. Excel just displays the results for that month.

    Now move along to the right to column K, the Net field. Click onto the K. The column is highlighted and at the bottom of the screen Excel gives the total of the column.

    Now choose another month on the Month column. Excel recalculates the Sum total each time.

    This really is super stuff. I have to say that Excel Autofilter seems to me to be one of the all-time greats of IT design.

    Unfortunately, though, the totals are wrong because Sage has exported both debits and credits as positive numbers. You will have to convert one or the other of them to negatives.

    Step 6 - Make credits or debits negative
    All numbers have come over as positives. So, in the case of a journal for £100 cr and £100 dr, both amounts come over as 100.00. You are going to have to multiply the debits or the credits by -1.

    Below is a list of the Transaction Types ('Tp') to be found in Sage.

    1 ' SI Sales Invoices
    2 - SC Sales Credit
    3 - SR Sales Receipt
    4 - SA Sales Account (Cash from Customer not allocated)
    5 - SD Sales Discount

    6 - PI Purchase Invoices
    7 - PC Purchase Credit
    8 - PP Purchase Payment
    9 - PA Purchase Account (Cash to Supplier, not allocated)
    10 - PD Purchase Discount

    11 - BP Bank Payment (not supplier)
    12 - BR Bank Receipt (not customer)

    13 - CP Cash Payment (not supplier)
    14 - CR Cash Receipt (not customer)

    15 - JD Journal Debit
    16 - JC - Journal Credit

    17 - VP Visa Payment
    18 - VR Visa Receipt

    Of these, the following are naturally debits and credits (I think):
    Credits: BP, CP, PC, PA, PP, SI, PD, VP, JC
    Debits: BR, CR, SA, SR, SC, PI, SD, VR, JD

    So one of these sets has to be multiplied by '1. Under double entry rules, the credits should be minuses, but management tend to get upset when they see Sales as negative, so maybe make the debits negative instead.

    To make the debits (say) negative you can multiply all the numbers by hand or use a Lookup table. We'll discuss Lookup tables in a moment. To multiply by hand, I personally would:
    1. Sort the file into Tp order.
    2. Make a copy of the Net column to the right of the first Net column. Call it Net2.
    3. Data'Autofilter
    4. In the Tp column, choose to show BP or BR.
    5. in the Net2 column, insert the formula =J2*-1 (where J2 contains the first Net amount)
    6. copy down the formula to all rows by Using the Excel Fill Handle
    7. highlight the Net2 column. Edit'Copy, then Edit'Paste Special-Values

    Step 7 ' Bring in Missing fields via VLOOKUP
    The last problem is that a number of essential fields have not been brought over, principally the descriptions of all the codes. This problem ' that essential fields are left out - occurs all the time with data export. But it's no problem once you know how to use Excel's VLOOKUP function.

    For example, the Financials data has the NL code in column D, but it doesn't have the NL description. However, if you export the Trial Balance into Excel, that DOES have the NL description. Using the fact that both files contain the NL code, you can Lookup the code description in the Trial Balance file and pull it into the Financials file.

    If you are serious about analysing data in Excel, you have to master the VLOOKUP function. Work your way throught AccountingWeb's tutorial Using Excel's VLOOKUP function to add fields to a worksheet.

    A couple of points about VLOOKUP
    a) The common field ' Acct Code, Dept code, NL code etc - must be the FIRST column of the table being looked up FROM. (It can be anywhere in the table which the data is being looked up TO.)

    b) You can use the VLOOKUP function to convert the debits or credits to negative numbers (step 6). Make a new Excel worksheet, and in column A type in all the Tp codes BP, SI etc. Then create column B next to them and insert 1 or '1 against each one. Use VLOOKUP to pull in the value 1 or '1 and multiply it by Net. Thanks to Alastair Harris for this suggestion. For a discussion among the experts of ways to convert the positives to negatives, see Excel Experts wanted urgently. Particular thanks to Philip Hodgson of VT Software who has made available an Excel function to multiply Sage credits for AW readers.

    David Carter, Consultant Editor

  • Tags:

    Replies (11)

    Please login or register to join the discussion.

    avatar
    By AnonymousUser
    14th Oct 2004 11:55

    Yet another option
    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 analytics
    enable you to gather and analyse your
    financial 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

    Thanks (0)
    avatar
    By AnonymousUser
    14th Oct 2004 15:29

    Why doesnt anyone use ODBC?
    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

    Thanks (0)
    avatar
    By David Carter
    14th Oct 2004 21:25

    ODBC is raw data
    Jackie, you ask why I don't mention ODBC.

    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.

    Thanks (0)
    avatar
    By Tomcogswell
    13th Oct 2004 17:26

    There is another option again
    Have a look at Alchemex. in fact you can go to https://www.accountingweb.co.uk/redirects/alchemex.html to have a look at an evaluation copy. These guys are massive across South Africa, and are now moving into the UK market.

    Thanks (0)
    avatar
    By Mark Duncan
    21st Oct 2004 15:29

    Sage Line 50 v11 - extracting data is simple, straight forward a

    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 addition
    to 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.

    Thanks (0)
    avatar
    By David Carter
    19th Oct 2004 22:44

    Standard in Sage?
    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.

    Thanks (0)
    avatar
    By ChrisDL
    07th Oct 2004 17:01

    The trial balance (or is it?)
    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!

    Thanks (0)
    avatar
    By AnonymousUser
    08th Oct 2004 09:58

    An alternative solution
    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

    Thanks (0)
    avatar
    By AnonymousUser
    15th Oct 2004 09:40

    is Sage line 50 fit for purpose?
    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!

    Thanks (0)
    avatar
    By Geoff O
    18th Mar 2010 19:22

    Exporting Data from Sage to Excel

    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?

    Your comments would be appreciated.

    Thanks (0)
    avatar
    By smpyana
    12th May 2015 09:29

    Sage tables
    Good day, I am very new in the sage space and would like to build odbc linked trial balance and general ledger transactional details for reconciliation and analysis. Could I be helped with the necessary tables to do this?

    Thanks (0)