Prepare your file for CSV import

Importing data using the Company Setup Checklist is a fast way to get your information into Sage Intacct. However, the way that you prepare your CSV files directly affects the success or failure of importing your data.

Process overview

The following illustration shows the steps to download, prepare, and import a CSV file.

Most imports start with getting the most current template from the Import data list. When you get your templates from the Import data list, you get templates that have been tailored to your specific company, including specific dimensions.

Next you add your data, using the correct formatting as explained in the template. After you've added your data, review the data with our best practices in mind. This helps eliminate anything that might make your import fail.

After preparing your data, you’re ready to import.

Download a template

Download a new blank template from the Company Setup Checklist whenever you import information into Sage Intacct. Using a downloaded template is important because:

• Templates in the Company Setup Checklist are automatically customized based on your company configuration, including any dimensions and custom fields you created.
• Templates can be updated by Intacct at any time, especially when a new version of Intacct is released. When templates are updated, an older template might not work upon import.

To download a template from the Company Setup Checklist:

1. Go to Company > Setup > Configuration > Import data.

   The Company Setup Checklist opens. You’ll find a list of areas that you can import information into Intacct.

2. Decide on a type of information to upload, and select the Template link associated with that information.

If you do not see the template that you want on the Company Setup Checklist, go to Download a CSV import template.

Add your data

After you download the appropriate template, it's time to add your data.

The following table represents a portion of a standard CSV file for importing Budgets. The top row is the header row and the second row contains descriptions of the kinds of data to enter in the column.

Header row	DONOTIMPORT	BUDGET_ID	ACCT_NO	DEPT_ID	LOCATION_ID
Header description row	# Rows that start with a number sign (#) are ignored during import	Field Name: BUDGET_ID UI Field Name: Budget ID Type: Character Length: 20 Default Value: None Valid Values: Alphanumeric and underscore Dependencies: Must exist in the company prior to import. Required: Yes Editable: No	Field Name: ACCT_NO UI Field Name: Account Type: Character Length: 4-6 Default Value: None Valid Values: Any; Must conform to primary/sub account specification in Company > Setup: Company Info Dependencies: Must exist in the company prior to import. Required: Yes Editable: No	Field Name: DEPT_ID UI Field Name: Department Type: Character Length: 20 Default Value: None Valid Values: Alphanumeric and underscore Dependencies: Must exist in the company prior to import. Required: No Editable: No	Field Name: LOCATION_ID UI Field Name: Location Type: Character Length: 20 Default Value: None Valid Values: Alphanumeric and underscore Dependencies: Must exist in the company prior to import. Required: No Editable: No

Each template contains a set of pre-defined headers, such as BUDGET_ID, ACCT_NO, and the like. You enter your data into the blank cells under the header and header description rows without changing the headers.

The following table is an example of a portion of the template after the data has been added. The description row has been hidden for this example.

BUDGET_ID	ACCT_NO	DEPT_ID	LOCATION_ID	Month End April 2021	Month End May 2021	Month End June 2021
Budget 1	5000	Admin	Sydney	3000	3000	3000
	6040	Admin	Sydney	15	15	15
	6120	Admin	Sydney	50	50	50
Budget 2	1200	Land	San Jose	6000	6000	6000
	1700	Land	San Jose	500	500	500
	2200	Land	San Jose	500	500	500

To add your data:

1. Open the template that you downloaded from the Company Setup Checklist.

2. Save the file as a comma-separated value (CSV) file. When saving from Excel, select the UTF-8 (Comma delimited) file type.

   If your file contains special characters such as words containing diacritical marks, the only supported encoding format is UTF-8 (comma delimited). Saving the CSV file in other file encoding formats might import characters incorrectly and create duplicate records.
3. Enter your information. Use the header descriptions as a guide for how to enter information correctly.
   • Do not change the headers.
     The header titles in the header row correlate to fields in UI. Incorrect header titles will cause an import error. When you download a template from the Company Setup Checklist, the correct headers are in the template.
   • Read the header descriptions.
     The header description is the cell under the header row that explains what type of information is accepted for each column. If you enter information in the wrong format, or with a value not accepted by the column, it will cause an error in your import.
   • Enter required information.
     Some data is required, while others are not. Read the description field to determine which information is required. If you do not enter required information, your import will fail.
   • Use initial caps only
     For fields with predefined valid entries (such as True/False), use initial capitalization. For example True, not TRUE or true. IDs require exact case matches, while predefined entries require initial capitalization.
4. Save the file after entering your data.

The file will have the file extension .csv after you save it.

Simple vs. complex imports

When importing complex data into Intacct, make the import as small as possible.

For example, if you’re importing 30 columns of data, limit the number of lines in the CSV file to 5000 or less. This will ensure that your import uploads correctly. On the other hand, if you’re only importing 4 columns of data, 10,000 lines or more could easily be imported.

The complexity of your data impacts the ability of Sage Intacct to import your data.

About CSV templates

There are some things that you should know about the CSV templates and how they behave before adding your data.

Remove all commas from your CSV file

Intacct recommends removing all commas from your CSV file.

A CSV file is a Comma Separated Values file (also sometimes known as a comma delimited file). If there are commas in the data of your file (for example, if your numbers are formatted like this: 10,000, or if you use commas between words in a field), your data will be shifted one column over because that's how the file is intended to work. This will either cause errors in the information, or cause the whole import to fail.

Column order

You can change the order of the columns as you see fit, as long as the information in the header cell does not change.

However, some templates require information to be imported in a specific order, for example: transaction data shouldn’t be imported before information about the transactions has been imported. Header columns containing information about the entire import need to stay to the left of detail columns (columns that contain the details of an account, transaction, supplier, etc.).

Intacct recommends reviewing the column order of the original template before moving around columns.

Add additional columns

If you need to keep other related information in your template document, you can add your own columns for this information.

If you add your own columns, use a # (pound sign or hashtag) symbol before the name of any column you add. For example, #BudgetNotes, or #ClientInfo. If by chance you use a name already in use by Intacct, the # (pound sign or hashtag) will make sure the information in your columns is not imported by accident.

You can also use the # (pound sign or hashtag) to write notes to yourself in your template. If you place the # (pound sign or hashtag) at the start of a row, the whole row of information is ignored during import. You can create rows of notes by starting the row with # in the first column.

Hide columns for easy data entry

When editing your file in Excel, you can hide columns you will not be using for data entry.

Data must conform to the business logic of Intacct

For example, the Budget ID field on the Budget Information page allows a 20 character ID to be entered. It follows then that the Budget import template would have the same limitation: if you try to import a Budget ID with more than 20 characters in your CSV file, your import fails.

How data should be formatted is found in the header description row, under the header row.

You can also see a field-by-field description for your specific template in this help. Go to CSV import: By application or subject, find the template you want to know more about, and select the corresponding link.

Blank fields overwrite existing data

A blank value in a cell is considered to be the desired value (a blank) for the field referenced by the column heading for the record being imported.

In almost all cases, a blank field will overwrite existing data in Intacct to also be blank.

Say you had the existing value 111 Elm St. as the address for a customer and you import a blank for this field. If you go to the general user interface and examine the contents of that field, you’ll see that the original value 111 Elm St. is replaced by nothing and the field is blank.

This is not true, however, for two templates:

• Update Supplier 1099 Transactions
• Update Supplier 1099 Opening Balances

If the value is blank in certain fields, the import does not overwrite the existing value. To remove the existing value in these few cases, enter the value NULL. These cases are clearly identified in their header descriptions.

Calculated field values

If you used a formula to come to a value in a field, you can leave the result in place as long as you match the Intacct field value for decimals.

Accounting application page help

In most cases, there's an exact match between the application pages (and the business logic with which they behave) and the data that you import. This includes the constraints on that data, and any dependencies that might exist between the data in the import file and other data in Intacct, or other data that you also import.

After you’ve added all your data, you save the Excel spreadsheet as a CSV file.

Debits and credits must balance

When importing a file that includes debits and credits, your debits and credits must balance. Intacct adheres to accepted accounting rules, which require debits and credits to balance.

Likewise, if you require accounts to balance by location, then when you import location balances using a CSV template, the location debits and credits must also balance.

Duplicate records

If an identical record already exists in Intacct, the import for that record typically fails because Intacct detects that the record exists and does not allow duplicates.

Duplicates are not allowed in account numbers, departments, locations, employees, customers, and suppliers. Although you can have duplicate transactions, the transaction summary name must be unique per application.

Line numbers

See the CSV import: Line number examples page for more information on how to use lines numbers in your CSV imports.

Special characters

UTF8 is supported in imported files, so character accents and other special characters are imported correctly.

Intacct recommends you remove commas from all numbers and do not use commas in any text fields. See Remove all commas from your CSV file for more information.

CSV data exported from non-Excel software

When exporting data from other applications, the other software might use a non-standard approach to quoting, which could cause problems when importing into Intacct.

As noted above, Intacct recommends removing all commas from text fields in a CSV file. However, there could be times in which removing the commas is not the ideal solution. In this case, CSV files do allow quotes around a value that contains a comma, and Intacct accepts this in an import. For example:

There might also be instances when you need to include a quote mark inside a statement with a comma. In this case, you would use a second set of quote marks, as follows:

Avoid common import errors

Intacct recommends following these guidelines to avoid import errors.

Import a CSV file

Now that you've entered your data and looked through it for any of the common issues imports can have, you’re ready to import your file to Intacct.

To import your CSV file:

1. Go to Company > Setup > Configuration > Import data.

2. Find the information type that you want to import, and select the corresponding Import link.

3. In the Import Company Information dialog, set the following and select Import:

   Import Company Information: options

   Import file option descriptions

   Option	Description
   Browse	Use the Browse button to locate and select the CSV file you want to import.
   Date format	When you select a date format here, Sage Intacct attempts to import your file using the selected date format. If Intacct can’t import your file with the selected date format, it attempts to discover the date format used in your file and import the data using that date format.
   File encoding	Leave this option set to "auto-detect" unless you're unable to import your file. Depending on your operating system, you may need to select a different type of file encoding.
   Process offline	Some imports can take considerable time, depending on their size and how much processing the system must do on the uploaded data. If you're not sure how much time your upload will take, select Process offline. When the system completes the process, it sends an email to the address you provide in the Email results to this address field. The system also sends you any errors that may have occurred in the process. If you prefer to wait until the upload process is complete, don’t select the Process offline checkbox. Transactions and other records that are processed offline show a source of System in the audit trail, instead of CSV import.
   Email results to this address	Email address to which import results and errors should be sent. You can enter multiple email addresses, separated by a semicolon (;) or comma (,). Sage Intacct highly recommends entering an email address.

After an import, Sage Intacct informs you if the import was successful and how many records were imported.

In a completely successful import, the confirmation page lets you verify all the records in your original file were, in fact, successfully imported without having to verify them manually.

Import offline

Some imports can take considerable time, depending on their size and how much processing Intacct must do to the data. When in doubt, select Process offline on the import utility page.

When the offline process is complete, Intacct sends an email to the address you entered in the Import Company Information dialog (where you imported your file).

Resolve errors

If any records contained errors and the import failed, you’re notified of which ones, and how to correct the problem in an email. For more information about resolving import errors, see Find and fix CSV import errors.

Order of CSV imports

In some cases, CSV imports must be performed in a specific order because of dependencies within the data. For example, if you have transactions that require locations, but you have not entered or imported the locations, the transaction import fails.

It’s always best to follow the order of the steps as they’re presented in the Company Setup Checklist when performing any type of import. Not all the steps are required. For example, you might not use territories anywhere in your accounting data, therefore creating them is optional.

In a few cases there are circular references. For example, an employee record can reference a department and vice versa. Problems that would otherwise result from such circular references have been eliminated for the purposes of importing by removing certain items. If you absolutely must have this data, you need to enter it manually.

Automate imports

You might be able to automate and schedule your imports using a third party application, such as Workato. Contact your account representative to learn more about this option.