CSV data manipulaton

Tagged: 

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Christian Sandfeld Christian Sandfeld 11 months ago.

  • Author
    Posts
  • #33811
    Profile photo of Barry Walledge
    Barry Walledge
    Participant

    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

  • #33816
    Profile photo of Christian Sandfeld
    Christian Sandfeld
    Participant

    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
        
        }
    
    }
    
  • #33818
    Profile photo of Christian Sandfeld
    Christian Sandfeld
    Participant

    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
            
        }
    
    }
    
  • #33822
    Profile photo of Barry Walledge
    Barry Walledge
    Participant

    Hi Christian

    Many thanks for the quick replies and excellent solution.

    Thanks

    Barry

  • #33823
    Profile photo of Christian Sandfeld
    Christian Sandfeld
    Participant

    You're welcome

  • You must be logged in to reply to this topic.