Adding Leading Zeros in .csv file

Question

I am trying to create a PowerShell script that adds two leading zeros to all the numbers in a particular column.This script is able to add the two zeros to the column values, but only at the end of the values.

When I change the order of the variables on line 16 to '$zeros + $employeeNumber', the 7DigitEmployeeNumber column does not include the leading zeros.

I have tried converting the data type of $zeros and $employeeNumber but have been unable to get the script to add leading zeros to the column values. Any guidance is greatly appreciated!

Please (also) post your code, data, error messages as ([properly formatted](https://stackoverflow.com/help/formatting)) _text_, [not (just) as _images_](https://meta.stackoverflow.com/a/285557/45375). — mklement0, Apr 28 '22 at 22:28
PLEASE, **_never post images of code/errors/data._** why? lookee ... Why should I not upload images of code/data/errors when asking a question? - Meta Stack Overflow — https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question — Lee_Dailey, Apr 28 '22 at 22:47
for a solution to your apparent problem ... use the `-f` string format operator to give you zero-padded numeric _strings_ instead of numbers that naturally don't have leading zeros. [*grin*] — Lee_Dailey, Apr 28 '22 at 22:50

score 1 · Answer 1 · answered Apr 28 '22 at 22:45

1

That's because numbers don't have leading zeros. If you want to pad zeros to the left of the number you have to treat it like a string.

$employeeNumber = 73128
$employeeNumberString = $employeeNumber.ToString()
$paddedEmployeeNumberString = $employeeNumberString.PadLeft(7,'0')

That will leave $paddedEmployeeNumberString with a value of 0073128.

answered Apr 28 '22 at 22:45

TheMadTechnician

34,906
3
42
56

You can also use the format operator `$paddedEmployeeNumberString = "{0:0000000}"-f 73128` – Remko Apr 29 '22 at 08:52

score 1 · Answer 2 · answered Apr 29 '22 at 11:28

Looking at the images you posted, this is not just about padding numbers with leading zeroes, but mainly that these leading zeroes 'dissapear' when the csv file is opened in Excel.

If you want to have Excel respect those zeroes, write them out in your CSV file prefixed with a TAB character:

"`t{0:D7}" -f 73128

produces 0073128

If you need more total digits, just change {0:D7} into {0:D9} or {0:D25} or..

Adding Leading Zeros in .csv file

2 Answers2

Linked