Exporting Data from Sage 100 ERP (US)

It seems a lot of people are trying to migrate from Sage general ledger products to other software, or at least that's what I gather from many of the forums I participate in.  It also seems that these people insist on trying to take their historical details with them which means exporting data and importing it into whatever new product they migrate to. 

A few weeks back I wrote a step-by-step on exporting data from Sage 50 (US), and with a recent forum posting about Sage 100 export I thought I would do another article taking you step-by-step through the process using the internal utility for exporting Sage data.

So here is a copy of Sage 100 ERP (US), it may not be the latest and greatest version, but it's the one I have available.  I can tell you that while the GUI (graphic user interface) maybe slightly different in the 2017/2018 versions, the workings, including the Data Exchange utility used for exporting data is the same, in fact I don't think it's changed since the 'ice age'.

Step 1 - Log-in to Sage 100 ERP, the main landing page appears (as shown below).

Step 2 - Within the Task tab on the left hand side of the landing page, click the Modules folder to expand the list of Sage 100 ERP modules.

Step 3 - Within the expanded list of modules appearing in the Task tab, click the General Ledger folder to expand the list of available sub-folders related to general ledger functions.

Step 4 - Within the expanded General Ledger folder appearing in the Task tab, click the Utilities folder. The expanded list of tasks available within the Utilities folder will appear in the right hand window.

Step-5 - Select the General Ledger Exchange task within the right hand window. Sage will display the Sage ERP Change Accounting Date window.  Accept the default if you want the latest information included in your export.  (Note: This is not the export period dates, this is they date thru which the General Ledger has been updated.) Click OK.

Step 6 - Sage will display the General Ledger Exchange interface listing recent exchange settings. Click the New Exchange Setting button in the lower left to proceed with configuring a new export of data.

Step 7 - Sage opens the General Ledger Exchange Settings Wizard, click Next to proceed with configuring the export.

Step 8 - The wizard takes you step-by-step through the process of configuring your export, the first selection involves selecting the method to be used for the data exchange. Select Export Data, then click Next to proceed.

Step 9 - The next selection involves the type of data you want to export. There are 3 options, Accounts, Budget & Activity, and Transactions.

Accounts - exports your Chart of Accounts.

Budget & Activity - exports your budget data or account activity by fiscal period.

Transactions - exports transaction detail from the General ledger Detail Posting file. In this article we are discussing the export of transactions so we will select Transactions within the export type box, and then click Next to proceed.

Step 10 - You need to provide a name for this exchange setting, the Name is required, the description is optional. Click Next when you have completed the required (and/or optional) information.

Step 11 - The Wizard asks you to select the specific transactions to include in the export, by default the source journal, journal/register number, posting date, account number, description, main and group codes are set to 'All' as shown in the illustration below.

Default setting - transaction fields are set to 'all'.

Configure Account numbers - if you wanted to only select transactions from specific accounts you could select those accounts using the Operand and Value fields for the Account Number fields. The example below illustrates how you can include one or many different account numbers within your selection.

In our example we want to export all of the transactions for all of the field and account options within Sage, so we simply accept the default setting, and click Next to proceed.

Step 12 - Select the File Type that will be created for the export, and also provide the File Name (including file path) that will be used. (Note: This file name can either be the same, or different, from the Exchange Name you created earlier for this export.)

In our case we are going to select an Excel file type from the Exchange File Type drop-down list, and then select our file path and file name using the familiar Windows file path/name dialog box as shown below. When we have completed the necessary information we will click Save to continue to the next step.

Step 13 - You will select the Exchange File Format that is appropriate for your export. In most cases you will select the Standard File Format.  In a limited number of cases you may need to select the Alternate File Format.  In the case of this example we will be using the Standard File Format, when you have made your selection, click Next to proceed.

Step 14 - We are just about finished with our configuration, and ready to start the actual export of our transactional data. Be certain to click the checkbox 'Execute the exchange when clicking Finish.'  This will start the export process immediately upon clicking Finish.

In our case the export data will be published into Excel. Sage does not actually open Excel to perform the export, it simply creates an Excel formatted file. (Note: If you selected an alternate export format back in Step 12, your data will be published in that format; Sage will not open the applicable file or format editor or application.)  When the export is complete Sage will display a 'Export is complete' message (as shown below).

Step 15 - Open and review your data using the appropriate application. In our case we exported the data into Excel, so we would open the export file in Excel, as shown below.

If you find the data isn't what you were looking for you will need to experiment with the various options available within the Wizard.

Should the volume of data being exported exceed the Excel row count for a worksheet, Sage will automatically populate the remainder of the data to subsequent additional worksheets within the same file. 

Using this Method vs. Alternatives

This method provides a convenient way to export most transactional data from Sage 100 (US) without any need to purchase costly other software.  Unfortunately not all data is available using this method. Some Sage modules offer alternative ways of selecting and formatting data into Excel reports that can be used to bypass the limitations of the 'data exchange' method of export.

Individuals wishing to do data extraction from Sage as a professional service might want to consider 'MASTransit' as an alternative to the methods shown and described above.  Insightful Accountant published an article titled 'Tools of the Pro's-MASTransit' back in 2014 discussing the purposes of the tool and its functionality.