CLA’s Sage Intacct Spark #9: Importing Best Practices

• December 13, 2018
• Kathy Jastrzebski

Have you ever tried playing Connect Four with a 4 year old? Good luck stopping them from filling up the Connect Four game with all their pieces and singing “I win! I win!” over and over again. Unlike a 4 year old playing Connect Four, there are certain rules to learn and abide by when importing files with Intacct.

What Can I Import to Intacct?

• Payroll Journal Entries
• Statistical Journal Entries
• Other Receipts in Cash Management
• Sales Invoices
• Charge Card Transactions

And that’s just to start! However, in order to use these efficiently and reap the time-saving benefits, you must first master the importing “rules of the game”. This post will take you through tips to keep in mind when formatting your import files to help you avoid common pitfalls.

Rule #1: Download Templates

The best rule of thumb is to download the original import template from the Company Setup Checklist (or Company > Import Data in the new Beta UI). Then tweak as you need based on the “things you can do” section below.

The original template will display all available fields you can import (even custom ones) as well as correct column headers (which should not be changed) and any requirements for individual fields (maximum length, valid values, valid format, etc).

Rule #2: Column Headers

Column headers contained in the first row of your import template should never be changed. The one exception to this rule is if you are adding a column not for import. In this case you can prefix it with a “#”.  For example, if you have a column for Notes, you could set the header as “#Notes”. Intacct will ignore the column upon import.

Rule #3: Descriptions as Guides

Field descriptions contained within the second row of the downloaded import template contain important information. Always review the following:

• Required: Yes – Any field with Required set to Yes indicates (as you might’ve already guessed) it must be filled out in order to import. If left blank, the import will fail.
• Length – This is the maximum number of characters a field can have. If the field is required and you exceed this maximum, you can expect a failure email delivered shortly to your inbox.
• Valid Values – Key insight on valid formats as well as values if the field is a pick-list.

Rule #4: Empty Fields

When importing updates, never leave a column heading in your template and clear the rows of data below unless you really wish to clear data. If you delete data from a row of your file, Intacct will take that as meaning you’d also like to clear this information in Intacct. Your data will be replaced with a blank. If that is not your desire, be careful of this. I recommend following the rule of deleting unnecessary columns.

Rule #5: Comma’s

Intacct imports must be CSV (comma-separated value) format. Therefore, amounts may not contain comma’s otherwise Intacct will take the comma to mean a new column. You’ll likely receive out of balance error messages.

To remove: highlight the amount cells > change the format to General.

On the topic of format, it’s also worth noting to be careful of Excel removing leading zero’s from your dimension ID’s (which is exactly the reason we never recommend leading zero’s in ID’s during implementation if it can be avoided).

Rule #6: Careful of Double Spaces

Always remove double spaces from your file before importing. Double spaces can cause database issues, so it’s best to remove them. To remove: highlight your entire sheet> click Find & Replace > Replace. Then enter a double space in the “Find What” section and a single space in “Replace With”. Then click Replace All. A few seconds-worth of work which will save you time long-term.

Rule #7: Line Numbering

Certain imports, such as Order Entry, are dependent on correct line numbering. I generally recommend an Excel formula to auto-calculate the line number for you. For example, if I wanted to import multiple journal entries with a single excel csv file, I can use the date field as the key for my line numbering formula:

Rule #8: Line Item Dimensions

If importing AP Bills, Purchasing Transactions, AR Invoices, or Order Entry Transactions, it is important to fill out both the Customer/Vendor field which is required on the template which populates the header (left part of the template) AND the line detail vendor/customer as well. These are two separate columns. Although in the UI, selecting a vendor/customer on the header will auto-populate the line item coding, the behavior is not the same for imports. Without this information, while your import may not fail, your financial reports looking to Vendor/Customer dimensions will be negatively impacted.

Things you can do:

1. Delete Columns: If a column is not set as required, then you may delete or hide it in your template.
2. Re-arrange Columns: As long as header fields are to the left of line fields, the column order generally can be re-organized.
3. Use # for Comments: Just like columns, you can add informational rows to your template but exclude for import by prefixing with a “#” in the left-most column.
4. Debit/Credits: Use the debit column to populate with debits/credits using a negative to denote a credit.

Let the Games Begin! What’s Next?

You can always find more information in Intacct’s Help Guide on importing. If you’re still getting that same error message and at your whit’s end, reach out!

With only 2 Sparks left in the series, and 2 weeks left in the year, subscribe!

Tags: CLA Sage Intacct Spark, Data Import, Imports, Intacct, Journal Entries, CLA Sage Intacct Spark | 1 Comment »