Dropping Rows from an Import-Csv

This topic contains 6 replies, has 2 voices, and was last updated by Profile photo of Gerald Gerald 1 year, 11 months ago.

  • Author
    Posts
  • #24437
    Profile photo of Gerald
    Gerald
    Participant

    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

  • #24438
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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"}
    
  • #24439
    Profile photo of Gerald
    Gerald
    Participant

    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

  • #24441
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

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

  • #24442
    Profile photo of Gerald
    Gerald
    Participant

    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

  • #24449
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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.

  • #24450
    Profile photo of Gerald
    Gerald
    Participant

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

    Appreciate the assistance here.

You must be logged in to reply to this topic.