You are not able to see the leading zeros due to the Excel setting. Please verify the output content by opening file in any text editor.
You can add "Custom Format" Leading Zeros in Excel by the steps below:
1. Select the range of cells you want to add leading zeros to and open up the Format Cells dialog box.
- Right click and choose Format Cells.
- Use the Ctrl + 1 keyboard shortcut.
2. Go to the Number tab.
3. Select Custom from the category options.
4. Add a new custom format in the Type input. If you want the total number of digits including any leading zeros to be 6 then add 000000 as the custom format.
5. Press the OK button.
After applying the custom format you will notice the data has not actually been changed. Selecting a cell with formatting will still show the original number in the formula bar. They only appear in the worksheet with the leading zero format. This also means if you re-import the data as values, you will lose the leading zeros.
Alternatively, you could force Excel to enter a number as text by using a leading apostrophe. This means you’ll be able to keep those zeros in front as you’re entering your data. This method is quick and easy while entering data. Just type a ' character before any numbers. This will tell Excel the data is meant to be text and not a number. When you press Enter, the leading zeros will stay visible in the worksheet. The ' will not be visible in the worksheet, but is still there and can be seen in the formula bar when the active cell cursor is on the cell.