Using the Correct File Formats for Importing Cost Data
Download CSV files using templates.
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.
- Do not use Excel’s Data from Text feature. This will corrupt the data and produce a non-compliant CSV.