Prevent data loss when opening CSV files

Roles: Technicians
Last updated: March 2021

A number of CSV (Comma Separated Value) files can be exported from the NextCentury Web Portal (e.g. Property Backups, Usage Reports, and other Custom Reports).

While there should be no need to do so, it is quite common for 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. 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 relevant system. 

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 a meter serial number in the CSV file that it identifies as scientific notation, Excel will change the format in the CSV file to suit the way that it prefers for this data to be formatted. (e.g. serial number 70143E7 may be interpreted and changed to 7.01E+11 instead) 

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

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

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

mceclip1.png

 

Although the exported file is a CSV, choose the 'Text file' option and then select 'Import'.

mceclip3.png

 

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

mceclip0.png

 

Ensure 'Delimited' is selected, leave all other settings as default and select 'Next'.

mceclip5.png

 

Choose only the 'Comma' option and then select 'Next'.

mceclip6.png

 

Select the 'Text' option and then select 'Finish'. (Ensure that all columns are selected during this step.)

mceclip7.png

 

The final step asks where to import the data. If you are in a new Excel Sheet already, leave the default setting and select 'OK'.  

mceclip8.png

 

You will now have a spreadsheet with the imported data to review without Excel having changed any formatting.

mceclip9.png

 

Once reviewed, remember to export the file from Excel to CSV again by selecting 'Save As' and choosing CSV as the File Format, before uploading to the third party application if you have made changes.

mceclip10.png