'Files edited in Excel may not work correctly' warning

Sometimes, CSV files that are opened up in Excel and re-saved can have corrupted dates within the file. This page will discuss the sort of corruption that can occur, and how to prevent it.

What can happen?

Excel will often pre-emptively change date formats within CSV files that are imported. This is particularly the case when date formats don't match the default locale and date settings within Excel. We've also noted that this issue was more prevalent with older versions of Excel - 2007 or earlier.

For example, a possible European / Australian / New Zealand date format is dd/mm/yyyy - but Excel's default date format is set to the US mm/dd/yyyy. When the file is opened in Excel, it converts all possible dates in the US format, but leaves those that would be invalid dates in US format as the default.

A specific example - imagine transaction rows in a CSV, one with a date of 11th of April 2013 (11/04/2013) and the other with 13th of April 2013 (13/04/2013). When opened with US-default Excel, it would:

  1. Interpret the 11th of April 2013 date (11/04/2013) as the 4th of November 2013 (mm/dd/yyyy format)
  2. Leave the 13th of April 2013 date intact (as in US-format it would mean the 4th of the 13th month - an invalid date)
  3. All other transactions that occur before the 12th of the month would be incorrectly processed by Excel as mm/dd/yyyy
Then upon re-saving the CSV file for upload to PocketSmith, all transactions that occurred on or before the 12th of the month would then be incorrect - the day would become the month, and date sequencing would break.

How can I prevent this?

The best prevention is to not open your file in Excel. Though the above error has become less prevalent in more recent versions of Excel, if you want to be absolutely safe in editing your file then only use a plain text editor to do so (e.g. Notepad on Windows, TextEdit on OS X, gedit on most Linux distributions).

Also check your locale and default date settings. Though this doesn't guarantee compatibility, if you aren't US based then checking your date settings reduces the possibility of an error.

Your can also try and use a later version of Excel. We've not seen as many occurrences of the above error occur in the past couple of years, so it may be that date detection works on a whole-column basis when importing CSVs in newer versions of Excel.

Feedback and Knowledge Base