Exporting Data from Sage 50 (US)

by

A reader wrote in asking how to extract all of their data from Sage 50 (United States version) without having to buy any special tools.  Sage 50 offers a built-in utility allowing you to export data in a format that some other programs, including Excel, can use (as shown in the example below).

Step-by-step for using the Sage 50 (US) Export Data Utility:

Step-1:  From the File menu, select Import/Export. (In some older versions of Sage 50 you will need to then select Export Records...)

Sage 50 will display the Select Import/Export window. Unfortunately the Sage 50 Export Data Utility does not permit you to simply select every type of data to be exported at once. You will have to perform the exports one-by-one, program area by program area, data-type by data-type.

Step-2:  Select the type of data (program area) you want to export.  In the example below we have selected Accounts Receivable. When you make a selection in the window on the left the window on the right shows the specific data template types (data templates correspond to either list or transaction data types) available for export.

Step-3:  Once you have made your data type and data template type selections, click the Export button.

Sage 50 will display the Template window specific for the data you selected, in the case illustrated we are looking at the Sales Journal template for Accounts Receivable data. The Template window contains three (3) tabs, the Filter tab, the Fields tab, and the Options tab.

Step 4:  On the filters tab the first field Report Order defaults to 'Date' in most export types, but alternative report orders are available. In this example you can see we have the option of either Invoice Date or Invoice/CM Number.  Select the Report Order option that best fits your needs.

Step5:  In our example using Accounts Receivable we need to select the AR Account ID which is the actual AR Account from which the data will be exported. Select the Account from the drop-down list that best fits your export requirements.

Step 6:  Select the date type (examples include 'This Year', 'This Month', 'Today", etc.) from the Date drop-down list, or specify 'Range' within the Date drop-down and then choose the exact date range within the From (date) and To (date) fields for the data you want to export.

Step 7:  Accept or change of the Filter Range information. When you click on any specific field, a drop-down menu permits you to select specific filter types (All, Exclude, Include, Begins with, Contains, etc.) or Information.

These filters allow you include or exclude particular information based on type and ranges. For example you might only want the information for a specific Customer, or a specific type of customer.

Step 8:  When you have finished making your filter selections, select the Fields tab. By default the 'Show All' (fields) button is selected. You can either select the ShowNone to remove all fields, or simply check or uncheck the fields appropriate for your export within the Show column.

Step 9:  The order in which the fields appear within this window (as shown above) is the order in which they will be exported. The Col # field defines the order. You can rearrange the order by selecting any one row, and then using the MoveUp or MoveDown buttons on the left to change the order. 

Step 10:  When you have finished making your field and field order selections, click the Options tab. By default Sage 50 supplies the default and file name for your export within the Import/Export File name shown in the top portion of this Options tab (illustrated within the blue box in the next figure). You can change the name or file location by simply clicking on the Arrow button to the left of the specified file path and name. Sage will open a Windows Explorer dialog box allowing you to make changes to either the path or file name.

Step 11:  Within the Export Options section of the Options tab select the Include Headings check-box (shown in the red box in the above illustration) if you wish to have the names and headings of the data fields exported along with that data.  In most cases this will be the preferred setting since only the data itself will be exported if you do not include the Headings.

Step 12:  If you export the same data repeatedly using the same file path, you may want to make a selection under the If Export File Exists portion of the Export Options on the Options tab. (a) Select the option "Ask, then overwrite" if you want Sage 50 to confirm the operation first before overwriting the file. (b) Select the option "Overwrite without asking" if you want Sage 50 to overwrite the file without prompting. (c) Select the option "Ask, then append" if you want Sage 50 to prompt for confirmation before appending the information to the existing file. (d) Select the "Append without asking" option if you want Sage 50 to append the information to the existing file without prompting for confirmation.

Step 13:  You can save the various changes you made to this data template so it can be reused without having to make the changes again. Select the Save button on the right of the template and then enter a new template name when prompted (for example you might enter 'My Sales Export'. In the future your saved template will appear within the list of available data type templates once you have selected the data type for export.

Step 14:  When you have made all of your various changes and selections on the template tabs, and saved your template (if you chose to do that - see Step 13), and you are ready to begin the export, click OK. The export process will begin immediately, only advisory messages (which may or may not require a response) will follow. When the process completes the window will close back to the Select Import/Export window.

If you are attempting to export all of your Sage 50 data without the aid of some third-party proprietary utility, then you will need to go through this same process for each of the various data types and data templates that Sage makes available for export.

Reference and Acknowledgement(s):

The illustrations provided within this article were captured from a Sage 50 2018 trial made available by Sage US. The step-by-step was adapted and expanded (during the process of working through the trial steps) from a greatly simplified Sage US Knowledgebase article (ID:11782) which was last modified on 11-1-2017.