Importing data from Sage line 50 & payroll

[#1]

Hi

I am trying to import financial information from Sage Line 50 & Sage Payroll in order to build a set of management accounts in QlikView.

I have the Sage Access DB, but cannot connect to it as I need a password. This is different from the password set by the admin to log into the Sage application.

I called Sage to try and figure out if there is a work around, and was informed that I would need to contact a Sage developer partner. This will certainly involve additional costs and will erode the value proposition for QlikView.

I appreciate the forums knowledge on this matter. Does anybody have any experience of getting data out of these two Sage products? Can you go through the database? Or is there are better way going through an API or the application itself? I do not want to have to resort to CSVs, this solution needs to be a persistent connection.

Many thanks

Rob

[#2]

Hi Rob,

I am by no means an expert on this, nor yet am I a development partner so don't panic. I have, however, managed to get data out of Line 50. I have yet to attempt Payroll but will do soon.

I understand that you have configured the ODBC connector for Sage but will you bear with me if I walk through step by step please?  The way that I do this is to:

1. Make sure that Sage is loaaded onto the PC on which you wish to run the Qlikview document. I previosuly tried just loading the Sage ODBC from the Sage server onto my laptop. this failed. I load the Sage Line 50 onto my laptop and then ignore the fact that it is there. Doing this seems to make the ODBC driver function correctly.

2. I link to the Sage server on which the data files I want to access reside. For me this means creating a shared drive to the location of the \ACCDATA subdirectory. When you find this it will have lots of .DTA files in it. Make sure that you click on this using File Explorer every time you want to work with it. If you don't the driver will again fail and tell you that you have the wrong driver or that not all files are recognised. In fact it is just that Qlikview seems not to force the drive to open but File Explorer will (I know this sounds weird but it may well save you frustration one day).

3. I go to the ODBC configurator in Qlikview. Select the 32 bit ODBC configurator. the 64 bit does not seem to work with Sage.

4. Click on the System DSN tab. The click on the Sage Line 50 v 20 driver. Then click on configure

5.Give this a name to remember such as Give me my Sage Data then, beneath this, enter the drive letter of the shared drive\ACCDATA (so X:\ACCDATA in my case). Click OK

6. Create a document and open the edit dialogue then dial up ODBC on the drop down. Click connect

7. From the list of available data sources select the name of the one you have just created.

8. Now to the part that seems to be causing you trouble. I use just an ID and password for the Sage system that is assigned to one of the users. The admin one should work too. I'm afraid I don't have much to offer by way of fault finding here, only that I struggled for a month or so to do it until I loaded the full Sage application onto my laptop before completing the rest. I stress I do not log into the copy on my laptop. there is no ID or password for it. I use a valid log on to the central server-based Sage.  What you can do, when you enter the ID and password, is to test the connection using the button at the bottom. The most common way that I confound myself is by forgetting to use File Explorer to open up the directory before I try to load data or test the connection using Qlikview.

I hope some of this helps and I apologise if I am patronising you

Regards

Mike