none
Exporting to CSV/Excel when an array has different Size Members RRS feed

  • Question

  • please excuse me if I am using the wrong terminology

    I have created a script that goes through VMware powercli and UCS manager using powertools  collecting data.  This data i am collecting creates members in an array, but each of the members could be of different sizes depending on the Host it is collecting data from. What i need help finding, is a way to export the data and have it export it all without cutting off longer members. 

    at the end i will put my code if that helps you.. 

    here is what you could see in the array "$result"

    One member could look like this..

    Host_Name               : Value
    vCenter -Value          : Value
    Cluster_Name            : Value
    UCS_DOMAIN              : Value
    vmk0_Name               : Value
    vmk0_Mac                : Value
    vmk0_IP                 : Value
    vmk0_Vlan               : Value
    vmk0_vNIC1              : Value
    vmk0_vNIC1_Mac          : Value
    vmk0_UCS_Template_vNIC1 : Value
    vmk0_vNIC2              : Value
    vmk0_vNIC2_Mac          : Value
    vmk0_UCS_Template_vNIC2 : Value
    vmk1_Name               : Value
    vmk1_Mac                : Value
    vmk1_IP                 : Value
    vmk1_Vlan               : Value
    vmk1_vNIC1              : Value
    vmk1_vNIC1_Mac          : Value
    vmk1_UCS_Template_vNIC1 : Value
    vmk1_vNIC2              : Value
    vmk1_vNIC2_Mac          : Value
    vmk1_UCS_Template_vNIC2 : Value
    vmk2_Name               : Value
    vmk2_Mac                : Value
    vmk2_IP                 : Value
    vmk2_Vlan               : Value
    vmk2_vNIC1              : Value
    vmk2_vNIC1_Mac          : Value
    vmk2_UCS_Template_vNIC1 : Value
    vmk3_Name               : Value
    vmk3_Mac                : Value
    vmk3_IP                 : Value
    vmk3_Vlan               : Value
    vmk3_vNIC1              : Value
    vmk3_vNIC1_Mac          : Value
    vmk3_UCS_Template_vNIC1 : Value
    vmk4_Name               : Value
    vmk4_Mac                : Value
    vmk4_IP                 : Value
    vmk4_Vlan               : Value
    vmk4_vNIC1              : Value
    vmk4_vNIC1_Mac          : Value
    vmk4_UCS_Template_vNIC1 : Value

    When another Could look like this

    Host_Name               : Value
    vCenter -Value          : Value
    Cluster_Name            : Value
    UCS_DOMAIN              : Value
    vmk0_Name               : Value
    vmk0_Mac                : Value
    vmk0_IP                 : Value
    vmk0_Vlan               : Value
    vmk0_vNIC1              : Value
    vmk0_vNIC1_Mac          : Value
    vmk0_UCS_Template_vNIC1 : Value
    vmk0_vNIC2              : Value
    vmk0_vNIC2_Mac          : Value
    vmk0_UCS_Template_vNIC2 : Value
    vmk1_Name               : Value
    vmk1_Mac                : Value
    vmk1_IP                 : Value
    vmk1_Vlan               : Value
    vmk1_vNIC1              : Value
    vmk1_vNIC1_Mac          : Value
    vmk1_UCS_Template_vNIC1 : Value
    vmk1_vNIC2              : Value
    vmk1_vNIC2_Mac          : Value
    vmk1_UCS_Template_vNIC2 : Value

    When I use a normal export-csv depending how its Formatted it may cut off the longer member in the CSV. so you dont get all the Headers.

    the work around i have works.  but i think its taking the log way around to do something that may be simpler another way i don't know about..

    Please help if you can.

    $result=@()
    $Vmhostsall = get-vmhost
    $hostcount = $Vmhostsall.count
    $hostNA_ALL = Get-VMHostNetworkAdapter -VMKernel | select VMhost,Name,Mac,PortGroupName,IP,deviceName
    $serPro = Get-UcsServiceProfile
    #$sermac = Get-UcsServiceProfile | get-ucsvnic
    $serhba = Get-UcsServiceProfile | Get-UcsVhba | select @{n="service_Profile_Name";e={@(($_.Dn).split("/")[3]).replace("ls-","")}},Name,Addr
    $i = 0
    Foreach($vmhost1 in $vmhostsall)
    {
    	$i = $i+1
    	Write-Host -ForegroundColor Yellow "************** Gathering Info for Host $vmhost1 ($i/$hostcount) ***********************"
    	$KC =($hostNA_ALL | ?{$_.VMhost -like $vmhost1.name}).count
    	$row = New-Object –TypeName PSObject
    	$row | add-member -type NoteProperty -name "Host_Name" -Value $vmhost1.Name
    	$row | add-member -type NoteProperty -name "vCenter -Value" ([system.net.dns]::resolve($vmhost1.ExtensionData.client.ServiceUrl.split('/')[2])).hostname
    	$row | add-member -type NoteProperty -name "Cluster_Name" -Value (get-cluster -vmhost $vmhost1.name).name
    	if(($serPro | ?{$_.name -eq ($vmhost1.name.split(".")[0])}) -eq $null){$row | add-member -type NoteProperty -name "UCS_DOMAIN" -Value "No UCS-Host"} Else {$row | add-member -type NoteProperty -name "UCS_DOMAIN" -Value (nslookup ($serPro | ?{$_.name -like (($vmhost1.name).split(".")[0])}).ucs)[3].replace(' ','').split(':')[1]}
    	$loop=1
    	do
    	{
    		$vmk_name = ($hostNA_ALL | ?{$_.VMhost -like $vmhost1.name -and $_.name} | select name).name[($loop-1)]
    		$row | add-member -type NoteProperty -name ("vmk"+($($loop)-1)+"_Name") -Value $vmk_name
    		$row | add-member -type NoteProperty -name ("vmk"+($loop-1)+"_Mac") -Value ($hostNA_ALL | ?{$_.VMhost -like $vmhost1.name -and $_.name -eq $vmk_name} | select mac).mac
    		$row | add-member -type NoteProperty -name ("vmk"+($loop-1)+"_IP") -Value ($hostNA_ALL | ?{$_.VMhost -like $vmhost1.name -and $_.name -eq  $vmk_name} | select IP).IP
    		$row | add-member -type NoteProperty -name ("vmk"+($loop-1)+"_Vlan") -Value (Get-VirtualPortGroup -VMHost $vmhost1.name -Name (($hostNA_ALL | ?{$_.VMhost -like $vmhost1.name -and $_.name -eq $vmk_name} | select PortGroupName).PortGroupName)).vlanid
    		$NICCOUNT = ((Get-VirtualPortGroup -VMHost $vmhost1.name -Name (($hostNA_ALL | ?{$_.VMhost -like $vmhost1.name -and $_.name -eq $vmk_name}).PortGroupName)).VirtualSwitch).nic.count
    		$loopNIC = 1
    		$proppull = 0
    		do
    		{
    			$VMK_0 = (Get-VirtualPortGroup -VMHost $vmhost1.name -Name (($($hostNA_ALL) | ?{$_.VMhost -like $($vmhost1.name) -and $_.name -eq $vmk_name}).PortGroupName)).VirtualSwitch | select nic
    			$VMK_0_1 = if($VMK_0.nic[$proppull] -eq ""){"none"} Else {$VMK_0.nic[$proppull]}
    			$row | add-member -type NoteProperty -name ("vmk"+($loop-1)+"_vNIC"+$loopNic) -Value $VMK_0_1
    			$row | add-member -type NoteProperty -name ("vmk"+($loop-1)+"_vNIC"+$loopNic+"_Mac") -Value  (Get-VMHostNetworkAdapter -VMHost $vmhost1.name | ?{$_.deviceName -eq ($row.("vmk"+($loop-1)+"_vNIC"+$loopNic))}).mac
    			$row | add-member -type NoteProperty -name ("vmk"+($loop-1)+"_UCS_Template_vNIC"+$loopNic) -Value  ($serPro | ?{$_.name -eq (($vmhost1.name).split(".")[0])} | get-ucsvnic | ?{$_.addr -eq ($row.("vmk"+($loop-1)+"_vNIC"+$loopNic+"_Mac"))}).NwTemplName
    			$loopNIC = $loopNIC + 1
    			$proppull = $proppull + 1
    		}While($loopNIC -le $NICCOUNT)
    		$loop= $loop+1
    	} While($loop -le $kc)
    	$result += $Row
    }
    $headerout = @()
    $a=@()
    $loopout = 0
    foreach($data in $result)
    {
    	$row1 = "" | select len,Value
    	$b=($data | Get-Member | ?{$_.membertype -eq "NoteProperty"}).length
    	$row1.len = $b
    	$row1.Value = $loopout
    	$a += $row1
    	$loopout = $loopout + 1
    }
    $c = ($a.len | measure -Maximum).Maximum 
    $d=($a |?{$_.len -eq $c})[0].value
    $header = ($result[$d] | get-member | ?{$_.membertype -eq "NoteProperty"}).name
    $headercount = $header.count
    $obj = new-object PSObject
    
    $loopH = 1 
    	foreach($head in $header){
    	$obj | add-member -membertype NoteProperty -name $($head) -value ""
    	$loopH = $loopH + 1
    	$headerout += $obj
    }
    
    Export-csv -InputObject $headerout[0] -NoTypeInformation -Append -Path ("c:\temp\"+$global:DefaultVIServers+"_Host_VMK_Info.csv") 
    $result | select * | Export-csv -NoTypeInformation -Append -Path ("c:\temp\"+$global:DefaultVIServers+"_Host_VMK_Info.csv") -force




    • Edited by JJ_Gmail Monday, August 13, 2018 6:28 PM
    Monday, August 13, 2018 6:04 PM

Answers

  • You need to redesign the code so that you output custom objects that have a consistent list of properties.


    -- Bill Stewart [Bill_Stewart]

    Monday, August 13, 2018 7:59 PM
    Moderator

All replies

  • Your question is vague.  What doe "cutting off longer members" mean.  Exporting does not cut off anything.

    What you have posted is the hash of name/value pairs.  I don't think this is what you want.

    When exporting objects the first object will always define the properties exported for all objects.  To prevent this use a select-object statement that specifies all properties required and the order to export.  Objects with missing properties will just have null entries for those properties.


    \_(ツ)_/

    Monday, August 13, 2018 6:29 PM
  • so a hash is the the group inside the array?   and member is the name/value of the hash?

    but I have done a "Select *" and it still did same thing where it would only export the Headers for the ones it exports first.  and I can not tell it what headers to export as the headers will change from Host to host. 

    and even the workaround i am using won't work for everything as it may change how many nics per VMK.

    in my code you will see I have 4 static that will be in every output. then it find all the VMK on each host, then it find ever NIC on each VMK. after it collects all that data and adds it to the $row,  it adds that $row to the $result array.

    I am sorry I am not 100% sure how else to explain it much better but will try if you have other questions.

    Monday, August 13, 2018 7:14 PM
  • The code posted is a bit odd and what you are trying to do is unclear.

    I recommend posting in the VMWare forum for help with your issues.

    Start by learning how to create objects and not hashes.  Hashes do not export meaningfully.

    We will not re-write your code.  You will need to learn enough about PwoerShell to redesign this correctly.   This forum is not a consulting forum.  We can answer specific questions but re-writing /re-designing code is beyond the scope of these forumscode


    \_(ツ)_/

    Monday, August 13, 2018 7:27 PM
  • I do not wish any one to re-write anything for me.. I have one direct question I am trying to get answered. the question i have is not a VMware direct question its an over all PowerShell one.

    i am sorry it is confusing for you. but to me its clearly laid out of what i am collecting and trying to display

    So in your opinion there is no way to export  the hash with all the Headers each time.  even if each hash has Different Headers or different amount of headers?

    • Edited by JJ_Gmail Monday, August 13, 2018 7:54 PM
    Monday, August 13, 2018 7:53 PM
  • You need to redesign the code so that you output custom objects that have a consistent list of properties.


    -- Bill Stewart [Bill_Stewart]

    Monday, August 13, 2018 7:59 PM
    Moderator
  • As  both Bill and I have noted.  You need to use objects and not hashes.

    I recommend that you start here to understand what we are trying to tell you: https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276


    \_(ツ)_/

    Monday, August 13, 2018 8:24 PM
  • Thank you 
    will look at Objects. 
    Tuesday, August 14, 2018 11:26 AM