Hi LucD
I am trying to filter a VRNI CSV file and I need some help to exclude IP address which i have in an input.csv file
Once it excludes then i need to create a new CSV with only the excluded data
Here is my Input.CSV file
ipaddress |
192,168,1,10 |
192,168,1,11 |
192,168,1,12 |
192,168,1,13 |
192,168,1,14 |
Here is my output.csv file
desip | ALLOW | Protocol | SourceIP |
192,168,1,10 | ALLOW | TCP | 10,10,10,1 |
192,168,1,11 | ALLOW | TCP | 10,10,10,2 |
192,168,1,12 | ALLOW | TCP | 10,10,10,3 |
192,168,1,13 | ALLOW | TCP | 10,10,10,4 |
192,168,1,14 | ALLOW | TCP | 10,10,10,5 |
10.0.0.1 | ALLOW | UDP | 10,10,10,6 |
10.25.102.61 | ALLOW | TCP | 10,10,10,7 |
10.25.102.67 | ALLOW | TCP | 10,10,10,8 |
10.25.102.73 | ALLOW | TCP | 10,10,10,9 |
10.25.102.79 | ALLOW | TCP | 10,10,10,10 |
10.25.102.85 | ALLOW | TCP | 10,10,10,11 |
10.25.102.91 | ALLOW | TCP | 10,10,10,12 |
10.25.102.97 | ALLOW | TCP | 10,10,10,13 |
10.25.102.103 | ALLOW | TCP | 10,10,10,14 |
10.25.102.109 | ALLOW | TCP | 10,10,10,15 |
10.25.102.115 | ALLOW | TCP | 10,10,10,16 |
10.25.102.121 | ALLOW | UDP | 10,10,10,17 |
10.25.102.127 | ALLOW | UDP | 10,10,10,18 |
10.25.102.133 | ALLOW | UDP | 10,10,10,19 |
I need to read the Input CSV and if the IP address on the Input CSV matches the output.CSV then i need to exclude that line and i need to export a new CSV file with the excluded CSV file
Because i dont need any ip address from the input file on the new CSV file because it garbage data
When i execute this code i get all the entries and its not excluding the data found from the input file
Can you help me with this please
Script
$inputfile=import-csv -Path "C:\temp\input.csv"
$outputfile = import-csv -Path "C:\Temp\VRNIOutputfile.CSV"
$outputfile |where {$inputfile.ipaddress -notin $_.desip}
If the output file really uses ';' as a separator, you would need to specify that.
$outputfile = import-csv -Path "VRNIOutputfile.CSV" -Delimiter ";"
$outputfile |where {$inputfile.ipaddress -notcontains $_.desip}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
That should be the -notconatins condition I think
$outputfile = import-csv -Path "C:\Temp\VRNIOutputfile.CSV"
$outputfile |where {$inputfile.ipaddress -notcontains $_.desip}
But I did notice that some of your IP address have the comma as separator.
Thta might cause issues.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Hi LucD
It was a typo from my end when i changed the ip address for security reasons
As advised I added -notcontains
But still i get the entire file its not excluding the IP with matching rows
desip;ALLOW;Protocol;SourceIP
-----------------------------
192.168.1.10;ALLOW;TCP;10.10.10.1
192.168.1.11;ALLOW;TCP;10.10.10.2
192.168.1.12;ALLOW;TCP;10.10.10.3
192.168.1.13;ALLOW;TCP;10.10.10.4
192.168.1.14;ALLOW;TCP;10.10.10.5
10.0.0.1;ALLOW;UDP;10.10.10.6
10.25.102.61;ALLOW;TCP;10.10.10.7
10.25.102.67;ALLOW;TCP;10.10.10.8
10.25.102.73;ALLOW;TCP;10.10.10.9
10.25.102.79;ALLOW;TCP;10.10.10.10
10.25.102.85;ALLOW;TCP;10.10.10.11
10.25.102.91;ALLOW;TCP;10.10.10.12
10.25.102.97;ALLOW;TCP;10.10.10.13
10.25.102.103;ALLOW;TCP;10.10.10.14
10.25.102.109;ALLOW;TCP;10.10.10.15
10.25.102.115;ALLOW;TCP;10.10.10.16
10.25.102.121;ALLOW;UDP;10.10.10.17
10.25.102.127;ALLOW;UDP;10.10.10.18
10.25.102.133;ALLOW;UDP;10.10.10.19
Please advise
Many thanks
RXJ
If the output file really uses ';' as a separator, you would need to specify that.
$outputfile = import-csv -Path "VRNIOutputfile.CSV" -Delimiter ";"
$outputfile |where {$inputfile.ipaddress -notcontains $_.desip}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Hi LucD
Thanks for helping me out, i really appreciate all your help
It was the -Delimiter "," which i missed and never realised
I have added the full code for the use of everyone in the community
Thanks again
<#
.Synopsis
This script is written to filter a column from a CSV file called Input file and filter the contents and produce a new file
.DESCRIPTION
Long description
.EXAMPLE
Example of how to use this cmdlet
.EXAMPLE
Another example of how to use this cmdlet
.INPUTS
Inputs to this cmdlet (if any)
.OUTPUTS
Output from this cmdlet (if any)
.NOTES
General notes
.COMPONENT
The component this cmdlet belongs to
.ROLE
The role this cmdlet belongs to
.FUNCTIONALITY
The functionality that best describes this cmdlet
#>
Add-Type -AssemblyName System.Windows.Forms
####################################################################################
#Input CSV file to filter IP address -Input IPADDRESS list
####################################################################################
$FileBrowserInput = New-Object System.Windows.Forms.OpenFileDialog -Property @{
InitialDirectory = [Environment]::GetFolderPath('Desktop')
#Filter = 'CSV Files (*.CSV)|*.docx|SpreadSheet (*.xlsx)|*.xlsx'
Filter = 'CSV Files (*.csv)|*.csv|SpreadSheet (*.xlsx)|*.xlsx'
}
Write-Host "Select Input file to filter IP address... (see FileOpen Dialog)" -ForegroundColor Green
$res1 = $FileBrowserInput.ShowDialog()
$res1
if($res1 -eq "OK") {
Write-Host "Selected Input File:" -ForegroundColor Green
$FileBrowserInput.filename
# $OpenFileDialog.CheckFileExists
Write-Host "Input CSV File Selected" -ForegroundColor Green
}
else { Write-Host "Input File selection Cancelled!" -ForegroundColor Yellow}
$FileBrowserInputFilename= $FileBrowserInput.filename
####################################################################################
#Select the VRNI File
####################################################################################
$FileBrowserVRNIFile = New-Object System.Windows.Forms.OpenFileDialog -Property @{
InitialDirectory = [Environment]::GetFolderPath('Desktop')
#Filter = 'CSV Files (*.CSV)|*.docx|SpreadSheet (*.xlsx)|*.xlsx'
Filter = 'CSV Files (*.csv)|*.csv|SpreadSheet (*.xlsx)|*.xlsx'
}
Write-Host "Select VRNI CSV file ... (see FileOpen Dialog)" -ForegroundColor Green
$res2 = $FileBrowserVRNIFile.ShowDialog()
$res2
if($res2 -eq "OK") {
Write-Host "Selected VRNI File:" -ForegroundColor Green
$FileBrowserVRNIFile.filename
# $OpenFileDialog.CheckFileExists
Write-Host "VRNI CSV File Selected for filtering " -ForegroundColor Green
}
else { Write-Host "VRNI File selection Cancelled!" -ForegroundColor Yellow}
$FileBrowserVRNIFilename= $FileBrowserVRNIFile.filename
####################################################################################
#Select OutPut File location to Save new VRNI filtered file as a CSV
####################################################################################
$SaveFileDialog = New-Object windows.forms.savefiledialog
$SaveFileDialog.initialDirectory = [System.IO.Directory]::GetCurrentDirectory()
$SaveFileDialog.title = "Save File to Disk"
#$SaveFileDialog.filter = "All files (*.*)| *.*"
#$SaveFileDialog.filter = "PublishSettings Files|*.publishsettings|All Files|*.*"
$SaveFileDialog.filter = "CSV Files|*.CSV|VRNI Filtered OutPut Files|*.publishsettings|All Files|*.*"
$SaveFileDialog.ShowHelp = $True
Write-Host "Where would you like to Save the CSV file?... (see File Save Dialog)" -ForegroundColor Green
$result = $SaveFileDialog.ShowDialog()
$result
if($result -eq "OK") {
Write-Host "Selected File and Location:" -ForegroundColor Green
$SaveFileDialog.filename
}
else { Write-Host "File Save Dialog Cancelled!" -ForegroundColor Yellow}
#$SaveFileDialog.Dispose()
$Savefile=$SaveFileDialog.filename
####################################################################################
#Code to Filter files
####################################################################################
$inputfile=import-csv -Path $FileBrowserInputFilename
$VRNIoutputfile = import-csv -Path $FileBrowserVRNIFilename -Delimiter ","
$VRNIoutputfile |where {$inputfile.ipaddress -notcontains $_.'Destination IP Address'} |
Export-csv -Path $Savefile -NoClobber -NoTypeInformation
####################################################################################
# E N D of S C R I P T
####################################################################################