CSV data manipulaton

PowerShell Help
1

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

2

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
    
    }

}
    • 3

    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
        
    }

}
    4

    Hi Christian

    Many thanks for the quick replies and excellent solution.

    Thanks

    Barry

    5

    You’re welcome