Adding column to existing csv output from vcenter powershell

location vcenter username password abc 10.1 X xxx def 10.2 Y yyy

a csv file which has location column which is a location and in that location IP column indicates the vCenter IP

i need to generate all host details and add location field as the first field in the final csv but not able to do so

Example

Suppose location abc has a vCenter with 5 hosts and location def has a vCenter with 3 hosts

The output should look like

location    name    version
abc         h1      6.5
abc         h2      6.5
abc         h3      6.5
abc         h4      6.5
abc         h5      6.5 
def         hxx     6.7
def         hyy     6.7
def         hzz     6.7

script

$inventory = Import-Csv -path path.csv

foreach($i in $inventory){
    Connect-VIServer -Server $i.vcenter -User $i.username -Password $i.password
    Get-VMHost | Select-Object -Property Name,Version | Export-csv tempoutput.csv -NoTypeInformation -Append
    $ExistingCSV = Import-Csv -Path 'tempoutput.csv'
    $ExistingCSV | Add-Member -MemberType NoteProperty -Name 'Location' -Value $i.location 
    $ExistingCSV | Export-Csv final.csv -NoTypeInformation  
    Disconnect-VIServer -Server $i.vcenter -Force -Confirm:$false
}

But this is not working

Solution

I would only write to the output CSV once provided your memory can hold all of the Get-VMHost data. You may make use of calculated properties to prevent having to use a temporary CSV file.

$inventory = Import-Csv -path path.csv

$output = foreach ($i in $inventory) {
    $null = Connect-VIServer -Server $i.vcenter -User $i.username -Password $i.password
    Get-VMHost | Select-Object -Property Name,Version,@{n='Location';e={$i.Location}}
    $null = Disconnect-VIServer -Server $i.vcenter -Force -Confirm:$false
}

$output | Export-Csv final.csv -NoType

It may be worthwhile to consider a redesign if you have repeated entries of the same vCenter and credentials in the CSV file. Connecting and disconnecting to the same vCenter so many times is likely inefficient. You may make use of Group-Object to only connect and disconnect from vCenter when the vCenter server changes.

$inventory = Import-Csv -path path.csv | Group-Object vcenter,username,password | Foreach-Object {
    $vc = $_.Group[0] | Select-Object vcenter,username,password,location
    $null = Connect-VIServer -Server $vc.vcenter -User $vc.username -Password $vc.password
    Get-VMHost | Select-Object -Property Name,Version,@{n='Location';e={$vc.Location}}
    $null = Disconnect-VIServer -Server $vc.vcenter -Force -Confirm:$false
}

$inventory | Export-Csv final.csv -NoType

In your attempt, you are reading the entire temp CSV in $ExistingCSV during each loop iteration. Then you are adding the property Location to the entire object $ExistingCSV, which adds it to every item in the $ExistingCSV list with the same value. So at the end of the loop, every line of the CSV has a Location value that matches the last item in the loop.