Find and fix CSV import errors
Related information
Related topics
There are a number of reasons CSV imported records fail, such as duplicated entries or invalid field content. Sage Intacct provides information about the failed records in an attempt to help you fix the import error.
Find the error message
The import confirmation page indicates how many records failed. This page also contains a link to download a CSV file, which contains ONLY the failed records.
Records that are successfully imported are not returned in the CSV file.
Select the link and download the CSV file.
If you entered your email address into the Import Company Information dialog box, an email is sent that contains the error messages for your attempted import. The email lists the error code, and then each unique instance of that error in the file you attempted to import.
The error message email is sent to the email address associated with the logged in user ID logged at the time of the file upload; otherwise, the email address for the company contact is used.
The import pages also allows another email address to be explicitly entered in the Email results to this address field.
If you did not enter your email in the Import Company Information dialog box, and you do not select the link in the confirmation page, there is no way to retrieve the CSV error report file for the import you just did.
Best practice: enter your email in the Import Company Information dialog box at the beginning of every import.
The reason you are sent a separate email with the error messages is because a record could fail for more than one reason. Displaying all the text for each record in the CSV file could become quite cumbersome and hard to read. Furthermore, it is often the case that thousands of records are uploaded. If thousands of errors fail, the CSV file could become quite bloated.
Read the error message email
The error messages for your failed upload file can be challenging to read. Intacct recommends the following strategy:
- Start at the top of the email and look at each error message section.
- Within each error message section, start reading from the bottom up. The error at the bottom of the section is the first error encountered during import.
Start at the top of the email
Error messages are presented in sections, and the sections are listed in the order the errors are encountered in your file.
For example, in the graphic above, the first error message reads:
===================================================
Error BL01001973
===================================================
BL01001973-1 Description: Duplicate account number The account number
'4150' already exists
BL01001973-1 Correction: Enter unique account number
BL01001973-2 Description: Unable to create record Could not create
GL Account record!
===================================================When reading the error messages, start with the first section, such as the one above.
BL01001973 above, are Intacct internal codes. These internal codes might help Intacct locate the source of a systemic issue, if one is identified.===================================================
Error BL03000019
===================================================
BL03000019-1 Description: Invalid contact data entered.
TRANSACTIONTYPE is a required field and can not be empty
BL03000019-1 Correction: Some of the fields you have entered
are invalid. Please correct them with proper values.
===================================================Read from the bottom of the error message up
In the above example of an error message, look at the error BL01001973-2 Description: Unable to create record Could not create GL Account record! first. This identifies the first error that was encountered during import. It's not very informative, however, so look at the next error message for more information.
The next error message in the list (as read from the bottom up), BL01001973-1 Correction: Enter unique account number, identifies the reason the GL account record couldn't be created, but it's not yet clear what to do to fix the problem.
The final error message in the example above (as read from the bottom up), BL01001973-1 Description: Duplicate account number The account number '4150' already exists, describes more fully the cause of the error and suggests a fix: find and delete the duplicate account number.
It is possible to have several errors for a single failed record. You must resolve all the errors in a row before attempting to upload the file again.
Avoid common import errors
Intacct recommends following these guidelines to avoid import errors.
In Microsoft Excel:
- Select all cells.
- Right-click the cells and select Format Cells.
- On the Number tab, select Text and then select OK.
- Save the file.
The short date format is formatted like this: 3/15/2021
In Microsoft Excel:
- Select a column of dates.
- Right-click the column and select Format Cells.
- On the Number tab, select Date and select the short date format.
- Save the file.
While spaces within memo fields are allowed, make sure there are no leading or trailing spaces in any field (not just memos). Leading and trainling spaces may cause import errors. For all other fields, remove unnecessary spaces at the start or end of the data.
In Microsoft Excel:
- Select a range of cells.
- On the Home tab, in the Editing group, select Find & Select and select Replace.
- In the Find what box, press the space bar once. Leave the Replace with box blank.
- Select Replace All. A prompt opens telling you how many spaces have been removed.
- Save the file.
Commas are used to separate fields in CSV files.
To prevent data from shifting into the wrong columns, avoid using commas in numeric and text fields.
If you must include a comma in a text field, always surround the field with double quotes, for example "Smith, John".
Headers in your CSV file must match the template exactly, including spelling, capitalization, and spacing. Even small changes (such as adding a space or changing a letter’s case) will cause the import to fail. Always use the official template provided by Sage Intacct.
ID fields must contain exact references to customers or suppliers.
ID fields must match exactly, including capitalization. For example, ven100 and VEN100 do not match.
Enter the ID exactly as it appears in Intacct or you run the risk of matching transactions to the wrong supplier or customer, or having the upload fail.
If you reference a supplier, customer, term, budget, etc., in a CSV file, whatever you’re referencing must already exist in Intacct.
For example, if you’re uploading transactions for a particular supplier, if the supplier does not already exist as a supplier in Intacct before your upload, your upload will fail.
This does not apply when creating new customers or suppliers using the import process.
For more information about to correctly number line items, see CSV import: Line number examples.
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.
If your import template contains a country field, for example for an address, make sure that this field has an entry.
If your company has enabled ISO country codes, you must specify the country code in the country field for each record. If you are unsure whether ISO codes are enabled, check with your administrator or test a sample import to confirm the required format.
