Import Several Variables from CSV file

[BrentCocjhran]

Hello PowerCLI Masters!

I've been dabbling in PowerCLI now for about 6 months, mostly for larger scale VM deployments.  I have been tweaking my scripts to introduce more automation over time.  I started out with setting all the variables manually and just importing VM names from a txt file, then created several loops in a single script that would import from different text files, depending on template, datastore, disk sizes, VLAN, etc.

I believe the next step here is to import variables per vm (essentially, a row of a CSV file).  We receive our build requests in a spreadsheet form so it would be relatively easy to populate the correct fields in a sheet, and run a script that will import the values.  I can't seem to get on the right track with simple searching so I thought I would post it here and see if:

1. someone has already done this and can provide some guidance

2. someone can put me on the right track to how I would go about doing this

3. someone can tell me it's not possible (hoping it doesn't go down like this, but it's certainly possible)

THIS IS JUST A SAMPLE SCRIPTS - may or may not have perfect syntax at this point...

## SET VARIABLES ##

$vmname = get-content C:\PowerShell\serverlist_1.txt
$vmname | foreach-object {
$vmname=$_

$templatename="TMPLT-GSI"
$folder="GSI"
$date= Get-Date -uformat "%Y%m%d"
$expdate="20110930"
$reqno="00034"
$sysowner="GSI"

$esxhost="esxhost1.test.com"
$datastore="DATASTORE_1"

$cpu=1
$mem=4096
$datadrive1=26214400

$datadrive2=52428800
$pgname="ECOM_902"

#

## BUILD/CONFIGURE VM ##

#

New-VM -vmhost $esxhost -Name $vmname -Template $templatename -Datastore $datastore -DiskStorageFormat thick -Location $folder

New-Harddisk -vm $vmname -CapacityKB $datadrive1
New-Harddisk -vm $vmname -CapacityKB $datadrive2
Get-VM -Name $vmname | Set-VM -MemoryMB $mem -NumCPU $cpu -Confirm:$false
get-networkadapter $vmname | set-networkadapter -networkname $pgname -Confirm:$false
Get-VM -Name $vmname | Set-CustomField -Name "Creation Date" -Value $date -Confirm:$false
Get-VM -Name $vmname | Set-CustomField -Name "Expiration Date" -Value $expdate -Confirm:$false
Get-VM -Name $vmname | Set-CustomField -Name "Request Number" -Value $reqno -Confirm:$false
Get-VM -Name $vmname | Set-CustomField -Name "System Owner" -Value $sysowner -Confirm:$false

}

So, rather than setting these values in the script, I'd like to yank them from a sheet that looks something like this (only a partial list provided of course):

$vmname	$templatename	$cpu	$mem	$pgname	$datadrive1	$datadrive2
Testdeploy001	TMPLT-RHEL6	1	4	"ECOM_902"	26214400	52428800
Testdeploy002	TMPLT-2008_ENT_64	2	8	"ECOM_902"	26214400	104857600
Testdeploy003	TMPLT-2008_ENT_64	2	8	"ECOM_906"	26214400	104857600

Thanks in advance for any assistance provided!

-Brent

[alanrenouf · Accepted solution]

vmname	templatename	cpu	mem	pgname	datadrive1	datadrive2	folder
Testdeploy001	TMPLT-RHEL6	1	4	"ECOM_902"	26214400	52428800	GSI
Testdeploy002	TMPLT-2008_ENT_64	2	8	"ECOM_902"	26214400	104857600	GSI
Testdeploy003	TMPLT-2008_ENT_64	2	8	"ECOM_906"	26214400	104857600	GSI

I would do something like this:

$date= Get-Date -uformat "%Y%m%d"
$expdate="20110930"
$reqno="00034"
$sysowner="GSI"
$esxhost="esxhost1.test.com"
$datastore="DATASTORE_1"
 
Import-CSV C:\PowerShell\serverlist.csv | Foreach {
 New-VM -vmhost $esxhost -Name $_.vmname -Template $_.templatename -Datastore $datastore -DiskStorageFormat thick -Location $_folder
 New-Harddisk -vm $_.vmname -CapacityKB $_.datadrive1
 New-Harddisk -vm $_.vmname -CapacityKB $_.datadrive2
 Set-VM -VM $_.vmname -MemoryMB $_.mem -NumCPU $_.cpu -Confirm:$false
 get-networkadapter $_.vmname | set-networkadapter -networkname $_.pgname -Confirm:$false
 Get-VM -Name $_.vmname | Set-CustomField -Name "Creation Date" -Value $date -Confirm:$false
 Get-VM -Name $_.vmname | Set-CustomField -Name "Expiration Date" -Value $expdate -Confirm:$false
 Get-VM -Name $_.vmname | Set-CustomField -Name "Request Number" -Value $reqno -Confirm:$false
 Get-VM -Name $_.vmname | Set-CustomField -Name "System Owner" -Value $sysowner -Confirm:$false
}

[BrentCocjhran]

Alan - it looks so easy when you put it that way! 

That's exactly the trick I needed and it's working great.  The only thing I really had to change was removing all the quotation marks from the cells.  Apparently the cell itself dictates the contents of the variable.

Thanks again Alan!  With a little work protecting the cells and populating the correct data, our deployments will get a whole lot faster.

[alanrenouf]

No problem, glad to help !