Using Powershell, how can I export and delete csv rows, where a particular value is *not found* in a *different* csv?

Question

I have two files. One is called allper.csv

institutiongroup,studentid,iscomplete
institutionId=22343,123,FALSE
institutionId=22343,456,FALSE
institutionId=22343,789,FALSE

The other one is called actswithpersons.csv

abc,123;456
def,456
ghi,123
jkl,123;456

Note: The actswithpersons.csv does not have headers - they are going to be added in later via an excel power query so don't want them in there now. The actswithpersons csv columns are delimited with commas - there are only two columns, and the second one contains multiple personids - again Excel will deal with this later.

I want to remove all rows from allper.csv where the personid doesn't appear in actswithpersons.csv, and export them to another csv. So in the desired outcome, allper.csv would look like this

institutiongroup,studentid,iscomplete
institutionId=22343,123,FALSE
institutionId=22343,456,FALSE

and the export.csv would look like this

institutiongroup,studentid,iscomplete
institutionId=22343,789,FALSE

I've got as far as the below, which will put into the shell whether the personid is found in the actswithpersons.csv file.

$donestuff = (Get-Content .\ActsWithpersons.csv | ConvertFrom-Csv); $ids=(Import-Csv .\allper.csv);foreach($id in $ids.personid) {echo $id;if($donestuff -like "*$id*" )
{
   echo 'Contains String'
}
else
{
   echo 'Does not contain String'
}}

However, I'm not sure how to go the last step, and export & remove the unwanted rows from allper.csv

I've tried (among many things)

$donestuff = (Get-Content .\ActsWithpersons.csv | ConvertFrom-Csv);
Import-Csv .\allper.csv |
    Where-Object {$donestuff -notlike $_.personid} |
        Export-Csv -Path export.csv -NoTypeInformation

This took a really long time and left me with an empty csv. So, if you can give any guidance, please help.

Abraham Zinala · Answer

Since your actswithpersons.csv doesn't have headers, in order for you to import as csv, you can specify the -Header parameter in either Import-Csv or ConvertFrom-Csv; with the former cmdlet being the better solution.

With that said, you can use any header name for those 2 columns then filter by the given column name (ID in this case) after your import of allper.csv using Where-Object:

$awp = (Import-Csv -Path '.\actswithpersons.csv' -Header 'blah','ID').ID.Split(';')
Import-Csv -Path '.\allper.csv' | Where-Object -Property 'Studentid' -notin $awp

This should give you:

institutiongroup    studentid iscomplete
----------------    --------- ----------
institutionId=22343 789       FALSE

If you're looking to do it with Get-Content you can split by the delimiters of , and ;. This should give you just a single row of values which you can then compare the entirety of variable ($awp) using the same filter as above which will give you the same results:

$awp = (Get-Content -Path '.\actswithpersons.csv') -split ",|;" 
Import-Csv -Path '.\allper.csv' | Where-Object -Property 'Studentid' -notin $awp

Using Powershell, how can I export and delete csv rows, where a particular value is not found in a different csv?

1 Answers