Author Posts

January 12, 2016 at 11:56 pm

Hi guys

I need some help with a CSV file and data extraction. I have a CSV file that looks like the following:

Subnet Ipaddress IsInBoth
255.255.255.0 10.1.10.0 10.1.10.0
255.255.255.0 10.1.20.0
255.255.254.0 10.2.10.0
255.255.252.0 10.3.10.0 10.2.10.0
255.255.255.0 10.4.10.0 10.3.10.0

I need to read the IsInBoth value for each row, compare it to every item of data in the Ipaddress column in the file and if they match extract the Subnet for that row.

Is the above possible?

Thanks

Barry

January 13, 2016 at 1:41 am

Based on the data in your post, and with the following assumptions:

  • Space is used as delimiter in the CSV file
  • CSV is named IP.Csv

    You could do something like this:

    # Import your CSV data
    $CSVData = Import-Csv -Delimiter ' ' -Path .\IP.Csv
    
    # Loop through each row in your data set, and filter out rows with no value in IsInBoth
    foreach ($Row in ($CSVData | Where-Object { $_.IsInBoth.Length -gt 0 } ) ) {
    
        # Check if IsInBoth value is in the Ipaddress column
        if ($Row.IsInBoth -in $CSVData.Ipaddress) {
        
            # Output the Subnet from the current row
            $Row.Subnet
        
        }
    
    }
    
  • January 13, 2016 at 2:29 am

    Dawned on me that what you asked for might have been the subnet from the row with the matching Ipaddress – in that case you could do this instead:

    # Import your CSV data
    $CSVData = Import-Csv -Delimiter ' ' -Path .\IP.Csv
    
    # Loop through each row in your data set, and filter out rows with no value in IsInBoth
    foreach ($Row in ($CSVData | Where-Object { $_.IsInBoth.Length -gt 0 } ) ) {
    
        # Check if IsInBoth value is in the Ipaddress column
        if ($Row.IsInBoth -in $CSVData.Ipaddress) {
        
            # Output the Subnet from the record with Ipaddress matching IsInBoth
            $CSVData | Where-Object { $_.Ipaddress -eq $Row.IsInBoth } | Select-Object -ExpandProperty Subnet
            
        }
    
    }
    

    January 13, 2016 at 3:28 am

    Hi Christian

    Many thanks for the quick replies and excellent solution.

    Thanks

    Barry