Using the correct file formats when importing Cost data

When you're importing data into Cost, you must ensure the import files are formatted correctly. If not, the import will fail.

You can use CSV files to import data into the Cost module. Each import has its own template which can be downloaded from within the module.

All import files must be formatted correctly for the import to work.

Mandatory Requirements

Files must be formatted as UTF8 CSV files:

Comma separated value files can be created using Excel.
Excel 2016 and later have a specific format ensures compatibility: CSV UTF-8 (Coma delimited).

Dates must be in the correct format:

Dates must be formatted as YYYY-MM-DD. I.e. 2018-08-01 for 1st August 2018.
Excel does NOT save date formats in CSV files, so opening and closing the file will cause the format to change.
You can set the date format in Excel by selecting the column, going into Format Cells, and using Custom format: YYYY-MM-DD.

Numbers must be in the correct format:

Some special characters are not supported:

Most non-English characters are supported.
If you receive a generic error message such as “We do not recognize the character encoding of this file.”, you may have special characters in the file. To find them, save the file in Excel as CSV UTF-8, then close and re-open it. Scroll through the data and look for non-standard characters.
A common cause of import failures is the MS Word special hyphen: this is known to cause import errors and should be replaced by a standard hyphen in Excel using Find and Replace.

Use other editor programs:

Excel can be useful for populating the data and copying out of other files, but it can introduce errors and problems when working with CSV files.
You can use other editors such as Ron’s Editor, or Notepad++ to work with the files. These are useful when searching for errors.

Other Notes:

Do not use Excel’s Data from Text functionality as this corrupts the data and leaving you with a non-compliant CSV file.