CODEWITHSUNDEEP
powershellcsv
Gerald Begin
Gerald Begin

Reputation: 45

PowerShell - Replace a character in a specific column in a csv file

I need to replace "-" for "" (nothing) in a many csv files, except for the Name column, which may contain - characters I need to keep.

Ex:

"Name","timestamp","CPU|Demand (%)","CPU|Demand (%) (Trend)","CPU|Demand (%) (30 days forecast)"
"ZY02-LAB-WinMachine","Mar 2, 2017 12:01:19 AM","-","38.07","-"
"ZY02-LAB-WinMachine","Mar 21, 2017 10:45:00 AM","40.55","-","-"
"ZY02-LAB-WinMachine","Apr 6, 2017 11:56:19 AM","-","-","38.69"
"ZY02-LAB-WinMachine","Apr 6, 2017 12:11:19 PM","-","-","38.7"

will become

"Name","timestamp","CPU|Demand (%)","CPU|Demand (%) (Trend)","CPU|Demand (%) (30 days forecast)"
"ZY02-LAB-WinMachine","Mar 2, 2017 12:01:19 AM","","38.07",""
"ZY02-LAB-WinMachine","Mar 21, 2017 10:45:00 AM","40.55","",""
"ZY02-LAB-WinMachine","Apr 6, 2017 11:56:19 AM","","","38.69"
"ZY02-LAB-WinMachine","Apr 6, 2017 12:11:19 PM","","","38.7"

The line I have in my script replaces ALL - in the csv .. even the Name column :-(

(Get-Content $ImportCPUFile) | % {$_ -replace "-"} | out-file -FilePath CSV-cleaned.csv -Fo -En ascii

Upvotes: 3

Views: 7314

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415765

Try this:

(Get-Content $ImportCPUFile) |
    ForEach-Object { $_ -replace '"-"', '""' } |
    Out-File -FilePath CSV-cleaned.csv -Force -Encoding ascii

Upvotes: 2

Mihail Kuznesov
Mihail Kuznesov

Reputation: 575

Bacon Bits have very good code, but I think it can be little bit modified. What will happen if Name column will have value "-"? 

$Csv = Import-Csv -Path $ImportCPUFile

$Headers = $Csv | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name;

ForEach ($Record in $Csv) {
    foreach ($Header in $Headers) {
        if ($Header -ne "Name") {
             $Record.$Header = $Record.$Header.Replace("-","")
        }
    }
}
$Headers = $Csv | Get-Member -MemberType NoteProperty


$Csv | Export-Csv -Path $OutputFile -NoTypeInformation;

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32170

Try something like this:

$Csv = Import-Csv -Path $ImportCPUFile;
$Headers = $Csv | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name;

ForEach ($Record in $Csv) {
    foreach ($Header in $Headers) {
        if ($Record.$Header -eq '-') {
            $Record.$Header = [String]::Empty;
        }
    }
}

$Csv | Export-Csv -Path $OutputFile -NoTypeInformation;

You may want to use $Record.$Header.Trim() -eq '-' for the comparison instead if you have leading or trailing spaces in some fields.

Upvotes: 3

Related Questions