Using the correct file formats when importing Cost data
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:
- 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. 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.
- Do not use Excel’s Data from Text functionality as this corrupts the data and leaving you with a non-compliant CSV file.