Import Data into Sage Intacct with a Breeze

• May 17, 2021
• Ashley Klapperick

With everyone being cooped up at home for the past 15 months, YouTube views have skyrocketed for DIY (Do-it-yourself) videos. Most individuals probably didn’t realize how many steps were involved in many DIY projects they were completing until they finished a video.

Let me clarify with a short story. We (my husband and I) decided to transition our 3-season porch into a 4-season porch. A simple project, or so we thought. As my husband and I started diving into the renovation, he told me that “y” can’t be completed before “x” several times throughout the process with very detailed explanations as to why. I had no idea that switching a small room from a 3-season to a 4-season porch had so many different aspects to it, nor that with an older home there were more limitations. Long story short, it’s almost done but my patience was tested along the way.

This same concept of complete “x” before “y” is consistent more often than you think. Consider importing data into Sage Intacct; there are specific steps and rules you must follow before you can successfully upload data into Intacct via their CSV templates.

What can you import into Intacct?

• Journal Entries
• AP Bills
• AR Invoices
• Order Entry Transactions
• Purchasing Transactions
• Vendor Updates
• Other Receipts in Cash Management
• Credit Card Transactions
• Time & Expense Transactions

This names only a small list of the transactions you can import into Sage Intacct. Each template has specific requirements to successfully import data into the system. Throughout this post, we will walk you through tips and tricks when populating your import templates to help avoid common pitfalls.

Rule 1: Download fresh templates

It’s always a good rule of thumb to download the original import template from the Company Setup Checklist (Company > Import Data). You can always tweak the template as you need based on the template you are using. This is not to say you could save a template in a format that you like and use it repeatedly; however, down the road, you may run into upload issues. Intacct every now and then makes updates to the header information or template entirely, which can cause an old template to error.

Rule 2: Leave column headers as they are

Never ever change the Column headers contained in the first row of your import template. The one and only exception to this rule is if you are adding a column that’s not for import, which you can prefix with a “#”. For example, you need a column to add notes or run a calculation, you could set the header as “#Notes” or “#Calculation” and Intacct will ignore the column upon import.

However, you can delete, hide re-arrange columns as desired. If a column is not set as required, or not internally required, you may either hide the column or delete the column. The column order can be re-organized as long as header fields are to the left of line fields.

Rule 3: Second-row descriptions are your guide

Utilize the field descriptions within the second row of the downloaded template as a guide. These cells contain important information that assists with populating your data. You may change some of the information in these cells to be more specific to your organization or the type of data you are importing.

Rule 4: Empty fields when updating data

When and if you are importing updates to records or budgets in Intacct, never leave a column heading in your template with cleared or blank data below UNLESS you really wish to clear data. If you delete data from a row of your file or keep a cell blank, Intacct will take that as you’d also like to clear this information in Intacct. Therefore, your data will be replaced with no data. If that is not your desire, be careful and delete unnecessary columns.

Rule 5: Formatting

Intacct imports must be CSV (comma-separated value) file formats. Therefore, amounts may not contain unique characters such as commas, double spaces, or remove leading zero’s. You’ll likely receive out-of-balance error messages or invalid data messages. Let’s look at each:

Commas: Intacct assumes the comma within your amounts or debit/credits means there’s a new column that would cause an out-of-balance error message. To easily remove commas in each of your value-based columns, highlight the amount cells and change the format to General -or – Number and remove the comma and currency symbol.

If you use the Numbers text format uncheck the “Use `1000 Separator” box, which refers to the comma.

Double Spaces: it’s best practice to always remove double spaces from your file before importing. Doubles spaces can cause database issues if loaded into the system. Not sure how to remove double spaces or don’t even know if they exist within your template: highlight your entire CSV worksheet and click on Find > Replace. Enter a double space in the “Find What” section and a single space in “Replace With” and click “Replace All”. (Helpful tip: This seems to be very common in budget imports. You can read more tips on importing budgets in this blog).

Leading zeros removed: if you have dimension IDs that have leading zeros be careful of excel removing leading zeros from the dimension IDs when saving to a CSV template. We never recommend leading zeros in IDs during implementation if it can be avoided. If you do have leading zeros in dimension IDs make sure to format your IDs to “Text” to eliminate leading zeros from dropping.

Rule 6: Correct line numbering

Specific import templates are dependent on correct line numbering, such as Order Entry invoices. Typically, we recommend using an Excel formula to auto-calculate the line number. Meaning if you want to import multiple Order Entry invoices with a single excel CSV file you could use either the invoice number, date, or customer fields as the key for my line numbering formula.

Rule 7: Line item dimension values

When importing AP Bills, Purchasing transactions, AR Invoices, or Order Entry transactions, it is important to fill out the Customer/Vendor field on both the header (left side of each template) AND the transaction line details (far right side of template). Yes, these are two separate columns. When manually entering transactions in Sage Intacct and selecting the Customer/Vendor record in the header, it auto-populates the customer/vendor on each transaction line item. This behavior that is automated directly in the user interface does not apply to imports. If you do not include the customer/vendor information on each transaction line within the CSV template, your import may not fail. As a consequence, your financial reports when looking at the vendor/customer dimensions will be negatively impacted.

Everything we’ve covered in this post today and more can be found in Intacct’s Help Guide on importing data. If you’re still getting error messages consistently and can’t figure out why don’t hesitate to reach out!

What’s next?

Join us for our complimentary Webinar on May 25^th at 10am (PT)/1pm (ET) to learn the answers to the following questions:

• How to outline a process to successfully prepare import templates?
• Understand where to start with troubleshooting CSV import errors.
• See live demonstrations

Reading this post after the webinar? Check the webinar registration page for a recording or reach out to your CLA Sage Intacct team for more information.

Tags: Bill Imports, Data Imports, Importing Tips and Tricks, Imports, Invoice Imports, Journal Entry Imports, Upload Templates, Accounts Payable, Accounts Receivable, Events, General Ledger | 3 Comments »