Import Data from Excel Sheet

[dsheltzel]

I currently script the creation of VM's. What I would love to do is have the script "query" an excel spreadsheet that has a list of VM Hostanmes and IP addresses and have it fill in the script for me. This is a sample of the script I use to add VM's -

# Vm Details
$destination_host = "VM_HOST_NAME"
$template_name = "TEMPLATE_NAME"
$datastore_name = "DATASTORE_NAME"
$customization = "CUSTOMIZE_SCRIPT"
$network_name = "NETWORK_NAME"
$resourcepool_name = "RESOURCE_NAME"
$folder_name = "FOLDER_NAME"

# Name the VMs in this array
$array = "SERVER_1", "SERVER_2", "SERVER_3", "SERVER_4"
$iparray = "192.168.1.1", "192.168.1.2", "192.168.1.3", "192.168.1.4"
$a= 0

foreach ($vm in $array)
{
        Get-OSCustomizationSpec $customization | Get-OSCustomizationNicMapping | Set-OSCustomizationNicMapping -IpMode UseStaticIP -IpAddress $IParray[$a] -SubnetMask 255.255.255.0 -DefaultGateway 192.168.1.0
      $vm=New-VM -Name $vm -Template $template_name -Host $destination_host -Datastore $datastore_name -ResourcePool $resourcepool_name -Location $folder_name -OSCustomizationSpec $customization -Confirm:$false
        $a = $a + 1
}

What I would love for it to do is pull the Arrays for the Server names and IP's from an excel spreadsheet and autopopulate it for me correctly and in the correct format. This is such a time saver as we can create 100's a VM's a day for testing. It would help speed things up tremendously.

Is there such a way or can someone point me to the right place to look for this?

Thanks!

[LucD]

Try this

#Import VM Info from CSV File
Import-Csv .\vm.csv | %{
    Get-OSCustomizationSpec $_.customization | Get-OSCustomizationNicMapping |
    Set-OSCustomizationNicMapping -IpMode UseStaticIP -IpAddress $_.IP -SubnetMask 255.255.255.0 -DefaultGateway 192.168.10.1
     $vm=New-VM -Name $_.vm_name -Template $_.template_name -Host  $_.destination_host -Datastore $_.datastore_name -ResourcePool  $_.resourcepool_name -Location $_.folder_name -NetworkName  $_.network_name -MemoryMB $_.memory -NumCPU $_.cpu -OSCustomizationSpec  $_.customization -Confirm:$false
}

[dsheltzel]

Hi LucD,

I appreciate all of your help, but I decided to take a different approach and found a good script on MCPGuides. I have it all setup with my CSV - I chose this because I like the verbose output as they are building.

But when I run the script, I get an error. Below is my script, CSV and the error I am receiving. Any help would be great!

function LoadSnapin{
    param($PSSnapinName)
    if (!(Get-PSSnapin | where {$_.Name -eq $PSSnapinName})){
    Add-pssnapin -name $PSSnapinName
    }
    }
    LoadSnapin -PSSnapinName "VMware.VimAutomation.Core"

    'Reading CSV file'
    $vms = Import-CSV C:\Scripts\NewVMs\vm.csv

    'Reading the contents of the CSV, and for each line, execute the following code'
    foreach ($vm in $vms){
    'Declaring variables that correspond to the column names in the CSV'
    $VMName = $vm.name
    $VMHost = Get-VMHost $vm.host
    $Datastore = Get-Datastore $vm.datastore
    $Template = Get-Template $vm.template
    $Customization = $vm.customization
    $IPAddress = $vm.ipaddress
    $Subnetmask = $vm.subnetmask
    $DefaultGW = $vm.defaultgw
    $DNS = $vm.dns
    $Resource = $vm.resource
    $Location = $vm.folder
    $Network = $vm.network
    $Memory = $vm.memory
    $CPU = $vm.cpu
    $Guest = $vm.guest
   
    'Modifying the customization file with the network information you specified in the CSV'
    Get-OSCustomizationSpec $Customization | Get-OSCustomizationNicMapping | Set-OSCustomizationNicMapping -IpMode UseStaticIp -IpAddress $IPAddress -SubnetMask $Subnetmask -DNS $DNS -DefaultGateway $DefaultGW
   
    'Deploying a new VM from the template you specified in the CSV'
    New-VM -Name $VMName -OSCustomizationSpec $Customization -Template $Template -VMHost $VMHost -Datastore $Datastore -ResourcePool $Resource -Location $Location -NetworkName $Network -MemoryMB $Memory -NumCPU $CPU -GuestID $Guest -RunAsync
   
    'Powering up the newly created VM, to allow the guest customization to complete'
    Start-Sleep -s 10
    Start-VM -VM $VMName -RunAsync
    }

[LucD]

The New-VM cmdlet has 4 parametersets. Notice the 4 distinct blocks in the Syntax section.

You can't mix parameters from different parametersets.

In your case that would be for example the OSCustomizationSpec parameter together with the NumCpu parameter.

[dsheltzel]

So will this work, by setting the Set-VM after the New-VM is created? I cannot figure out where to run the -NetworkName

function LoadSnapin{
    param($PSSnapinName)
    if (!(Get-PSSnapin | where {$_.Name -eq $PSSnapinName})){
    Add-pssnapin -name $PSSnapinName
    }
    }
    LoadSnapin -PSSnapinName "VMware.VimAutomation.Core"

    'Reading CSV file'
    $vms = Import-CSV C:\Scripts\NewVMs\vm.csv

    'Reading the contents of the CSV, and for each line, execute the following code'
    foreach ($vm in $vms){
    'Declaring variables that correspond to the column names in the CSV'
    $VMName = $vm.name
    $VMHost = Get-VMHost $vm.host
    $Datastore = Get-Datastore $vm.datastore
    $Template = Get-Template $vm.template
    $Customization = $vm.customization
    $IPAddress = $vm.ipaddress
    $Subnetmask = $vm.subnetmask
    $DefaultGW = $vm.defaultgw
    $DNS = $vm.dns
    $Resource = $vm.resource
    $Location = $vm.folder
    $Network = $vm.network
    $Memory = $vm.memory
    $CPU = $vm.cpu
    $Guest = $vm.guest
   
    'Modifying the customization file with the network information you specified in the CSV'
     Get-OSCustomizationSpec $Customization | Get-OSCustomizationNicMapping |  Set-OSCustomizationNicMapping -IpMode UseStaticIp -IpAddress $IPAddress  -SubnetMask $Subnetmask -DNS $DNS -DefaultGateway $DefaultGW
   
    'Deploying a new VM from the template you specified in the CSV'
     New-VM -Name $VMName -OSCustomizationSpec $Customization -Template  $Template -VMHost $VMHost -Datastore $Datastore -ResourcePool $Resource  -Location $Location -RunAsync
    Set-VM -NetworkName $Network -MemoryMB $Memory -NumCPU $CPU -GuestID $Guest
   
    'Powering up the newly created VM, to allow the guest customization to complete'
    Start-Sleep -s 10
    Start-VM -VM $VMName -RunAsync
    }

[LucD]

Yes, that is the way to go, use the Set-VM after the New-VM.

You can use the Set-NetworkAdapter to change the portgroup to which the VM's NIC should be connected.

[dsheltzel]

Awesome. Thanks for your help!  The only part that I am real unclear of is running the "-RunAsync" command. Do I run this after each command, or just once at the end.

Right now I have it after the Commands: New-VM, Set-VM, Set-NetworkAdapter and Start-VM

Do I need it listed that many times?

[LucD]

The RunAsync will start the task in the background, meaning the cmdlet returns practically immediatly.

This will improve the total execution time of your script, since several tasks can run in parallel in the background.

The diadvantage is that you not use the pipeline (|) between cmdlets, since a cmdlet that runs with the RunAsync parameter will not return the same object as it normally does.

If you have cmdlets that depend on the finish of a previous cmdlet (for example the Set-VM after a New-VM) you will have to take care of the synchronisation in the script. Your script will become a bit more complex.

An example can be found in my About Async tasks, the Get-Task cmdlet and a hash table post.

[dsheltzel]

You always keep me on my toes...back to the drawing board again! :smileyconfused:

[dsheltzel]

I am beating the crud out of myself figuring this out. If I could go back 10 years, I would be a coder not a Windows Admin!

[LucD]

I would suggest to start without the RunAsync.

Make sure the deployment logic, driven by the contents of the CSV file, works.

Then perhaps start looking at optimalization techniques.

[dsheltzel]

I am finally able to start testing this and have been for a few days in my test environment to no avail. I am getting some errors in the script that I cannot track down. It is creating the VM and starting it up, but the errors are attached.

Here is the script, and a picture of my CSV file as well. Thanks for any help you can offer - I know the IP's are funky, but I am just testing

function LoadSnapin{
    param($PSSnapinName)
    if (!(Get-PSSnapin | where {$_.Name -eq $PSSnapinName})){
    Add-pssnapin -name $PSSnapinName
    }
    }
    LoadSnapin -PSSnapinName "VMware.VimAutomation.Core"

    '**************************************************************'
    '**                   Importing CSV file...                  **'
    '**************************************************************'
   
    $vms = Import-CSV C:\Scripts\NewVMs\vm.csv

    '**************************************************************'
    '** Reading the contents of the CSV, and for each line,      **'
    '** execute the following code...                            **'
    '**************************************************************'
   
    foreach ($vm in $vms){
   
    '**************************************************************'
    '** Declaring variables that correspond to the column names  **'
    '** in the CSV file...                                       **'
    '**************************************************************'
   
    $VMName = $vm.name
    $VMHost = Get-VMHost $vm.host
    $Datastore = Get-Datastore $vm.datastore
    $Template = Get-Template $vm.template
    $Customization = $vm.customization
    $IPAddress = $vm.ipaddress
    $Subnetmask = $vm.subnetmask
    $DefaultGW = $vm.defaultgw
    $DNS = $vm.dns
    #$Resource = $vm.resource - add  -ResourcePool $Resource
    $Location = $vm.folder
    $Network = $vm.network
    $Memory = $vm.memory
    $CPU = $vm.cpu
    $Guest = $vm.guest
   
    '**************************************************************'
    '** Modifying the customization file with the network        **'
    '** information you specified in the CSV...                  **'
    '**************************************************************'
   
    Get-OSCustomizationSpec $Customization | Get-OSCustomizationNicMapping | Set-OSCustomizationNicMapping -IpMode UseStaticIp -IpAddress $IPAddress -SubnetMask $Subnetmask -DNS $DNS -DefaultGateway $DefaultGW
   
    '**************************************************************'
    '** Deploying a new VM from the template you specified in    **'
    '** the CSV file...                                          **'
    '**************************************************************'
   
    New-VM -Name $VMName -OSCustomizationSpec $Customization -Template $Template -VMHost $VMHost -Datastore $Datastore -Location $Location
    Set-VM -MemoryMB $Memory -NumCPU $CPU -GuestID $Guest
    Set-NetworkAdapter -NetworkName $Network
   
    '**************************************************************'
    '** Powering up the newly created VM, to allow the guest     **'
    '** customization to complete...                             **'
    '**************************************************************'
   
    Start-Sleep -s 10
    Start-VM -VM $VMName
    }
   
    '**************************************************************'
    '**                      Ending Script                       **'
    '**************************************************************'

[dsheltzel]

Wouldnt let me attach the 2nd screenshot.

[LucD]

You have to tell the Set-VM and Set-NetworkAdapter cmdlets on which objects you want to perform the changes.

Something like this

$vm = New-VM -Name $VMName -OSCustomizationSpec $Customization -Template $Template -VMHost $VMHost -Datastore $Datastore -Location $Location 
Set-VM -VM $vm -MemoryMB $Memory -NumCPU $CPU -GuestID $Guest
 Get-NetworkAdapter -VM $vm | Set-NetworkAdapter -NetworkName $Network 

[dsheltzel]

That works awesome LucD save for one thing, when running the script, it is prompting me to do the "Set-VM" command. It says "Proceed to configure the following parameters..." Y for yes A for all etc..

Is there way to tell it A for all and not prommtp me with the questions?

Thanks again, this is turning out nicely!

[LucD]

To avoid the confirmation question you can add the -Confirm:$false parameter on the Set-VM cmdlet.

[dsheltzel]

Thanks LucD!

One last piece of the puzzle I am trying to figure out. In my script, the set-vm works fine when setting one CPU. But, in vSphere, I have a place to set "number of Virtual Sockets:" and "Number of cores per socket".

The script reads "-NumCPU"

I cannot find anywhere on the web to read from a CSV for this setting for Number of sockets and number of cores. Do you have any suggestions?

Thanks again for all you do!

[LucD]

I'm afraid you can't do that with the Set-VM cmdlet.

But it's possible using the ReconfigVM method, see Matt's script in cpuid.coresPerSocket