topic Re: Join VM info with external info in VMware PowerCLI Discussions

Join VM info with external info

TheVMinator — Fri, 17 Feb 2017 22:47:10 GMT

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!

Re: Join VM info with external info

bayupw — Sat, 18 Feb 2017 00:24:27 GMT

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

Re: Join VM info with external info

LucD — Sat, 18 Feb 2017 08:19:02 GMT

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

Re: Join VM info with external info

TheVMinator — Mon, 27 Feb 2017 17:55:04 GMT

ok thanks again