AccountingWeb
News
Tutorial: Import data from Sage Line 100 into Excel. By David Carter
This tutorial shows you how to import data from Sage Line 100 and into Excel via ODBC. This is the probably the easiest way to get data out of Line 100.Line 100 uses the Retrieve database (as do early versions of Sage MMS). So first you will need to set up the ODBC drivers for Retrieve.
Is ODBC already installed?
If you’re lucky, ODBC was set up automatically when Line 100 was installed on your desktop. To find out, go the Start button and select Settings – Control Panel.
A series of icons is displayed. Click on the ODBC32 icon or (Windows XP) Administrative Tools - Data Sources (ODBC).
The ODBC Data Source Administrator box appears. You are looking at the User DSN tab.
Click onto the System DSN tab. Hopefully, Sage132 Sage Line100 is in the list.
If it IS NOT in the list, you need to install the ODBC drivers. Go on to the next section 1. Find the Location of your data . If it IS in the list, move on to section 3, Download your data tables into Excel.
1. Find the location of your data
Before starting the install you need to find out where your program and data files are located.
When you install the ODBC drivers, the setup program will ask for these, specifically for the Logon path, the Schema Path and the Data Path (a “path” is the location of a folder on the hard disk).
Obviously, these will depend on how your system is setup, but typically the paths would be:
Logon path…:..C:\SAGE\xxxxxLINE100
Schema path.: C:\SAGE\SVNPROGS
Data path........: C:\SAGE\SVNDATA
[SVN refers to Sovereign, the old name for Line 100. The Logon Path is the folder containing the user table, with passwords, permissions etc. The name of this file varies, but it should end with LINE100. The Schema Path is the folder that contains the Sage programs (one of which is the Schema which contains descriptions of the data files and how they are related). The Data Path is the folder that contains the data files, i.e. your company data]
Find out where these are on your own system. Make a note of them. If you get stuck, ring up your reseller and ask.
2. Generate the ODBC driver
Now find your original Sage CD and stick it in the disk drive.
Using Explorer, find the folder ODBC32. There are two sub-folders, Disk1 and Disk2.
Click into Disk1. There should be a program called SETUP.EXE. Double click it to install the drivers.
When the set program is complete, go back into Control Panel – Administrative Tools – Data Sources ODBC. In the System DSN tab, check that Sage Line 100 is now in the list of Data Sources.
With Line 100 in the list of Data Sources, you are now ready to download the Line 100 data tables into Excel
3. Download your data files into Excel.
Start up Excel; open a new workbook. Click onto cell A1.
In the Choose Data Source box, highlight Sage Line 100. OK.
You are asked for your User ID and password. Type them in. OK.
Hopefully, you now see the Query Wizard – Choose columns box, with a list of tables starting with ACS_ADDRESS.
You are in. These are the data tables for Line 100, and there’s an awful lot of them!
Download the data tables
As with most accounts packages, the data in Line 100 is “normalised”, i.e. scattered around in numerous data tables.
Probably only 1-2% of this data will actually be useful for reporting. You now have to embark on a journey of discovery to find out where that 1-2% is located in Line 100.
I’d suggest you download the tables that look interesting. Download them into Excel one at a time, then see what data each one contains and identify the important fields. Some important tables are:
if you are interested in Nominal (GL) Reporting
if you are interested in Sales and Margin Analysis
Warning As usual, nothing in Sage is simple. In the NLPostedTran table there’s a field called NLNominalAccountID. You might think that this was the Nominal Account Code, but it’s not. It’s the number of the record within the NLNominalAccount table which holds the details of this particular nominal account.
So any field ending with the letters ID will not display data that means anything to you. It is simply an internal Sage record number.
Tips on dumping a data table
When you download, e.g., the NLPostedTran table, for the first time, select all the fields within the table. To do this, highlight the table name, then click on the right facing chevron. All the fields appear in the right hand box. Next.
You are now in the Filter screen. You don't want to bring over records from years back, only the more recent ones. Therefore :
Select Transaction Date = greater than 6 months ago. Next
In the Sort screen, choose: Transaction Date – Descending.
This means that only the last 6 months transactions will come over, and the most recent ones will appear at the top of the screen.
Next. Then just press Enter to Return the Data to Excel.
Use Paste Special – Transpose
Once you’ve downloaded the data table into Excel, highlight the first 4 or 5 rows, then Edit – Copy. Go into another worksheet, cell A1, then Edit – Paste Special – Transpose.
The field names are copied into column A and are much easier to read.
END OF SESSION
Related material
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:
Reporting tutorials index: Use MS Query to extract and analyse accounts data
Reporting Tools #4: Introduction to summary reporting
Towards an analysis-ready file for reporting #1
Towards an analysis-ready file for reporting #2
Management Reporting in Sage Line 50 and Instant
Want to learn more about pivot tables? Start here
Improve your reporting skills with self-teach tutorials
An introduction to Excel-driven reporting tools
Subscribe to the ExcelZone newswire
To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.
Number of comments: 1
AccountingWEB.co.uk 19-Feb-2008
Categories: IT Features, Management Reporting Features, ExcelZone Features
Times read: 5229
There are some aspects of this article that will, I suspect, confuse the typical Sage Line 100 user:
* most Line 100 users will be on version 7.6 or earlier, in which case the table names are all different to those mentioned in the article, and the way in which tables are linked is also different
* "the location of data" describes typical paths, but these are relatively unlikely to be correct because most Line 100 users will have their data and other Sage related files stored on a network drive not their local disk
* I'm a bit reluctant to generalise, but it is usually much better to filter in Excel than as the data is being loaded into Excel, with two caveats: Excel 2003 and earlier can only load about 63 000 records; there are some fields which are "indexed" eg typically account numbers, which can be used for filters
Lastly, if you have significant numbers of Line 100 transactions, it is generally better to do a batch transfer into something like Access, and then report from there
