This article covers some of the frequently asked questions related to reports.
Opening CSV files in Excel that have leading zeros in columns causes Excel to read those columns as numbers and silently drop the zeros, e.g. 0875 becomes 875.
Note that this is not a formatting issue, and converting the column to text won’t cause the leading zero to reappear – the data is permanently changed (by Excel) and becomes an issue if the file is then re-saved.
There are ways of ensuring the leading zeros are retained. The instructions below cover the processes for exporting the data so the leading zeros are retained, depending on whether the file is for import (into Arlo or another application) or for manipulation in Excel (for advanced reporting).
- Export the data as required.
- Save the file to your computer, ensuring that the file is saved as a CSV.
Never open the file in Excel directly, as opening in Excel is the process that removes the leading zeros.
- If you wish to ensure that your file still contains leading zeros, in your file directory, right-click and open the file with notepad.
- Search for (Ctrl + F) and check an example number that should contain a leading xero.
- Export the data as required.
- Save the file to your computer's desktop.
Do not open the file at this point.
- Open a blank Excel Spreadsheet.
- In Excel, open the Data tab of the ribbon. In the Get External Data section, click the From Text icon.
- Select the file you saved to your desktop and press Import.
- In the Delimiter field, select Comma.
- Press Load.
- If the number fields still do not have the zero in front, at this stage converting the cells to text will now work.
- Select all of the cells in the column.
- Right-click in the highlighted area and select Format Cells...
- Choose Text.
- Press OK.