CODEWITHSUNDEEP
powershellscripting
user15779222
user15779222

Reputation: 23

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

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.

Upvotes: 2

Views: 2865

Answers (3)

Asy
Asy

Reputation: 1

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.

Upvotes: -1

Olaf
Olaf

Reputation: 5232

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. ;-)

Upvotes: 0

AdminOfThings
AdminOfThings

Reputation: 25001

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

Upvotes: 4

Related Questions