Join VM info with external info

[TheVMinator]

I need to take an existing spreadsheet with a list of VM names, merge information from vCenter with it, and create a new csv that I can open in Excel.

For example, my existing spreadsheet looks like this:

name	Location	Owner
Hostname1	NY	Bob
hostname2	LA	Joe
hostname3	DC	Bill

then, I have powercli script that does this:

get-vm | select name, numcpu, memoryGB

The "Name" field in my spreadsheet is equivalent to the "name" field in vCenter.  How can I create a csv that has the info both from vCenter and the spreadsheet for each VM?

Thanks!

[bayupw]

Could you change your column name from 'name' to 'VMName' so it doesn't conflict?

Or you also could do 2 sheets and the third sheet will have a combined spreadsheet using function like VLOOKUP VLOOKUP function - Office Support

[LucD · Accepted solution]

Try like this

$report = foreach($row in Import-Csv -Path vmnames.csv -UseCulture){

    Get-VM -Name $row.name |

    Select Name,NumCpu,MemoryGB,

        @{N='Location';E={$row.Location}},

        @{N='Owner';E={$row.Owner}}

}

$report | Export-Csv -Path newreport.csv -NoTypeInformation -UseCulture

[TheVMinator]

ok thanks again