You are here

Using the Correct File Formats for Importing Cost Data

Download CSV files using templates.

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

All the import files must be formatted correctly for the import to work. See the downloadable Importing Data - Minimum Requirements Table PDF below.

Mandatory requirements

Files must be formatted as UTF8 CSV files

  • Comma separated value files can be created using Excel.
  • Excel 2016 and later has a specific format which will ensure 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 use a Custom format: YYYY-MM-DD.

Numbers must be in the correct format

  • Numbers must use a full stop (.) as the decimal symbol.
  • Commas can be used for digit grouping, but are not required.
  • Currency symbols such as $ must not be included.

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.
  • Save the file in Excel as CSV UTF-8, then close and re-open the file. Scroll through the data and look for non-standard characters.
  • A common cause of import failures is the Microsoft Word special hyphen: 
    • This is known to cause import errors and should be replaced by a standard hyphen in Excel using the Find and Replace feature.

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 feature. This will corrupt the data and produce a non-compliant CSV.

Was this article helpful?

Thanks. A ticket has been opened with the Support Central team.