VMware Cloud Community
rxjoseph
Enthusiast
Enthusiast
Jump to solution

Get input file, export CSV file without the rows from the input file

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

    

desipALLOWProtocolSourceIP
192,168,1,10ALLOWTCP10,10,10,1
192,168,1,11ALLOWTCP10,10,10,2
192,168,1,12ALLOWTCP10,10,10,3
192,168,1,13ALLOWTCP10,10,10,4
192,168,1,14ALLOWTCP10,10,10,5
10.0.0.1ALLOWUDP10,10,10,6
10.25.102.61ALLOWTCP10,10,10,7
10.25.102.67ALLOWTCP10,10,10,8
10.25.102.73ALLOWTCP10,10,10,9
10.25.102.79ALLOWTCP10,10,10,10
10.25.102.85ALLOWTCP10,10,10,11
10.25.102.91ALLOWTCP10,10,10,12
10.25.102.97ALLOWTCP10,10,10,13
10.25.102.103ALLOWTCP10,10,10,14
10.25.102.109ALLOWTCP10,10,10,15
10.25.102.115ALLOWTCP10,10,10,16
10.25.102.121ALLOWUDP10,10,10,17
10.25.102.127ALLOWUDP10,10,10,18
10.25.102.133ALLOWUDP10,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}

Reply
0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership
Jump to solution

If the output file really uses ';' as a separator, you would need to specify that.

$inputfile = import-csv -Path "input.csv"

$outputfile = import-csv -Path "VRNIOutputfile.CSV" -Delimiter ";"

$outputfile |where {$inputfile.ipaddress -notcontains $_.desip}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

Reply
0 Kudos
4 Replies
LucD
Leadership
Leadership
Jump to solution

That should be the -notconatins condition I think

$inputfile=import-csv -Path "C:\temp\input.csv"

$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

Reply
0 Kudos
rxjoseph
Enthusiast
Enthusiast
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

If the output file really uses ';' as a separator, you would need to specify that.

$inputfile = import-csv -Path "input.csv"

$outputfile = import-csv -Path "VRNIOutputfile.CSV" -Delimiter ";"

$outputfile |where {$inputfile.ipaddress -notcontains $_.desip}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
rxjoseph
Enthusiast
Enthusiast
Jump to solution

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

####################################################################################

Reply
0 Kudos