Powershell CSV removing rows and then remove from whole file if A column matches

user_15779222 0
Viewed 2003

I've created the following small script to remove 2++ strings from a CSV. Each row is a log of a given person and a answer they give. The CSV has X columns. The column named FIRST identifies the person.

What I need to do is when I delete a row matching the answer, I also need to delete the person from the whole CSV if it had one of the two strings.

What I've made so far, removes the row of people having the answers but the person is still left in the overall CSV with other answers. I want to remove the person fully if the questions have been answered.

Can somebody help me out with making the addition or changes to make this happen?

INPUT File

FIRST,LAST,ADDR,ADDR2,GENDER,HOME,WORK
1,N/A,N/A,N/A,N/A,BAF,N/A
10005,JAS,AA,N/A,,ZAV,N/A
10007,JADE,BB,N/A,OMA,N/A,N/A
10007,JADE,N/A,RAV,N/A,N/A,N/A
10011,KIAH,N/A,N/A,BALI,BB,N/A

SCRIPT

$CSVfile = "C:\Temp\Test\Test.csv"
$CSVfile_filtered = "C:\Temp\Test\Test.csv"

$regex001 = "AA"
$regex002 = "BB"


$filterArray = @($regex001,$regex002)
Get-Content $CSVfile | Select-String -pattern $filterArray -notmatch | Set-Content $CSVfile_filtered

The file should then remove 10005, 10011 and both lines of 10007. But my version only removes one of the 10007 since it only matches one of the two patterns.

powershell scripting
3 Answers

Using more of PowerShell's built-in cmdlets can make this a little easier to manage.

# Assuming searching only properties ADDR and ADDR2
$filter = 'AA','BB'
# Grouping by First and Last values to easily remove duplicates
# -match uses regex so | is needed for an OR of multiple items
Import-Csv Test.csv | Group-Object First,Last |
    Where {!($_.Group.ADDR,$_.Group.ADDR2 -match ($filter -join '|'))} |
        Foreach-Object Group |
            Export-Csv output.csv -NoType

You would think strictly using text manipulation would be simpler, but it adds other scenarios to consider:

  1. You will need to track users that have duplicate entries and potentially back track to remove them (if not grouping). This could require reading the file contents twice.
  2. Your header row could match the string you want to filter so you will need to add it to the output if filtering removes it.

Keeping the scenarios above in mind, you can still use a grouping concept:

$filter = 'AA','BB'
$file = Get-Content Test.csv
# $file[0] is the header row
# -split string uses regex and splits at the second comma
# -split results' [0] element is First,Last values
$file[0],($file |
    Select-Object -Skip 1 |
        Group-Object {($_ -split '(?<=^[^,]*,[^,]*),')[0]} |
            where {!($_.Group -match ($filter -join '|'))} |
                Foreach-Object Group) | Set-Content output.csv
user_110254760

If I got it right you could do something like this:

$SearchPattern = 'AA', 'BB'

$INPUTCSV = @'
FIRST,LAST,ADDR,ADDR2,GENDER,HOME,WORK
1,N/A,N/A,N/A,N/A,BAF,N/A
10005,JAS,AA,N/A,,ZAV,N/A
10007,JADE,BB,N/A,OMA,N/A,N/A
10007,JADE,N/A,RAV,N/A,N/A,N/A
10011,KIAH,N/A,N/A,BALI,BB,N/A
'@ | ConvertFrom-Csv 


$ActualSearchPattern =
$INPUTCSV | 
Where-Object {
    $_.LAST -in $SearchPattern -or
    $_.ADDR -in $SearchPattern -or
    $_.ADDR2 -in $SearchPattern -or
    $_.GENDER -in $SearchPattern -or
    $_.HOME -in $SearchPattern -or
    $_.Work -in $SearchPattern
} | 
Select-Object -ExpandProperty FIRST

$INPUTCSV | 
Where-Object -Property FIRST -NotIn -Value $ActualSearchPattern |
Format-Table -AutoSize

There might be more sophisticated or more elegant ways but I cannot think about one at the moment. ;-)

user_91965600

There is a nice PowerShell module you can use to manipulate the content of a csv or xlsx file: ImportExcel

This give you a lot of options to manipulate the sheets, columns etc.

user_121453800
Related