How to send data from Excel to Sage 300 CRE

Overview

The Office Connector license that you use (Write or Import) to send data from Excel into Sage 300 CRE depends on the type of data and whether Sage allows that type of data to be sent directly into the database or whether the data must be processed and validated via a Sage import program. Office Connector Write supports DIRECT writing and Office Connector Import supports the INDIRECT option using text file import functions of Sage 300 CRE.

Click here for information about which Office Connector module (Write or Import) to use for specific data.

Right Tool for the Job

Choose the right tool based the type of data you want to send into Sage and the approach that Sage makes available for that type of data.

Office Connector Write ( DIRECT )

  Generally used with master-file setup-related records, or static non-financial values.

  • Uses Sage ODBC Driver to write data directly to the Sage database
  • Creation of new records is restricted to specific tables that Sage has enabled
  • Updating of existing records is restricted to specific tables and columns that Sage has enabled for writing
  • Permission is controlled by application and file-type within Sage Security Administration under ODBC

    Learn More
Office Connector Import ( INDIRECT )

  Generally used for transactional data

  • Uses Sage Import programs to read data from formatted text files and then create records in Sage database
  • Restricted to the available types of imports offered by Sage 300 CRE
  • Permission is controlled by menu security (i.e., access to the import menu items) within Sage Security Administration

    Learn More

Comparison - Safety vs. Flexibility

Although writing data directly to your Sage database can streamline a process and offer more flexibility, importing data more closely emulates the manual workflow of recording transactional data. Hence some data can only be imported because there would be no “safe” means to allow for it to be written directly to the database…

Write
Import
Data Processing
Inbound data is processed by a Sage program in order to automatically create other supporting/related records and/or to populate other fields based on settings and retrieval options.
Creation of Transactions Transactions are created in a “new” (unposted) transaction file that can then be posted to update the appropriate master file values as well as other applications.
Basic Data Field Validation Data is validated based on type (text, date, number, etc.) and length.
Referential Field Validation References to other records are validated (e.g., Job, Account, Property)
Business Rule Enforcement Business rules are validated (e.g., Accounting Date is not for a closed period, Job is not closed, Insurance is not expired, etc.)
Sage Security Enforcement Enabled via options in Sage Security Administration
Use of Sage License Uses a concurrency of a Sage application (beyond just Office Connector)

Typical Import Process

  1. Generate a formatted text file from Excel using Office Connector Import
  2. Use the applicable import task in Sage and identify the file to be imported.
  3. View the import journal produced by the Sage import task and address any rejected records. Correct any issues and re-import the rejected information.
  4. Post transactions that were created by the import task.

Cart

"One of the more significant benefits to using Office Connector is the ability to 'search' for information. If someone is out of balance by a certain dollar amount, it is extremely simple using Office Connector to search for and find the reason why."

K. Saland, Terrus Real Estate Group