Filter CSV file with powershell

Question

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

An image of your code is not helpful - that is also valid for all kinds of sample data. ... and you have a typo in your code -ey -> -eq — Olaf
– Olaf, Commented Feb 21, 2019 at 14:28
If your data is not valid CSV you cannot expect Powershell to gracefully deal with it. You will have to transform your source data to fit your needs. — Olaf
– Olaf, Commented Feb 21, 2019 at 14:31

FoxDeploy · Accepted Answer · 2019-02-21 21:01:07Z

2

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

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

edited Feb 21, 2019 at 21:01

answered Feb 21, 2019 at 14:49

FoxDeploy

13.8k3 gold badges42 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spikey Over a year ago

Thanks Mate... it worked...i can able to sort the data.... however the newly created CSV (Output) is doesn't contain that skipped data .... Is there any way we can included that too after processing? just checking possibility.

Spikey Over a year ago

Thank you.. It worked, however the output csv was scrambled (the second part, combined in singled row and columns), I did some modifications.. now it is perfect... Thank you Very much.. :)

Spikey · Accepted Answer · 2019-02-22 07:51:48Z

0

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

answered Feb 22, 2019 at 7:51

Spikey

151 gold badge1 silver badge4 bronze badges

Collectives™ on Stack Overflow

Filter CSV file with powershell

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related