Importing Accounting Data

Importing Accounting Data from a third-party system is now easier than ever. The following section is guidelines and theories rather than specifics.

Data Export & Formatting

Most software now offers some form of data export. Ideally the format of choice is Excel, followed by comma-delimited and fixed-position. It is better to have too much information than too little.

If their system does not support data export, find a paper-based report that gives you what you need (even better if there is a report-writer built-in), and set up a FILE printer that you can print to. Once you have a print-out in this format, you can use the same approach as a fixed-position report.

NOTE: Importing via QueryGen requires data to be in the same input line. Therefore, any reports captured in a file print need to have their data on the same line. If the information appears on two or more lines, the file must be edited to put the data on the same line.

Reviewing Source Files

The key to a successful import lies in formatting the data in a way that is easily imported. Critical to this effort is consistency of import records. Our tools of choice for imports are Microsoft Excel & QueryGen.

Whenever possible, we prefer the export to be in Excel (XLS) or Comma-Delimited (CSV) format. If a fixed-column .TXT file is possible, we can use that after manually configuring the import (you have to set the beginning and end of each column - easy to do, but tedious) to Excel, but that requires additional work and can especially time-consuming on trial & error export/import. We can also work with .XML. So long as the data can be imported into Excel where we can save it in a .XLS format, we can use it.

Because other systems use different names for their fields than we do, a little detective work is required. After loading the data into Excel, use the Auto-Filter (Data Filter AutoFilter). This will then allow you to click the dropdown on the column headings to reveal unique field settings. In addition. where some settings may be outside a range and require modification, this allows for easy editing of records.

A rule of thumb on a field's importance - where there is only one value, it is likely not as necessary to import. For example, if there is a field called BILLING FREQUENCY and all customers have the same value, then they are likely billing all customers at once, indicating to us that we don't need to do anything special. If there are three different values, that implies that special handling is required, and we have a good candidate to be imported into our CUSTOMER CLASS field.

Importing Order

Files should be imported in the following order.

• System Basics

This information must be in the database first. Without this data, you cannot import much else.

1. Locations - Make sure at least location 0 is setup
2. Plants

• General Ledger Information

1. G/L Groups - Many systems won't have this, but if they do it will likely simplify getting our financial statements to look like their existing ones.
2. G/L Accounts - After importing the G/L accounts, don't forget to set up segments if necessary.

• Customer Information

1. Tax Authorities - Required for importing customers. If there is not a default Tax Authority in their customer file, then add via File Maintenance and assign it to all customers when you build the ARTCUST record.
2. Tax Codes - Many systems may not have this. This can be ignored if everything is initially set to taxable = 'Y'.
3. Salesmen (if applicable - otherwise add via File Maintenance and hardcode during import.)
4. Customer Class (if applicable - otherwise add via File Maintenance and hardcode during import.)
5. Credit Terms (if applicable - otherwise add via File Maintenance and hardcode during import.)
6. Customers - In addition to the fields above, START_DATE will require a value. Hardcode if necessary.

• Product Information

1. Unit of Measure - many of these already exist as defaults in CompuCrete, but verify prior to importing products.
2. Product Class (if applicable - otherwise add via File Maintenance and hardcode during import.)
3. Plants - it is very likely that you may be cloning products across multiple plants from a single entry. Plants must be either imported or added via File Maintenance.
4. Products - Typically, the product file from other system is spartan, and in some cases may not even contain prices. If there are no prices, set the default LIST_PRICE to 999.99.

Additional work: It is very rare that other systems identify any type of automatic quantity (bring-down, etc.) You will likely need to do some update queries. If additives are identified, it is likely that any additive with a unit price less than $20 is a bring-down.