When you load a CSV file into Excel, it tries to determine the format of the data being loaded. Excel automatically recognises the field as a number when importing it. By default, then, the number is displayed using one of the number fields, excluding any leading zeros. This is a limitation of the CSV format and not something that can be changed in DEAR. However, it is possible to import/export leading zeroes following these steps.
NOTE: If you export a file with leading zeroes and save it as a simple CSV, the characters will be overwritten and CANNOT be reverted to the correct format. If the overwritten file is then imported into DEAR, it will overwrite the characters in the system, and they CANNOT be recovered. This is a limitation of the CSV format that is beyond DEAR's control.
We recommend keeping a backup in Excel format (.xls or .xlsx) with the correct file in case files from DEAR get overwritten.
If data has been corrupted, there is nothing we can do; the only thing to do is to input the data again in DEAR, or input the characters in Excel, save to CSV, and import.
Table of Contents
Export and view CSV files with leading zeroes
- When exporting a CSV file, make sure the CSV file is renamed so it has a .txt extension, not .csv
- You MUST perform this step, or the rest of the steps will NOT work because Excel won't start the Text Import Wizard in Step 5.
- Do not double click the file to be opened in Excel. If a file opened in this way is saved, it will remove the leading zeroes automatically. You can check that correct details have been exported by opening the file in Notepad.
- Open Excel from your menu or Desktop. DO NOT double-click the exported file to open Excel.
- Navigate to Data → Get External Data → From Text. This may look a bit different depending on which version of Excel you are running.
- Navigate to the location of the CSV file you want to import.
- Choose the Delimited option.
- Check My data has headers so that Excel recognises that the first row of the CSV file has column names.
- Click Next to display the second step of Text Import Wizard.
- Set the delimiter to Comma.
- Click Next to move on to the next step.
- Select all columns with leading zeroes and set Column data format to Text.
- Click OK and then Finish.
- Keep the default values inside the Import Data dialogue and click OK.
- Now you can make modifications to the file and save the file as an Excel document or a CSV file. You will need to use Save As rather than Save.
Importing CSV files with leading zeroes
Follow these steps to export the CSV file with the preserved leading zeroes back into DEAR.
NOTE: You must only use the CSV file that you have JUST saved from the Excel file. If you open the CSV file, make changes, then save it again, this will remove all the leading zeroes. If you want to make changes, you must make them in the Excel file, then save as CSV again.
If a file with the leading zeroes stripped out is uploaded to DEAR, it will corrupt the data in DEAR, and there is nothing we can do to restore it. The only thing to do is to input the characters again in DEAR, or input the characters in Excel, save to CSV, and import.