Exporting Sage 300 CRE data to Excel

by Kyle Zeigler, Sage Senior Certified Consultant

Taking the fast train

Let’s be honest! Despite your Sage consultant’s recommendations to eliminate outside systems, sometimes you just want your Sage 300 CRE data in Excel. And when we approach year-end preparation time, it is particularly helpful in preparing reconciliation worksheets that you may eventually send to an outside CPA or auditor.

There are hundreds, if not thousands, of prebuilt reports and inquiries in the software to get you started, and custom versions of reports and inquiries can be created using Report Designer, Crystal Reports, and Inquiry Designer if you have purchased those applications.

However, for those who love the on-the-fly flexibility and power of Excel’s subtotals, pivot tables, and other features, a number of options are available to you.

The scenic route

Office Connector (OC)

This is another Sage product that can be purchased through your local business partner. The various licenses let you create refreshable queries of your Sage 300 CRE data, as well as create custom import templates and even write data directly to certain fields in your database.

ODBC/Microsoft Query

This method requires no additional purchase and uses Microsoft components that are usually automatically installed on any computer using a Windows operating system. With a little setup, you can create read-only refreshable queries, but the manipulation of the data is sometimes more limited than what you can do with Office Connector. For instance, you can’t insert a calculation column in the middle of a query as you can in OD.

Printing reports to .txt

For those of you who have tried this, you know that it works only some of the time and that the results can just be…well, ugly. The reports you choose to print to a .txt file type should be those with well-defined columns, fields with duplicate information printed on every line of detail, and few or no subtotals so that when you import the .txt file into Excel, there is a minimum amount of cleanup to do.

Inquiries

Several versions ago, Sage heard the cries of users desperate for a better way to get data into Excel and finally enabled that celebrated button on the Inquiry window. Inquiries are possibly one of the handiest features of the software, with the ability to add and remove columns, filter the contents of the inquiry, even add formulas to do calculations, and either print the contents of the inquiry to .pdf or paper – with totals! – or export the contents to Excel. Some formatting may be required in columns used for sorting (leading blank spaces are sometimes added to dates, job numbers, etc.).

If you would like information about the advantages / disadvantages of these choices or  training in how to use them, call 480.423.8300 or click below!

request-support