CODEWITHSUNDEEP
csvpowershell
test
test

Reputation: 85

Replace empty fields in CSV with 0

I am trying to replace null values in a certain column to 0 using PowerShell.

I have the CSV in this format:

test   test2    test3    test4
----   -----    -----    -----
blah   fsds       4      45645
bla1   fsds1             45645
blah2  fsds2      4      34322
blah3  fsds3      4      67544
blah4  fsds4             3432432

so I want to go through the null values in "test3" and replace to 0.

I have this, but it doesn't work:

$inFilePath = "G:\powershell\excel\test.csv"
$csvColumnNames = (Get-Content $inFilePath | Select-Object -First 1).Split(",")

foreach ($row in $inFilePath) {
    if ($row.test3 -eq $null) {
        $row.test3 = 0
        Write-Host "updating value in excel"
    }
}

$csvColumnNames | Export-Csv  "G:\powershell\excel\replaced2.csv" -NoTypeInformation

Upvotes: 1

Views: 3483

Answers (2)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

Use Import-Csv for reading and Export-Csv for writing CSV files.

$inFilePath  = "G:\powershell\excel\test.csv"
$outFilePath = "G:\powershell\excel\replaced2.csv"

Import-Csv $inFilePath | % {
  if (-not $_.test3) { $_.test3 = 0 }
  $_   # echo all records, so they can be exported back to a file
} | Export-Csv $outFilePath -NoType

Upvotes: 1

Kiran Reddy
Kiran Reddy

Reputation: 2904

you are on the right track with foreach and if.Try this:

foreach($row in $inFilePath)
{
    if (-not $row.test3)
    {
      $row.test3= 0
    }

}

to get the column headers:

$inFilePath | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name

Upvotes: 1

Related Questions