Why is my downloaded spreadsheet formatted incorrectly?

Most files that are downloaded from the system are CSV files. When you open a CSV file in a program such as Excel is it not always a true representation of the data contained within. The reason being is that Excel will apply formatting automatically to the spreadsheet whether you like it or not.

Typical exported files that might experience these problems:

• Payroll batches
  • Payroll files
  • New starters
  • Xero exports
  • Quickbooks exports
  • Expenses
• Shifts
  • With Selected > Export shifts
• Jobs list
  • Export schedule
  • Export schedule with staff
• Client area
  • Submissions data download

Some common examples of Excel auto conversions include:

Removing leading zeros

You will have at some point been presented with this dialogue when opening a certain type of file whereby Excel is asking whether or not you want to convert the file to remove leading zeros, and whether to make these conversions permanent for future files:

Ideally, you would not allow Excel to make any such conversions.

Examples of data with leading zeros would be bank account numbers and phone numbers. 

If we use a payroll data export as an example, when opened in Excel and with this conversation taking place, it would look like this:

But when opened in a program such as Notepad (with no such conversion taking place) you will see how the data actually is represented, with the leading zeros:

Changing sort codes into dates

Here excel has interpreted these sort codes as dates:

Once again when opened in Notepad we can see how the data actually looks: