CODEWITHSUNDEEP
powershellcsv
Lisbjerg
Lisbjerg

Reputation: 57

Remove lines from CSV file

I have a .CSV file (file1) that looks like

Initials,Size excl. Backup/Pst,Number of warnings
USER1,100,1
USER10,1000,1
USER12,1100,1
USER13,1200,1

I have another .CSV file (file2) that looks like

Initials,Size excl. Backup/Pst
USER1,100
USER4,400
USER10,1000
USER11,1100

I'm trying to remove the lines from file1, where the username is not in file2.
So my output should be: (USER12 and USER13 is removed from the file, since they are not in file2)

Initials,Size excl. Backup/Pst,Number of warnings
USER1,100,1
USER10,1000,1

Here's what I have

$ArrayDB1   = $file1 | Import-Csv | Select-Object Initials
$ArrayUSERS = $file2 | Import-Csv | Select-Object Initials
$ArrayDiff  = Compare-Object ($ArrayDB1) ($ArrayUSERS1) -Property Initials |
              Where-Object { $_.SideIndicator -eq '<=' } |
              Select-Object Initials

This gives me (in $ArrayDiff):

Initials
--------
USER12
USER13

So far so good. But when I try to remove it from file1 using the below

Select-String $file1 -Pattern $ArrayDiff -NotMatch |
  select -Expand line |
  Out-File "file3.log"

The file3 just gives me whats in file1. Nothing is removed:

Initials,Size excl. Backup/Pst,Number of warnings
USER1,100,1
USER10,1000,1
USER12,1100,1
USER13,1200,1

Upvotes: 1

Views: 2222

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200283

Try this:

$userlist = Import-Csv $file2 | % { $_.Initials }

Import-Csv $file1 | ? { $userlist -contains $_.Initials } | Export-Csv $file3

The first line creates an array from the Initials column of the 2nd CSV. With that array you can use the -contains operator for selecting just those records from the 1st CSV that have a matching Initials value.

Upvotes: 3

Related Questions