Replace empty fields in CSV with 0

Question

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

Kiran Reddy · Accepted Answer · 2015-07-09 07:35:40Z

1

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

edited Jul 9, 2015 at 7:35

answered Jul 9, 2015 at 6:03

Kiran Reddy

2,9242 gold badges18 silver badges21 bronze badges

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

4 Comments

test Over a year ago

hey that works as expected. but would you have a format thats less powershelly? I want it in a format where I can do a few other checks before it enters the foreach-object.

Kiran Reddy Over a year ago

can you elaborate a little more on what you mean by 'less powershelly'

test Over a year ago

as in one that doesnt use " |" so i can do further checked after importing and before the foreach-object loop?. E.g. I need this link in there $csvColumnNames = (Get-Content $inFilePath | Select-Object -First 1).Split(",")

Kiran Reddy Over a year ago

you can put whatever code you wanted inside the foreach-object but to keep things simple you can re-use the original code you posted. I guess the only thing you needed to change was $row.test3 -eq $null

Ansgar Wiechers · Accepted Answer · 2015-07-09 09:19:04Z

1

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

answered Jul 9, 2015 at 9:19

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Collectives™ on Stack Overflow

Replace empty fields in CSV with 0

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related