Change value from csv

Welcome Forums General PowerShell Q&A Change value from csv

  • This topic has 4 replies, 2 voices, and was last updated 2 months ago by
    Participant
    .
Viewing 4 reply threads
  • Author
    Posts
    • #214209
      Participant
      Topics: 1
      Replies: 2
      Points: 37
      Rank: Member

      Hi all

      I’m trying di modify data into a csv, I’ve to remove value in parentheses(). After that I would to calculate a percentage (“Space used”/”Hard Limit” * 100 ) e put this into header “state”

      I’m a newbe and I’m not able to find the right method. How can I do this?

      source csv is

      “Path”,”Space used”,”Soft limit”,”Hard limit”,”Grace period left”,”State”
      “/Services/sales”,”414065664 (394.8M)”,”10737418240 (10.0G)”,”53687091200 (50.0G)”,””,”OK”
      “/Services/HR”,”401392640 (382.7M)”,”53687091200 (50.0G)”,”107374182400 (100.0G)”,””,”OK”

      Result csv should be

      “/Services/sales”,”414065664″,”10737418240″,”53687091200″,””,”OK”
      “/Services/HR”,”401392640″,”53687091200″,”107374182400″,””,”OK”

      Thanks

      Mancio

    • #214212
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      welcome to Powershell.org. Please read the instructions you find in the very first post on top of the list of this forum: Read Me Before Posting! You’ll be Glad You Did!

      When you post code or sample data or error messages or console output you should format this as code using the code tags “PRE“. Thanks in advance.

      What have you tried so far? Please show your code. Have you tried to search for a solution?

    • #214215
      Participant
      Topics: 1
      Replies: 2
      Points: 37
      Rank: Member

      Hi Olaf

      I’ve tried with this code, it doesn’t manipulate import file as csv but as text file

      $quotacsv = "./test.csv"
      $quotamod = "./test-exp.csv"
      $quotaclean = "./test-clean.csv"
      (Get-Content $quotacsv)|Foreach-Object {
      $_ -replace '\(([^\)]+)\)',''} | Set-Content $quotamod
      $quotaclean
      thanks
      Matteo
    • #214224
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      Rank: Community MVP

      If I got it right something like this should be a start for you:

      $InputData = @'
      "Path","Space used","Soft limit","Hard limit","Grace period left","State"
      "/Services/sales","414065664 (394.8M)","10737418240 (10.0G)","53687091200 (50.0G)","","OK"
      "/Services/HR","401392640 (382.7M)","53687091200 (50.0G)","107374182400 (100.0G)","","OK"
      '@ | ConvertFrom-Csv -Delimiter ','
      
      
      $InputData |
          ForEach-Object {
              [PSCustomObject]@{
                  Path = $_.Path
                  SpaceUsed = $_.'Space used' -replace '\s+\(.+\)'
                  SoftLimit = $_.'Soft limit' -replace '\s+\(.+\)'
                  HardLimit = $_.'Hard limit' -replace '\s+\(.+\)'
                  GracePeriodLeft = $_.'Grace period left'
                  State = "{0:n2} %" -f ([Int64]$($_.'Space used' -replace '\s+\(.+\)')/[INT64]$($_.'Hard limit' -replace '\s+\(.+\)') * 100)
              }
          }
    • #214236
      Participant
      Topics: 1
      Replies: 2
      Points: 37
      Rank: Member

      Hi Olaf

      I’ve done some little mod

      $quotacsv = "/Users/mancio/tmp/prova.csv"
      $quotacsvexp = "/Users/mancio/tmp/prova-clean.csv"
      $inputdata = Import-Csv $quotacsv -Delimiter ","
      $InputData |
      ForEach-Object {
      [PSCustomObject]@{
      Path = $_.Path
      SpaceUsed = $_.'Space used' -replace '\s+\(.+\)'
      SoftLimit = $_.'Soft limit' -replace '\s+\(.+\)'
      HardLimit = $_.'Hard limit' -replace '\s+\(.+\)'
      GracePeriodLeft = $_.'Grace period left'
      State = "{0:n2} %"-f ([Int64]$($_.'Space used' -replace '\s+\(.+\)')/[INT64]$($_.'Hard limit' -replace '\s+\(.+\)') * 100)
      }
      } | Export-Csv -Path $quotacsvexp
      It works great!!
      thanks
      Mancio
Viewing 4 reply threads
  • You must be logged in to reply to this topic.