Change value from csv

PowerShell Help
1

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

2

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?

3

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
4

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)
        }
    }
5

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