CODEWITHSUNDEEP
powershellcsv
user2284702
user2284702

Reputation: 117

powershell filter csv

Looking for help on best practice as I am Powershell newbie,

I have a csv file and I would like to filter out every row in the csv except for the rows containing "Not Installed"

I would then like to filter those results against a separate csv files housing a list of computers and also exclude any rows containing a match.

Any help or starting points would be appreciated !

Upvotes: 6

Views: 28049

Answers (3)

Graham Gold
Graham Gold

Reputation: 2485

Came across this question when trying to work out how to do the same thing myself.

What I came up with as a solution was as follows:

$Filtered = $Table|Select-String -Pattern $ExcludeStrings -NotMatch -SimpleMatch
$FilteredArray = @()
$Filtered.Line | foreach {$split = $_ -split ';';
                          $tidied = $split -replace '@|{|}|^ ';
                          $obj = New-Object System.Object
                          $tidied|foreach {$obj|Add-Member -MemberType NoteProperty -Name ($_.split('=')[0]) -Value ($_.split('=')[1])
                                            }
                          $FilteredArray += $obj  
                            }

The first line is just showing me filtering the array that I got via Import-CSV to get only the results I wanted.

Unfortunately, this leaves me with a MatchInfo array, with each array being in the following format:

@{Column1=Value1; Column2=Value2; Column3=Value3;}

This does not export back out to CSV nicely at all, as is.

So, I opted to create a new output array ($FilteredArray).

I then piped the Line property of the matchinfo array from Select-String into a foreach loop, and for each line, I split it on the semi-colon, then I remove the @{} chars and any leading spaces.

I then create a new System.Object, then pipe the tidied output into another foreach loop, creating new NotePropertys in the obj, naming them from the current line in the tidied output up to the = character, and the value being the remaining text after the = character.

What I end up with is:

PS C:\Users\Graham> $obj

Column1            : Value1
Column2            : Value2
Column3            : Value3

And the filtered array looks like:

PS C:\Users\Graham> $FilteredArray[0..3]|Format-table

Column1         Column2        Column2                                    
-------         -------        -------
Value1          Value2         Value3 
Value1          Value2         Value3
Value1          Value2         Value3

Upvotes: 0

Keith Hill
Keith Hill

Reputation: 202032

For the first part, you can use Select-String -Notmatch ... e.g.:

$file = "data.csv"
$csv = Get-Content $file
$csv | Select-String -Notmatch 'Not Installed' | Out-File $file -Enc ascii

If your CSV file happens to be Unicode, then remove the "-Enc ascii" part since the default for Out-File is Unicode. As for the second part of your question, you're going to need to be bit more specific. However, you should look at the help on Import-Csv and Export-Csv:

man Import-Csv -full
man Export-Csv -full

Upvotes: 6

Paul Rowland
Paul Rowland

Reputation: 8352

get-help *csv*

get-help import-csv -examples

example csv file called test.txt

header1,header2,header3
installed,2,3
not installed,2,3

Import the file into a powershell variable and filter it. 

$p = Import-CSV .\test.txt
$p | Where { $_.header1 -eq 'installed'}

Upvotes: 4

Related Questions