Format particulat column values of CSV file

This topic contains 8 replies, has 3 voices, and was last updated by  Rishabh Verma 6 months, 3 weeks ago.

  • Author
    Posts
  • #65689

    Rishabh Verma
    Participant

    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
    
  • #65692

    Daniel Krebs
    Moderator

    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.

    • #65697

      Rishabh Verma
      Participant

      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.

  • #65694

    Rishabh Verma
    Participant

    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.

  • #65700

    Daniel Krebs
    Moderator

    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
    
    • #65703

      Rishabh Verma
      Participant

      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
      
    • #65706

      Rishabh Verma
      Participant

      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

  • #65716

    Ron
    Participant
    $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
    
    • #65742

      Rishabh Verma
      Participant

      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

You must be logged in to reply to this topic.