How to open CSV files safely with Microsoft Excel
SupportAbility exports a number of CSV (Comma Separated Value) data files that can be used by other applications for invoicing, NDIS payment requests and other purposes.
It is quite common for SupportAbility users to open these exported CSV files using programs like Microsoft Excel to check their contents before importing the file into a third party application such as MYOB. However, when Microsoft Excel opens a CSV file, it attempts to interpret the data in that file and starts making changes to it.
For example, if Excel sees something in the CSV file that it identifies as a date (often incorrectly), Excel will change the date format in the CSV file to suit the way that it prefers to dates to be formatted. Unfortunately, this generally has the unintended consequence of breaking the CSV file for the third party application that the CSV was specifically designed for.
If you need to open a CSV file in Excel without breaking it, here's how to do it:
- Open a new Excel sheet, select the Data tab, then click 'From Text' in the Get External Data group.
- Browse to the CSV file and select 'Import'.
- In step 1 of the Import Wizard choose 'Delimited' as the original data type. Click 'Next'.
- In step 2 of the Import Wizard choose Comma as the delimiter (deselect the Tab check box) and click 'Next'.
- In step 3 of the Import Wizard, you tell Excel not to change your formats. With the first column in the Data Preview selected, scroll across to the last column and select it while holding the SHIFT key (all columns should now be selected). Then select 'Text' as the Column Data Format and click 'Finish'.
- Click OK to insert the data into cell A1
You should now have a spreadsheet with the imported data but without Excel breaking the formatting required for the intended third party application. Remember to export the file from Excel to CSV again before uploading to the third party application if you have made changes.