CODEWITHSUNDEEP
powershellcsv
Spikey
Spikey

Reputation: 15

Filter CSV file with powershell

I am new to powershell ... I am trying to filter one of my scan result (.csv) on specific value, I am able to do this for simple CSV files. however the automated scan result seems like nested.

I need to filter Column "F" where the value is "Vuln" (Image is added) and write to a new CSV file.

can anyone please give me some leads.

I tried simple lines like : (but I didnt get any result)

Import-CSV -Path "C:\test.csv" | Where-Object {$_.Type -ey"Vuln"}

Sample CSV file format

Upvotes: 1

Views: 14606

Answers (2)

Spikey
Spikey

Reputation: 15

Here is the final script :)

$import = get-content .\Scan.csv
$import1= $import | Select-Object -First 7 $import | Select-Object -Skip 7 | ConvertFrom-Csv | Where-Object {$_.Type -eq "Vuln"} | Export-Csv Output1.csv -NoClobber -NoTypeInformation
$import1 + (Get-Content Output1.csv) | Set-Content Output2.csv

Upvotes: 0

FoxDeploy
FoxDeploy

Reputation: 13567

You're mostly there, it looks like problem is coming from that .CSV file.

Frankly, it isn't a truly valid csv file, since it isn't just a simple Comma Separated Value sheet, instead it has multiple types of data.

However, it looks like something close to a real .CSV begins on line 6, so what we'll need to do is skip the first few rows of the file and then try to convert from .csv. I made my own .csv like yours

enter image description here

I can read the file and skip the first five lines like so:

Get-Content C:\temp\input.csv | Select-Object -Skip 5 | ConvertFrom-Csv 
HostName  OS    Type   
--------  --    ----   
SomePC123 WinXp Vuln   
SomePC234 Win7  Vuln   
SomePc345 Win10 Patched

And then filter down to just items of Type Vuln with this command:

Get-Content C:\temp\input.csv | Select-Object -Skip 5 | ConvertFrom-Csv | Where-Object Type -eq 'Vuln'

HostName  OS    Type
--------  --    ----
SomePC123 WinXp Vuln
SomePC234 Win7  Vuln

To use this, just count down the number of lines until the spreadsheet begins within your .CSV and edit the -Skip parameter to match.

If you want to keep the header information, you can use an approach like this one:

$crap = Get-Content C:\temp\input.csv | Select-Object -First 5
$OnlyVulns = Get-Content C:\temp\input.csv | Select-Object -Skip 5 | ConvertFrom-Csv | Where-Object Type -eq 'Vuln'

$CrapAndOnlyVulns = $crap + $OnlyVulns
$CrapAndOnlyVulns > C:\pathTo\NewFile.csv

Upvotes: 2

Related Questions