Hello!
It’s been a while since I’ve posted but here I am again looking for help!
I have been tasked with somehow creating a way to import a CSV that has more than 2000 lines and scrubbing the data from it that isn’t needed.
The CSV Looks something like this:
“Plugin”,“Plugin Name”,“Family”,“Severity”,“IP Address”,“Protocol”,“Port”,“Exploit?”,“Repository”
“111111”,“Ping the remote host”,“Port scanners”,“Info”,“X.X.X.X”,“TCP”,“0”,“No”,“Individual Scan”
Then has data that ends up coming in different strings such as:
Object UUID :00000000000-0000-0000-000000000000
UUID : xxx-xxxx-xxxx-xxxxx-xxxxxx version 1.0
Description : Unknown RPC service
Annotation : Impl friendly name
Type : Local RPC service
Named pipe : LRPC-1234567
Then further down has webaddresses and hash numbers that start with 0x, and all of this data always shows up in the Plugin row.
The script I have somewhat works, but I find that when I try to go in and remove data, or look at the CSV in notepad the data I thought was removed actually isn’t, but doesn’t show up if I open it in CSV. I have tried using get-content, but it exports the CSV unformatted, and I’ve tried using other comparisons such as -notlike and -notmatching but they don’t seem to work either.
Imported/Exported Variables $CSVData = Import-Csv -Path "C:\Test.csv" $MatchingExportFile = "C:\RemovedItems.csv" $NonMatchingExportFile = "C:\NewOutput.csv" $MatchingData = @() $NonMatchingData = @() #Strings to search and remove from excel - Possibility to Add More $RegexStrings = '*Object UUID*', '*UUID*', '*Description*','*Annotation*','*Type*','*senssvc*','*Named*','*Windows*','*0x*','*3A*','*navigation*','*Value*','*+ CGI*','*Methods*','*Argument*','*- https*','*static*','*Attached*' #Headers $headerSelection = "Plugin", "Plugin Name","Family","IP Address","Port","Exploit","DNS Name","NetBIOS Name","Plugin Text","Synopsis","Description","Solution","Check Type","Version" Foreach ($Row in $CSVData) { $MatchFound = $False Foreach ($TestString in $RegexStrings) { If ($Row.Plugin -like $TestString) { $MatchFound = $True Break } } If ($MatchFound) {$MatchingData += $Row} Else {$NonMatchingData += $Row} } If ($MatchingData.Count -gt 0) { $MatchingData | Export-Csv -Path $MatchingExportFile -Force -NoTypeInformation Write-Host "Matching Data exported to $MatchingExportFile" } Else {Write-Host "No matching data to export!"} If ($NonMatchingData.Count -gt 0) { $NonMatchingData | Select $headerSelection | Export-Csv -Path $NonMatchingExportFile -Force -NoTypeInformation Write-Host "Non-matching Data exported to $NonMatchingExportFile" } Else {Write-Host "No non-matching data to export!"}
My goal is to import the CSV, delete the cells with the data that is not needed, and delete other blank cells that show up, then export to new CSV. But I cannot get it to successfully do either.
Can somebody provide some input on what I may be doing wrong?
Thank you