Getting Sage 300 CRE Data Into Excel

How much time do we spend in Excel entering data vs. analyzing it? Too much. Keep reading to learn some tips and tricks using Sage 300 CRE data exchange features to make your data work more efficiently for you.

Many software applications – like Sage 300 Data –  are excellent at manipulating and maintaining information. Often they have built-in reports, dashboards, and complex entry screens that allow view-only access.

For more advanced data analysis, there are data output customization tools, like Sage 300 CRE’s Report, Inquiry, and Financial Statement Designers, and 3^rd party products like Crystal Reports and AnterraBI. While they can be powerful and feature-rich, these tools often require specialized training to optimize their benefit and are often difficult to manipulate on-the-fly.

Sometimes, the inability to change data is a good thing because it builds confidence in the information and standardizes formats. Other times, it hinders the ability to quickly analyze, structure, manipulate, or use the data in other external systems.

Here is where Microsoft Excel shines. According to Forrester Research, 81% of businesses use Excel. With that adoption rate, the chances are high that the necessary skill to develop quality Excel-based solutions already exist within an organization.  However, the trick is getting the data out of your software and into Excel.

Aside from manual data entry, there are three general methods to move information into Excel.

Copy/Paste	This is probably the most common method. Any user of Excel has copied or moved data from one cell to another within the worksheet. The same concept applies to copying data from another data source, such as a text, pdf, or even another Excel file into Excel. As long as the source data is electronic and formatted so that Excel can interpret rows and columns of data, Copy and Paste can be used.
Push	Some applications, not all, support the ability to push their data directly into Excel.  Usually, data is pushed into a new workbook or worksheet.  When available, this is often used in conjunction with the Copy/Paste step and requires additional formatting and manipulation to get the data into its final form. As a result, the value of pushing data diminishes when the recurring nature of a workbook increases.
Pull	Excel can directly pull information from an external source using the Get Data feature (Data à Get & Transform Data)*. The best part of this feature is that the returned data is not static and can be refreshed at any time.  Pulling data bypasses the whole export, import, reformat routine needed to varying extents with the other two methods. There is no better method to use for often used and updated workbooks.

*The exact name depends on the version of Excel.  The reference above is from Microsoft 365.

3 Ways to Use SAGE 300 CRE to Push Data Into Excel

1.     Use Delimited Text Files

Excel can open text files that are formatted with a variety of field separators (delimited). Think of the fields as columns of data that need to be identified and separated.  The most popular delimiter is a comma, where a comma marks the separation between two pieces of data. This is known as comma-delimited (also comma-separated values (CSV)).

In the example below, there are five fields of data in the first row or record. Each separated by a comma.

Comma-delimited files are commonly used to transfer data from one application to another because most database systems can export and import comma-delimited data. Excel can do this too using the “Text to Columns” feature (Data Tab à Data Tools) to import data, and the *.CSV Save As format to export data.

2.     Push Inquiries to Excel

The data displayed in any Sage 300 CRE inquiry is easily pushed directly into Excel using the “Export to Excel” button on the inquiry menu toolbar. Columns entered on the fly are included in the data push.

Each data push opens a new Excel workbook where the data will neatly follow the source inquiry’s familiar rows and column format.

3.     Push Reports to Excel

You can “print” or save reports in Sage 300 CRE to various file types, including an Excel Workbook.  However, creating usable data in Excel using this method often requires much effort.  For example, headers that span multiple columns are typically split into different columns.  Numeric columns also randomly fall prey to this and require excessive manual intervention to fix.

Pushing reports from Sage 300 CRE to Excel is not recommended and should only be used in occasional, one-time scenarios.

Using ODBC To Pull SAGE 300 CRE Data Into Excel

ODBC DSN Connection

Each database application has one or more methods for external programs to connect to the data. Like many others, the most accessible external access to Sage 300 CRE data is Open Database Connectivity (ODBC). This method requires each Sage 300 CRE database to have a unique Data Source Name (DSN), configured on each computer through Window’s “ODBC Data Source Administrator (32-bit).” The ODBC DSN is referenced in Excel, or other programs such as Crystal Reports, to identify a specific Sage 300 CRE database.

External data connections, like through ODBC, are saved with an Excel workbook and can be refreshed at any time. A workbook can contain multiple ODBC connections, either to the same or different DSNs, which helps consolidate data from sources. As a bonus, data in the same ODBC query doesn’t need to be in a continuous range but can be interrupted by columns of other data or formulas that will dynamically expand when the data set grows. As a result, a workbook using ODBC to pull data requires far less formatting to be presentable after a refresh than other methods of getting data into Excel.

Before the concern arises about unintentional access to sensitive data, Excel users cannot access Sage 300 CRE data unless granted such rights in Sage 300 CRE’s Security Administration.

Next Steps

Take the time to evaluate the Excel workbooks you maintain. Which ones require the periodic entry of new data? Of those, how much time is spent just on entering new data versus analyzing the data? Since your time is better spent evaluating data instead of re-entering it (and correcting errors associated with re-entry), identifying workbooks that are the most time-consuming to maintain presents the best opportunities to create efficiencies. An upfront investment to increase automation and minimize data entry will pay dividends in the long run.

Got questions? Connect with an experienced Aprio advisor today.

Schedule a Consultation