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
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
- - - - - - - - - - - - - - - - - - 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
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
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.
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.
Getting data from Sage in to excel
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
Is there a report already there that has this information, or can you point me in the right direction?
Thanks
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
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
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.
Who is online
Users browsing this forum: No registered users and 4 guests