views:

36

answers:

2

Hi All

I want to read in a csv file, filter it based on the values of two of the fields and set the value of another field. Here's a simple example of what I'm trying to achieve:

c:\somefile.csv contents:

firstField,secondField,thirdField
1,2,"somevalue"
2,2,"avalue"
3,1,"somevalue"

#Import file into array
$csv = Import-Csv c:\somefile.csv

# Where secondField = 2 and thirdField = "someValue" set thirdField =
"anotherValue"
$csv | where {$_secondField -eq 2 -and $_.thirdField = "somevalue"} |
<set value of thirdField = "anotherValue">

How can I do this. As you can see, from the example about, I can read in and filter the array. But I don't know how to then set the value of the thirdField. I tried set-itemproperty but got the error: "The WriteObject and WriteError methods cannot be called after the pipeline has been closed".

Edit: I would also only like to change to value of the first 2 items (rows) returned. Answered by me: I used Select -first 2.

Any advice on how to achive this would be appreciated.

Alan T

+3  A: 

I altered your code a little bit:

$csv | 
    Where-Object {$_.secondField -eq 2 -and $_.thirdField -eq 'somevalue'} |
    Foreach-Object { $_.thirdField = 'anotherValue' }
  • $_secondField => $_.secondField
  • $_.thirdField = "somevalue" should be $_.thirdField -eq "somevalue"
  • Foreach-Object sets the third value. It processes only 1 record in this case, but basically it processes all the records that are piped in (just try to remove the Where part).
  • it is 'safer' to use single quote instead of double quote because of unintended variable expansion.
stej
Note to self: Make sure that the header line in the CSV has no trailing space ... that threw me off with my testing for the past few minutes here ...
Joey
Thanks stej worked a treat.
Alan T
+1  A: 

You've got the right idea. Use Where-Object (aliased to Where) to filter the objects in the pipeline and then further down the pipeline use Foreach-Object (aliased to Foreach) to set the value like so:

$csv | where {$_secondField -eq 2 -and $_.thirdField -eq "somevalue"} | 
    foreach {$_.thirdField = "notherValue"}

Think of Where-Object as just a filter mechanism and Foreach-Object as the pipeline mechanism that allows you to apply arbitrary script to each pipeline object.

Keith Hill
Thanks for the help Keith.
Alan T