Home > Enterprise >  Powershell: Embedded Hashtable writing to CSV
Powershell: Embedded Hashtable writing to CSV

Time:10-26

Currently trying to write a Hashtable to a CSV file. The Format I am looking for is

IP Ports
IP Port
Port
Port
IP Port
Port
Port

I have encountered two problems while doing this.

1.)

IP Ports
IP System.Collections.DictionaryEntry System.Collections.DictionaryEntry etc...
IP System.Collections.DictionaryEntry System.Collections.DictionaryEntry etc...
IP System.Collections.DictionaryEntry System.Collections.DictionaryEntry etc...
IP System.Collections.DictionaryEntry System.Collections.DictionaryEntry etc...

2.)

#TYPE System.Management.Automation.PSCustomObject
IP IP IP IP IP
@{Port=; Port=; Port=;....} @{Port=; Port=; Port=;...} @{Port=; Port=; Port=;...} @{Port=; Port=; Port=;...} @{Port=; Port=; Port=;...}

Below is the code

$filepath = "ip.csv"
$host_list = Import-Csv $filepath

$hash = @{}
$host_list | ForEach-Object{
    if($hash.ContainsKey($_.IP)){
            $IP = $_.IP
            if($hash.$IP.ContainsKey($_.Port)){
            }
            else{
                $Port = $_.Port
                $hash.$IP.$Port = @{}
            }
    }
    else{
        $IP = $_.IP
        $hash.$IP = @{}
    }
}

##Gives Table 1 Csv output
ForEach-Object{$hash.GetEnumerator() | sort Name} |
   Select-Object -Property @{N='IP'; E ={$_.Key}}, @{N='Port'; E={$_.Value.GetEnumerator()}}|
   Export-Csv -NoTypeInformation -Path "IPList.csv"

##Gives Table 2 csv output 

$HashObj = New-Object -TypeName PSObject -Property $hash
$HashObj | ConvertTo-Json |Set-Content -Path "IPList.json"

Get-Content IPList.json | ConvertFrom-Json | Export-Csv PlzWork.csv 


CodePudding user response:

Your desired CSV output format is unusual, in that the rows aren't self-contained:

It sounds like you want an empty IP column to imply that the row's Port column value is associated with the IP value of the most recent row with a non-empty IP column value.

To get the format as described, try the following (to switch to a self-contained row format, remove the $ip = $null statement):

$hash.GetEnumerator() | Sort-Object Name | ForEach-Object { 
  $ip = $_.Name
  foreach ($port in $_.Value.Keys) { 
    [pscustomobject] @{ IP = $ip; Port = $port } # construct and output an object
    $ip = $null 
  }
} | Export-Csv -NoTypeInformation IPList.cs
  • To see the resulting objects in tabular form before exporting, omit the | Export-Csv ... call.

  • To output the resulting CSV data as strings, by default to the display, replace | Export-Csv ... with | ConvertTo-Csv

  • Related