Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

Getting details from Sage into Excel

Sage 50 general help forum - Free help and support for all general issues
Post Reply
Andrew

Getting details from Sage into Excel

Post by Andrew » 04 Apr 2006, 12:53

As a one off thing, I need to get the following details from sage into Excel.

Customers:
1; Customer Name, address and contact derails (with Account Numbers)
2; Contact name and e-mail details (all details from the details page)
3; Credit limits and terms of payment
3; Opening Balance

Suppliers:
1; same as 1 above
2; same as above
3; same as above
4;same as above

Nominal Ledger:
1; Nominal number and description
2; Balance for each code

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Post by brucedenney » 04 Apr 2006, 13:18

As always there are several different ways you can do some of these things.

The three main methods are

ODBC
printing to a CSV report format
and
Send to Excel

ODBC is best geared to doing things time and time again, although once you have set up Excel & MSQuery it is probably quicker than using CSV report formats.

Send to Excel is a great system, but you are limited to getting in Excel what you can see on the screen (It is not available on early versions either)

For this purpose, the CSV Report is the way to go for the Customer and Supplier and the Send is the easy one for the nominal.

------------------------------

Nominal - Send Contents to Excel.

Open up the Nominal ledger module, highlight or filter (search) the view to show only the codes you want.

Go to File>Send>contents to Microsoft Excel

------------------------------

Customers - Suppliers CSV format report.

To do this you need to create a report with all the fields you want in it. Not wishing to do any more work than needed I would start with an existing report and add / remove the fields to it.

The nearest base report is in Customers>Reports>Customer Details Reports>Customer Record CSV (Extended)

This has a few extra fields you don't want, you can delete the columns in Excel when we are done.

It is only missing the Account Balance.

Choose to "Edit" the report

At the bottom of the report add the BALANCE field (don't bother add a heading for it.)

Save the report, in to my customer reports.

Now run the report but chose "File" as the destination.

Sage will now give you a dialogue box to decide where to save the file, by default this is the reports directory, I suggest you change this to somewhere like your desktop, which you can find easily.

YOU MUST also change the file type from .srd to .csv before you save.

Open the file you have just created with Excel and, bar deleting a few additional columns, you should have what you want.

Repeat for suppliers.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Andrew

Getting data from Sage in to excel

Post by Andrew » 11 Apr 2006, 09:35

Thanks for the information, it was worked fine for me. I am also looking to get outstanding (unpaid and unallocated) transactions from the Customer and Supplier accounts in to a csv/spreadsheet.

Is there a report already there that has this information, or can you point me in the right direction?

Thanks

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Post by brucedenney » 11 Apr 2006, 16:05

If it is a one off thing, then again, use a report as above.

The "statements" in customer ledger and reports>supplier statement might be a good place to start, there are also some outstanding and part paid invoice reports that will be worth looking at in >reports>supplier invoice reports and reports>customer invoice reports
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot] and 24 guests