none
PowerShell CSV and Duplicates in a multidemsional array RRS feed

  • Question

  • I have an issue where I am trying to accomplish several items. I am parsing through a host file, grabbing computer names and port numbers. I am then finding ports that are not used.  Here is the successful script that works, even though I want to combine the files (which I will get to):

    $data = Get-Content "Y:\Operations\host.txt"
    $list = @()
    $Previous = 200
    $Current = 0
    $Missing = $null
    
    foreach ($line in $data) {
        $Asset = $line -split '\s+|\t+'
        $PTTY = $line.Split("`=")[1]
        $Current = $PTTY -as [int]
        
        While ($Current -gt $Previous)
        {
            if($Current -ne ($Previous+1))
            {
                $Missing = $Previous+1
                echo $Missing >> "c:\Test\Missing.txt"
            
            }
            $Previous = $Previous + 1
        } 
        
        $list += New-Object psobject -Property @{col1=$Asset[0];col2=$PTTY}
        
    }
    $list | export-csv C:\Test\host.csv

    The first question is: 

    I want to find the duplicate computer names in col1.  I figured that I could do the following, but it does not work for multidemsional arrays.  How could I modify it to make it work?

    $duplicates = $list
    ($duplicates[1] | Group-Object | Where-Object {$_.Count -gt 1}).Values | export-csv C:\Test\duplicates.csv
    

    The Second:

    How would I combine all three arrays (Assuming I will create an array for the missing.txt) into a CSV knowing that each array are different sizes.  I also want to leave a row between the first array (PTTY and Computer names, each have their own column) and the second array (missing ones) and not between the second and third array (duplicates).

    Thank you in advance.

    Monday, January 25, 2016 6:15 PM

Answers

  • Try doing this with PowerShell an d not with a bunch of arbitrary arrays;

    Get-Content Y:\Operations\host.txt | 
    	ForEach-Obect{
    		$h=@{
    			'Asset Tag' = $line -split '\s+|\t+'
    			PTTYs = [int]($line.Split("`=")[1])
    		}
    		New-Object psobject -Property @{"PTTYs"=$PTTY;"Asset Tag"=$Asset[0]}
    	} | Group PTTY | %{$_.Group[0]}
    
    


    \_(ツ)_/

    • Marked as answer by Trey_b Wednesday, January 27, 2016 2:36 PM
    Monday, January 25, 2016 9:34 PM
  • I managed to figure out both issues. Here is the completed code with the duplicates and writing three arrays to the CSV file. It may be a bit clunky, but it is ran manually once every other week or so.

    $data = Get-Content "Y:\Operations\host.txt"
    $list, $Duplicates, $Missing = @(), @(), @()
    $Current, $Previous = 0, 200
    
    foreach ($line in $data) {
        $Asset = $line -split '\s+|\t+'
        $PTTY = $line.Split("`=")[1]
        $Current = $PTTY -as [int]
        
        While ($Current -gt $Previous)
        {
            if($Current -ne ($Previous+1)) { $Missing += New-Object psobject -Property @{Unused=$($Previous+1)} }
            $Previous = $Previous + 1
        } 
        $list += New-Object psobject -Property @{Asset=$Asset[0];PTTYs=$PTTY}   
    }
    $Duplicates = $list | Group Asset | Where-Object {$_.Count -gt 1} | Select-Object Name
    
    Write-output "Asset Tag,PTTYs,,Available PTTYs,Duplicate Asset Tags" | out-file c:\test\Host.csv -Encoding ASCII -Append
    for ($i=0; $i -le $list.count; $i++) {
        Write-output "$($list[$i] | Select-Object -ExpandProperty Asset),$($list[$i] | Select-Object -ExpandProperty PTTYs),,$($missing[$i] | Select-Object -ExpandProperty Unused),$($Duplicates[$i] | Select-Object -ExpandProperty Name)" | out-file c:\test\Host.csv -Encoding ASCII -Append
    
    }
    

    • Marked as answer by Trey_b Wednesday, January 27, 2016 2:36 PM
    Tuesday, January 26, 2016 9:46 PM

All replies

  • You are asking for a complete design solution.  Please ask a single question such as, "How can I combine these two files?"  or, "How can I get only unique records?"

    Here is how to "Unique" a CSV:

    Import-Csv file.csv | Group col1 |%{$_.Group[0]}


    \_(ツ)_/

    Monday, January 25, 2016 6:27 PM
  • Fine,  Lets tackle the first question.  Finding duplicates in an multidimensional array.  I do not want to do it on import since I am taking a flat text file and manipulating it until I get the two items I want out the line (and there is a ton of junk in there).  So one line of the array, $list, will have something to the effect of 295, J5cx87. where the first number would be the port and the second be the asset/PC name.

    I played around with the code a bit more took out majority of the other items:

    $data = Get-Content "Y:\Operations\host.txt"
    $list = @()
    $duplicates = @()
    
    foreach ($line in $data) {
        $Asset = $line -split '\s+|\t+'
        $PTTY = $line.Split("`=")[1]
        $Current = $PTTY -as [int]
        
        $list += New-Object psobject -Property @{"PTTYs"=$PTTY;"Asset Tag"=$Asset[0]}
        
    }
    
    $duplicates = Compare-object ($list | select –unique) $list | select -ExpandProperty inputobject

    host.txt is a downloaded host.cfg file that we downloaded from a server to keep out documentation up to date. 

    So knowing that the asset is the second part of the array....  How would I accomplish this?

    Monday, January 25, 2016 8:55 PM
  • What array are you talking about.  You have $list which is a one-dimensional array of objects.

    \_(ツ)_/

    Monday, January 25, 2016 9:23 PM
  • Try doing this with PowerShell an d not with a bunch of arbitrary arrays;

    Get-Content Y:\Operations\host.txt | 
    	ForEach-Obect{
    		$h=@{
    			'Asset Tag' = $line -split '\s+|\t+'
    			PTTYs = [int]($line.Split("`=")[1])
    		}
    		New-Object psobject -Property @{"PTTYs"=$PTTY;"Asset Tag"=$Asset[0]}
    	} | Group PTTY | %{$_.Group[0]}
    
    


    \_(ツ)_/

    • Marked as answer by Trey_b Wednesday, January 27, 2016 2:36 PM
    Monday, January 25, 2016 9:34 PM
  • I managed to figure out both issues. Here is the completed code with the duplicates and writing three arrays to the CSV file. It may be a bit clunky, but it is ran manually once every other week or so.

    $data = Get-Content "Y:\Operations\host.txt"
    $list, $Duplicates, $Missing = @(), @(), @()
    $Current, $Previous = 0, 200
    
    foreach ($line in $data) {
        $Asset = $line -split '\s+|\t+'
        $PTTY = $line.Split("`=")[1]
        $Current = $PTTY -as [int]
        
        While ($Current -gt $Previous)
        {
            if($Current -ne ($Previous+1)) { $Missing += New-Object psobject -Property @{Unused=$($Previous+1)} }
            $Previous = $Previous + 1
        } 
        $list += New-Object psobject -Property @{Asset=$Asset[0];PTTYs=$PTTY}   
    }
    $Duplicates = $list | Group Asset | Where-Object {$_.Count -gt 1} | Select-Object Name
    
    Write-output "Asset Tag,PTTYs,,Available PTTYs,Duplicate Asset Tags" | out-file c:\test\Host.csv -Encoding ASCII -Append
    for ($i=0; $i -le $list.count; $i++) {
        Write-output "$($list[$i] | Select-Object -ExpandProperty Asset),$($list[$i] | Select-Object -ExpandProperty PTTYs),,$($missing[$i] | Select-Object -ExpandProperty Unused),$($Duplicates[$i] | Select-Object -ExpandProperty Name)" | out-file c:\test\Host.csv -Encoding ASCII -Append
    
    }
    

    • Marked as answer by Trey_b Wednesday, January 27, 2016 2:36 PM
    Tuesday, January 26, 2016 9:46 PM