Exporting Control Centre data to a CSV file for (e.g.) Excel

This article describes how to export client contact details from Sage Taxation to a CSV file for use in other programs such as Excel, Outlook or any program that can open a CSV file.

What's involved?

This process relies on using both letters and reports to produce the final file.

• The report needs to identify which clients will be output on to the letter
• The letter needs to contain the mail-merge fields required in your CSV file
• When the letter is run against your report, you can save this from Microsoft Word into a CSV file.

Once you have set up your custom letter and report, these will be saved in their respective custom letters and custom reports folders ready for the next time you want to run them.

Prepare your report

Before you start working through this article, you'll need to have a report that will pick up the required clients. This can either be a standard report or a custom report. We recommend that you produce the report beforehand and make sure you're happy with the clients it's picking up before proceeding to create a letter which will output the data.

There is a standard report called All Database Entries if you are looking to pick up every client in your dataset

Generate a custom letter to output to CSV

1. Within Control Centre select Tools, Letter Generator, Custom Letters, New and give the letter a name to recognise it by for future reference
2. Select OK. Microsoft word will now load. This may take a few seconds to appear
3. When the merge field list loads up, select the fields you wish to appear on your CSV file

Most commonly used fields can be found under Main Details, Client Details

1. The merge fields will load up and display as shown below, place a comma between them (e.g.):

[103|Forename(s)], [104|Surname], [120|E-mail address]

1. Click Save in Word and click close on the Mail Merge Fields window. You can now close down your Word template.
2. You will now be returned to the letter generator in Control Centre. Preview the letter you have just created from Custom Letters and choose the option to use list of clients generated by the report.
3. Click Select report and choose the report you wish to use

If you have just created a report, this will be listed under custom reports

1. You will now see a list of all clients in the word document with forename, surname and email (e.g.):

Joe, Smethurst, [email protected], Grantham, [email protected]

Some versions of Microsoft Word may display each client on a new page, this is normal and won't affect the finished result.

1. Within Word, select File, Save As, and select "Plain Text (*.txt)" from the "Save as type" drop-down

Choose somewhere easily accessible like your desktop as you'll need this file location next.

1. Browse to the file location you have just saved to and double click on the text file to open in Notepad.
2. Select File, Save As. Change the Save as type drop-down to "All files (*.*)"
3. In the file name box, rename the text file and change the file extension from txt to csv and save the file.

This file can now be used in any spreadsheet such as Excel or proceed as below to import to Microsoft Outlook.

Dealing with blank rows

The export process will create an output with a blank row in between each line. You can remove these in the desktop version of Excel.

Show me how

These instructions are for Excel 2016 but should work in other versions. Please speak to your local IT support if you need assistance beyond these instructions.

1. Open the CSV file you have created. You'll need to use the desktop version of Excel rather than the online (365) version
2. On the Home tab, find the 'Editing' and 'Cells' groups. The group labels are located just below the toolbar icons.

1. Select Find & Select
2. Select Go to special
3. Select Blanks followed by OK
4. Still on the Home tab, find the 'Cells' group
5. Select the Delete icon

Select the delete icon itself, rather than its dropdown menu.

Please confirm the number of rows is as expected before saving your spreadsheet.

Limitations of this process

Although there is no official functionality to export from Control Centre to a CSV file, this article provides a workaround that is suitable for some customers.

There are certain limitations that may require some manual data manipulation once the data is exported to Excel (e.g. removing blank rows or using the Excel CONCAT function to combine address and postcode into one cell) - this is due to the database structure and can't be altered within the software.

If you require further assistance with manipulating your data once exported, please speak to your local IT support.