Create virtual machines from a spreadsheet and a PowerShell script

A few days ago, I was talking to someone who told me about a user calling him on the phone and immediately spewing a long list of instructions about virtual machines that they needed to create. He said that he told the user that he didn’t have time at the moment, and to send him a list of what was needed. As he told me this, it occurred to me that it would be relatively easy to create a VM from a list — or in this case a spreadsheet. So, let’s take a look at how it’s done.

To create VMs from a spreadsheet, we will need two things — a spreadsheet and a simple PowerShell script to create VMs based on the spreadsheet data.

Create VMs using PowerShell

The PowerShell cmdlet used to create virtual machines is New-VM. If you have ever created VMs from PowerShell, then you know just how complex the process can be. Even so, you can create a simple VM by supplying two parameters – the VM name and the amount of memory that you want to assign to the VM. Since I want to show you how to build a VM from a spreadsheet without getting bogged down by all of the intricacies of the New-VM cmdlet, I am going to keep things simple and use only the VM name and memory amount.

You can see my initial spreadsheet in the screenshot below. As you look at the screenshot, you will notice that there are no column headers. A lot of people like to define the column headers in the CSV file, and that technique certainly has its place. In this case, though, I am defining the column headers in the code rather than in the CSV file. If you were to add column headers, the script would still work but would display an error just before creating the VMs, because the script would interpret the header row as an invalid VM configuration.

Create virtual machines
Once the spreadsheet is complete, the next step in the process is to save it in CSV format. That way, we can configure PowerShell to import the spreadsheet’s contents. You can see what my CSV file looks like in the screenshot below. Notice that the CSV file contains both the header row and the data that will be used to create the virtual machines.

Building the PowerShell script

So now that we have created the spreadsheet, it’s time to begin building the PowerShell script. The first thing that our script needs to be able to do is to read in the CSV file. The PowerShell cmdlet that is used to do that is Import-CSV.

Of course, using the Import-CSV file by itself doesn’t do anything. You have to tell PowerShell which CSV file you want to import, and what you want to do with the data once the file has been imported.

Specifying the name of the CSV file to import is easy. You can append the path and filename directly to the end of the Import-CSV file cmdlet. Making use of the data within the CSV file, however, requires a bit more creativity.

One of the things that I have always liked about PowerShell is its flexibility. There are usually multiple ways to get a particular task done, and you are free to use the method that makes the most sense for you. That concept applies when it comes to extracting data from a CSV file. There are many different ways to get the job done. A lot of people like to create an array for each value found in the file. While there is certainly nothing wrong with that approach, I prefer to create a simple For-EachObject loop. Here is what the basic loop structure looks like:

$List = Import-CSV C:\Scripts\VMs.csv -Header ‘Name’, ‘Memory’
ForEach($VM in $List)
{
<create virtual machine>
}

For right now, I used <create virtual machine> in place of the code that will actually create the VM, because I haven’t shown you how to do that yet. Before we create some VMs, let’s take a look at what we are doing so far.

The first line of code creates a variable named $List. This variable will receive the full contents of the specified CSV file. The -Header switch gives us an opportunity to tell PowerShell what header names are being used.

The second line of code sets up a loop. Since each line in the CSV file will represent a VM that will be created, I am referring to each line with a variable called $VM. Therefore, the loop is looking at each VM ($VM) in the list ($List).

The next thing that I want to do is to capture the list values to some variables. $VM.Name will represent the current virtual machine name and $VM.Memory will represent the memory amount. I am simply mapping these values to variables named $Name and Memory. Here is the code:

$List = Import-CSV C:\Scripts\VMs.csv -Header ‘Name’, ‘Memory’
ForEach($VM in $List)
{
$Name = $VM.Name
$Memory = $VM.Memory
$Name
$Memory
}

When I run this code, it produces the output shown below. As you can see, the code displays the CSV file’s contents.

Create virtual machines
Now the only thing left to do is to add a line that will create the virtual machines. Here is the script with that line added:

$List = Import-CSV C:\Scripts\VMs.csv -Header ‘Name’, ‘Memory’
ForEach($VM in $List)
{
$Name = $VM.Name
$Memory = $VM.Memory
New-VM -Name $Name -MemoryStartupBytes $Memory
}

You will notice that I took out the $Name and $Memory commands since it isn’t necessary to echo those values.

Here is the output generated when I run the script:


The reason why Memory Assigned is currently zero is because the virtual machines are powered off. When the VMs are brought online, the correct amount of memory is shown.

Create virtual machines — and other things — from a spreadsheet

This is a simple example, but it clearly illustrates that you can create virtual machines from a spreadsheet. You could easily expand on this technique to specify additional virtual machine attributes, such as the number of virtual CPUs that are assigned to the VMs.

Featured image: Shutterstock

Leave a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Scroll to Top