The Book-keepers Forum (BKF)

Post Info TOPIC: Extracting data from Sage 50


Master Book-keeper

Status: Offline
Posts: 8295
Date:
Extracting data from Sage 50


Hi

Client has Sage 50 Accounts Professional version 22 (on a server).

They have a few customer/debtor accounts, but mostly use one debtor account styled 'cash account' for invoice production, otherwise their customer list would be pretty huge.

Invoice address panel contains

cash account - xxxxxxx (name of customer)

mobile number

email address

(and occasionally the full address)

Historically the email addresses have been collated in a separate database, but the phone numbers have not.

Client now wishes to extract the mobile numbers.

Wondering if anyone knows how this can be done? Nothing obvious springs to mind from Sage.

Wondering if Onion can help?

Edit - oops forgot to add - there is about 8500 lines of invoice data, albeit once extracted there will be duplication of info as its a repeat customer bases (annually or some times more).



-- Edited by Cheshire on Thursday 12th of December 2019 04:54:02 PM

__________________

Joanne

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position (and my that I dont mean with HMRC)



Expert

Status: Offline
Posts: 1768
Date:

I know none of the usual customer/invoice reports will do what you want, but I suspect exporting the invoices instead of printing them might work - and I've just fired up my Windows laptop (very rarely do I do that at home!) and experimented, and I think you're in luck - at least, this seems to work for me. Try it with a small batch first, just in case.

Select the batch of invoices, then click on the print (not quick print) icon on the toolbar. Select the invoice layout, and instead of printing, choose export (just the standard export, NOT export to Excel). Change the 'Save as type' to CSV, choose a filename, then save.

If you load the newly saved CSV file into a spreadsheet, you should hopefully see it's saved it as a line per invoice (whereas the Excel option would've laid it out to resemble an invoice). If they've been consistent with that address format you describe, the phone numbers *should* all be in one column. (You'll probably find the columns intermixed slightly - depending on the actual layout for invoices. (Here for example, A is 'Invoice' or 'Credit Note', B is the company name, C the account reference, D the first line of the address, E the date, F the second line of the address, and so on).

You could probably create a new layout that contains not much more than the address panel, and thus reduces the clutter in the CSV file - might speed things up slightly.

As for having that one customer account, I have a client set up similarly (my choice, not theirs). In their case, it's two accounts, one for B2B sales, one for B2C sales. (This happens to be the one I've mentioned before for whom I wrote an invoicing program).

__________________

Vince M Hudd - Soft Rock Software

(I only came here looking for fellow apiarists...)



Senior Member

Status: Offline
Posts: 213
Date:

Excellent response Vince.

__________________

Ian

Ian Brown FCA
Onion Reporting Software Ltd

www.onionrs.co.uk

Sage accounts in Excel. No set-up necessary. Free trial.



Master Book-keeper

Status: Offline
Posts: 8295
Date:

Vince - what a life saver! Going to give this a try later today at my clients.

Thank you, you star.

hi Ian, long time no see!

__________________

Joanne

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

You should check out answers with reference to the legal position (and my that I dont mean with HMRC)



Senior Member

Status: Offline
Posts: 213
Date:

Hi Joanne,

I keep an eye out, but it's not often I know anything that is of particular use to the folks on here. Then one on Sage comes along (I might have a chance there) and Vince piles in with his usual very comprehensive answer. Oh well. He must sleep sometime. I'll try looking for unanswered questions that I might know something about in the dead of night!

Best wishes,

Ian



__________________

Ian

Ian Brown FCA
Onion Reporting Software Ltd

www.onionrs.co.uk

Sage accounts in Excel. No set-up necessary. Free trial.

Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.

Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  

2007-2019 The Book-keepers Forum (BKF). All Rights Reserved. The Book-keepers Forum (BKF) is a trading division of Bookcert Ltd. Registered in England Company Number 05782923. 2 Laurel House, 1 Station Rd, Worle, Weston-super-Mare, North Somerset, BS22 6AR, United Kingdom. The Book-keepers Forum and BKF are trademarks of Bookcert Ltd. This forum is a discussion forum only. There will usually be more than one opinion to any question and any posting should not be viewed as a definitive solution. No responsibility for loss occasioned to any person acting or refraining from action as a result of any posting on this site is accepted by the contributors or The Book-keepers Forum. In all cases, appropriate professional advice should be sought before making a decision. We reserve the right to remove any postings which are offensive, libellous, self-promoting or engaged in covert marketing. We will not notify users of removals. The views expressed in the forum posts are those of the individual and do not necessary reflect or agree with those of The Book-keepers Forum. Any offensive or unsuitable posts will be removed by the moderators. Any reader of this forum can request for a post to be looked into by sending an email to: info@bookcert.co.uk.

Privacy & Cookie Policy  About