4 Replies Latest reply on Sep 24, 2020 4:48 PM by rxjoseph

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

    rxjoseph Enthusiast

      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}

        • 1. Re: Get input file, export CSV file without the rows from the input file
          LucD Guru
          User ModeratorsCommunity WarriorsvExpert

          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: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
          • 2. Re: Get input file, export CSV file without the rows from the input file
            rxjoseph Enthusiast

            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

            • 3. Re: Get input file, export CSV file without the rows from the input file
              LucD Guru
              Community WarriorsvExpertUser Moderators

              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: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
              • 4. Re: Get input file, export CSV file without the rows from the input file
                rxjoseph Enthusiast

                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

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