How to open CSV files safely with Microsoft Excel

A number of CSV (Comma Separated Value) data files are available for export from SupportAbility e.g. NDIS Bulk Payment Requests, Invoice Batches and Time Sheet Batches, for the purpose of importing this data to the relevant system. 

Whilst there should be no need to do so, it is quite common for Staff Members 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. This article steps through how to open CSV files safely using Microsoft Excel if the file must be opened prior to being uploaded to the required system. 

Audience: All

Why do these files need to be opened safely?

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.  

This is often problematic, as the data has changed just by opening the file. Then, when attempting to upload this file to the relevant system, an error may result because of this. 

How to open CSV files in Excel safely to ensure the data is not changed

If you need to open a CSV file in Excel, here's how to do so in a way that ensures the data is not changed:

1. File
   
   In a new Excel sheet, open the File menu and select 'Import'

   
   

2. Import 
   
   Even though the file that has been exported is a CSV, select 'Text file' here as the type of file you want to import, then select: 
   

   
   

3. Select file
   
   Select the relevant file from your Downloads or the folder/location the file is stored and select 'Get Data': 
   
   

   
   

4. Text Import Wizard - Step 1 of 3 
   
   Ensure 'Delimited' is selected, leave all other settings and click 'Next'
   

   
   
   N.B. If no data is appearing in the preview, you may need to change the File origin from the default of 'Unicode (UTF-8)' to either of the following 'Unicode' options: 

5. Text Import Wizard - Step 2 of 3 
   
   Select 'Comma': 

   
   

6. Text Import Wizard - Step 3 of 3
   
   Firstly, select each column by selecting 'Shift' then scrolling across to the right and then selecting the last column. Once all columns are selected (ensuring the Date column specifically is selected, or Account #, or Job columns for Finance exports), choose the 'Column data format' of Text (instead of the default of General):
   

   N.B. You will notice that 'Text' appears at the top of each column, once complete select 'Finish'.

7. Import Data
   
   This step asks where to import the data, if you are in a new Excel Sheet already, leave this set to 'Existing sheet' with the value as per the default:  
   
   Alternatively, 'New sheet' may be selected if this is preferred.

8. Review data
   
   A spreadsheet with the imported data will be available to review as required, without Excel having changed the required formatting for the intended third-party application: 
   

   

9. Save As - export to CSV 
   
   Once reviewed, it is important to export the file from Excel to CSV again by selecting 'Save As', then entering the relevant name of the file and choosing Comma-separated Values (.csv) as the file format if you have made changes, before uploading this saved file to the third-party application: