Prevent data loss when opening CSV files
Roles: Technicians
Last updated: September 2021
Reports from the NextCentury website are downloaded in the CSV format (Comma Separated Value). These files can be opened using common spreadsheet programs such as Microsoft Excel.
Excel will attempt to identify the data type as the report is being opened. Device serial numbers found in the report may be interpreted as scientific notation. When this happens, the serial number is modified and the original value is lost.
This article provides the necessary steps to prevent Excel from modifying serial numbers.
When Excel sees a meter serial number in the CSV file that it identifies as scientific notation, it will modify the data in the file. For example, a serial number of "70143E7" is changed to "7.01E+11".
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.
In a new Excel sheet, open the File menu and select 'Import'.
Although the exported file is a CSV, choose the 'Text file' option and then select 'Import'.
Select the relevant file from your Downloads or the folder/location the file is stored and select 'Get Data'.
Ensure 'Delimited' is selected, leave all other settings as default and select 'Next'.
Choose only the 'Comma' option and then select 'Next'.
Select the 'Text' option and then select 'Finish'. (Ensure that all columns are selected during this step.)
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'.
You will now have a spreadsheet with the imported data to review without Excel having changed any formatting.
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.
