Format particulat column values of CSV file

PowerShell Help
1

I am having a CSV file with Column name as a,b,c,…ae,af.
I have to format column b. It contains values like 20135 and i have to make it 13 digits by appending leading zero’s.
e.g cell 1 of column b has value of 203500 after formatting it should be 0000000203500.
I learned to format particular value like

$value = 203500
$formattedValue = "{0:D13}" -f $value
output is : 0000000203500

I learned it from some previous questions on this forum.But this is not working for me or i’am not doing it the correct way. What I am doing is-

$Imported = Import-Csv -Delimiter "," -Path C:\Users\risha\Desktop\PowerShell\testFile.csv
$columnName = $($Imported.b)
$Output = foreach ($i in $Imported) {
    foreach ($values in $columnName) {
        $formattedText = "{0:D13}" -f $values
        $values = $formattedText
    }
    $i
}
$Output
2

If you change

$formattedText = "{0:D13}" -f $values

to

$formattedText = "{0:D13}" -f [int] $values

it should work as expected because values you’ve imported are of the data type string not integer.

You can check out one of his recent post on a similar topic.

3

Thank you sir.

$formattedText = "{0:D13}" -f [int] $values

Gave appropriate value. but these values are not reflected in output.
a : 100001338
b : 200000 –> here it should show 0000000200000
c : 29
d : 000002
e : 600028008
if i do

$columnName = $($Imported.b)
    foreach ($a in $columnName) {
        $formattedText = "{0:D13}" -f [int] $value
        $value = $formattedText
        Write-Host $value

    }
$Output

$value shows the correct value that i want
but its not getting replaced in the column in output screen.

4

Thank you Daniel Krebs.

$formattedText = "{0:D13}" -f [int] $values

Worked Fine. But my still there’s an issue.
these values are not reflected in output.
a : 100001338
b : 200000 –> here it should show 0000000200000
c : 29
d : 000002
e : 600028008
if i do

$columnName = $($Imported.b)
    foreach ($a in $columnName) {
        $formattedText = "{0:D13}" -f [int] $value
        $value = $formattedText
        Write-Host $value

    }
$Output

$value shows the correct value that i want but its not getting replaced in the column, at output screen.

5

In you last post I don’t see you updating the $Output variable.

Based on your original post the following should work.

$Imported = Import-Csv -Delimiter "," -Path C:\Users\risha\Desktop\PowerShell\testFile.csv
$columnName = $($Imported.b)
$Output = foreach ($i in $Imported) {
  foreach ($values in $columnName) {
    "{0} : {1:D13}" -f $i, [int] $values
  }
}
$Output
6

Thank you so much sir. Code below worked for me.

$Imported = Import-Csv -Delimiter "," -Path C:\Users\risha\Desktop\PowerShell\testFile.csv
$columnName = $($Imported.b)
$Output = foreach ($i in $Imported) {
  foreach ($values in $columnName) {
    $formattedText = "{0:D13}" -f [int] $values
    $i.b = "$formattedText"
  }
  $i
}
$Output
7

Sorry Sorry, This didn’t worked!

$Imported = Import-Csv -Delimiter "," -Path C:\Users\risha\Desktop\PowerShell\testFile.csv
$columnName = $($Imported.b)
$Output = foreach ($i in $Imported) {
  foreach ($values in $columnName) {
    $formattedText = "{0:D13}" -f [int] $values
    $i.b = "$formattedText"
  }
  $i
}
$Output

It updates the whole column with the last value at $formattedText

8 
$Imported = @'
a,b,c
1,2,3
4,5,6
'@ | convertfrom-csv

foreach ($i in $Imported) {
  $i.b = "{0:D13}" -f [int] $i.b
}
$Imported
9

Thank you so much Ron. Your solution worked as per my requirement.

$Imported = Import-Csv -Delimiter "," -Path C:\Users\risha\Desktop\PowerShell\testFile.csv
foreach ($i in $Imported) {
  $i.b = "{0:D13}" -f [int] $i.b
}
$Imported | Export-Csv "C:\Users\risha\Desktop\PowerShell\replace.csv" -NoT -Encoding "UTF8"

Regards,
Rishabh