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

Question

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

Bacon Bits · Accepted Answer · 2017-04-03 15:44:08Z

3

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.

answered Apr 3, 2017 at 15:44

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sodawillow · Accepted Answer · 2017-04-03 19:12:36Z

2

Try this:

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

edited Apr 3, 2017 at 19:12

sodawillow

13.3k4 gold badges36 silver badges44 bronze badges

answered Apr 3, 2017 at 15:30

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

1 Comment

Gerald Begin Over a year ago

Génial :-) Working perfectly ... :-)

Mihail Kuznesov · Accepted Answer · 2017-04-03 16:37:20Z

0

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;

answered Apr 3, 2017 at 16:37

Mihail Kuznesov

5752 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related