Issue: You are creating a file for upload into the OnDataSuite system with a field that requires a numeric value that may contain leading zeros but your excel file keeps removing the leading zero(s)
Solution: You will need to follow the steps outlined below and save your document as a CSV file that contains the zeros.
Steps to saving an Excel document as a CSV with leading zero values
Step 1. Open your Excel document and click on the column header that contains the numeric value so that all the column is selected.
Step 2. While that column is highlighted, press the Ctrl key and the ‘1’ key at the same time (Ctrl + 1)
Step 3. A window will pop up. Select the ‘Custom’ option from the available Category section
Step 4. Type in six zeros (000000). This will tell the system that if the data inside this field is less than 6 digits, anything less than 5 digits will add zeros to the front of the numeric value.
Step 5. Click the OK button.
Step 6. After you click OK you will still be in your .XLSX formatted document. Visually confirm that you now will see a zero in the field. If you do, click File, Save As and proceed to step 7.
If you do not see your leading zero values, you will need to perform steps 1-5 again.
Step 7. IMPORTANT STEP! Save this document locally using the SAVE AS option and use the selector to select CSV (MS-DOS)(*.csv) then hit the save button
CLOSE THIS DOCUMENT AND DO NOT ATTEMPT TO OPEN IT AGAIN THROUGH MICROSOFT EXCEL OR THIS WILL DAMAGE THE FILE AND REMOVE THE ZEROS !!!
You will be able to review your new file via text as described in the section below.
How to review your CSV file to confirm leading zeros are in file using notepad (.txt reader)
You can open your .CSV document using notepad to make sure that the zeros are there and this will not change the document by performing the following steps.
Step 1. Open Notepad on your computer by clicking the search box on your computer and searching for notepad
Step 2. While in Notepad, select File, Open…
Step 3. Search for your .CSV file not the .XLSX File. If you do not see it, make sure that the ‘All Files’ selector is selected. After you locate your file double click or open it.
Step 4. With your file opened up in notepad and you can visually check to make sure it still has the leading zeros.
STEP 5. If your file has the leading zeros, close the file. This file is now formatted correctly and is ready to upload. Do not use the save or save as feature after reading your document. You were just using the text editor to review the file contents.