Dropping Rows from an Import-Csv

PowerShell Help
1

I’m having trouble trying to remove rows from a csv file I’ve imported.

I’m using Import-csv to read a csv file. The file contains 6 columns which have User ID’s. I need to check Active Directory to see if the ID’s are valid, if they’re not, then I need to remove the entire row from further processing. There are many duplicate ID’s within the csv, so rather than querying each individually, I’m first removing the duplicates and then querying against AD.

So I end up with a variable called $Data which is my original csv file. I have $UsersNF which is an array containing the ID’s not found within AD.

If an ID within $UsersNF is found within Columns J,N,P,R,T,V of $data, then I need to drop that row from $data.

Using this Where statement works against a single column –

$newdata = $data | where-object {$UsersNF -notcontains $_.J}

I can’t figure out how to code this to check against the multiple columns which I need. What I have now is this, and though it identifies the ID to be dropped, its not properly removing the row in question –

$Columns = @("J","N","P","R","T","V")

foreach ($entry in $data)
{
    ForEach ($Column in $Columns) 
    {
        If ($UsersNF -contains $Entry.$Column)
        {
            write-Host "User to be removed: " $entry.$column
            $newData = $data | Where-Object {$UsersNF -notContains $Entry.$Column}
                   
        } 
    }
}

Thanks

2

Check out the Big Book of Powershell Gotchas: [url]https://www.penflip.com/powershellorg/the-big-book-of-powershell-gotchas[/url]

The -Contains and -NotContains check to see if a value is in an array.
The -Like and -NotLike check if a string is present and supports wildcards.

PS C:\Windows\system32> $Columns = @("J","N","P","R","T","V")

"Column contains J: {0}" -f ($Columns -contains "J")
"Column contains A: {0}" -f ($Columns -contains "A")

"Column like dodah: {0}" -f ("whipitydodah" -like "*dodah")
"Column like didah: {0}" -f ("whipitydodah" -like "*didah")


Column contains J: True
Column contains A: False
Column like dodah: True
Column like didah: False

I don’t know what $UsersNF is, but if you are checking if a columns is Empty or Null, try logic like this:

$Columns = @("J","N","P","R","T","V")

if ($column) {"Not null"}else{"Null"}
if ($columns){"Not null"}else{"Null"}

if ([string]::IsNullOrEmpty($column)){"Null"}else{"Not Null"}
if ([string]::IsNullOrEmpty($columns)){"Null"}else{"Not Null"}
3

Thanks Rob, I believe it’s the –contains/-notcontains operator I require here.

$UsersNF is an array of UserID’s. If an ID is part of $UsersNF, and if that ID is found in any of the $Entry.$Column, then I need to remove the entire row from $data.

$UsersNF = @("Fred","Wilma")

The Write-Host I have is showing me that the 

 If ($UsersNF -contains $Entry.$Column)
is identifying the correct ID’s to drop. The problem I’m having is actually getting the row removed from the $data.

I thought using a pipe into Where-Object would do it for me, but I don’t think I have that coded properly.

Thanks

4

Are you trying to search if any column contains the name, then remove the line? Explain what your logic is.

5

Yes, If any of the columns indicated within the $Columns array has the name, then I need to remove the line.

I have this –

$data = import-csv $FileName

$Columns = @("J","N","P","R","T","V")
$UsersNF = @("Fred","Wilma"
)
foreach ($entry in $data)
{
    ForEach ($Column in $Columns) 
    {
        If ($UsersNF -contains $Entry.$Column)
        {
            write-Host "User to be removed: " $entry.$column
            $newData = $data | Where-Object {$UsersNF -notContains $Entry.$Column}
                               
        } 
    }
}

The write-host is identifying the user, but I can’t seem to get it removed.

Thanks

6

Gerald,

I think this is what you what you are trying to do:

$data = @()
$data += New-Object -TypeName PSObject -Property @{Column1 = "Blah"; Column2 = "foo"; Column3 = "Sam"}
$data += New-Object -TypeName PSObject -Property @{Column1 = "Boo"; Column2 = "Hoo"; Column3 = "Joe"}
$data += New-Object -TypeName PSObject -Property @{Column1 = "Susie"; Column2 = ""; Column3 = "Frank"}
$data += New-Object -TypeName PSObject -Property @{Column1 = "Howdy"; Column2 = "Doodie"; Column3 = "Susie"}

$usersNF = "Sam", "Susie"
$includedColumns = "Column2", "Column3"

$newData = @()
$newData = foreach ($row in $data) {
    $foundMatch = $false
    #Set :column label for each so we can exit if a match is found
    #and loop through all of the properties of the current row
    :column foreach ($column in $row.PSObject.Properties) {
        #Only process columns contained in the $included columns array
        if ($includedColumns -contains $column.Name) {
            #If the column contains a name from $usersNF, set a flag to indicate
            #a match was found and then exit the column loop using the label we set
            if ($usersNF -contains $column.Value) {
                $foundMatch = $true
                break column
            }
        }
    }
    #If there was no match in the row, return the row to $newData
    if ($foundMatch -eq $false){$row}
}

$newData | Select Column1, Column2, Column3 | Format-Table -AutoSize

Column1 Column2 Column3
------- ------- -------
Boo     Hoo     Joe    
Susie           Frank

Even though Susie is in usersNF, she is listed in column 1 because we are only processing column2 and column3. Only rows that do NOT have a match are placed in the newData object.

7

Thanks Rob - I think this is giving me what I was after.

Appreciate the assistance here.