Clever Geek Handbook

PowerShell - Replace character in specific column in csv file

I need to replace "-"

with ""

(nothing) in many csv files, except for a column Name

that may contain characters -

that I need to keep.

Example:

"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 column .. even in the column Name

: - (

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


source to share


3 answers


Try the following:



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


source


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;


Instead of comparison, you can use $Record.$Header.Trim() -eq '-'

for comparison if some fields have leading or trailing spaces.

+3


source


Bacon Bits have very nice code, but I think it can be changed a little. What happens if the Name column is "-"?

$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;
0


source






More articles:


All Articles