Manipulating Imported Data

Ever since the Import-CSV command was introduced in PowerShell it has been widely used, can be incredibly powerful, yet can be difficult for beginners to learn.

This is part 2 of a series of posts that will discuss the following:

In the previous part of this post we imported data from a CSV file into an array, this post we will show you how to do basic manipulation of the data, to provide some useful results.

*Note: If you are already a master of the ForEach and For statements and just want more examples on the Import-CSV command, feel free to skip straight to part 3.

Summary of Part 1

In the previous post we imported data from a CSV, it contained the following data

VM,Description,NumCpu

VM1,SQL VM,4

VM2,App VM,2

VM3,VCenter,4

We then

  • Imported the CSV in it’s entirety into an array called $userdata
  • Imported just the VM column into a separate array called $AllVMS
  • Imported just the Description column into another array called $AllDescription
  • Imported just the NumCPU Column into the $AllNumCPU array.

So what can we do now?

Basic data manipulation

Well if we enter this $userdata[0]
We get this returned.
import-csv2_userdata0

And if we enter this $userdata[1]
We get this returned.
import-csv2_userdata1
This is how we return a line of the array, note the array starts at 0 not 1.

So what if we only want one property I hear you ask? Let’s see.
It's as simple as putting the property name after a '.'

So using $userdata[0].VM will result in
import-csv2_userdata0vm

And this $userdata[1].Description will result in
import-csv2_userdata1description

See how much you know now compared to when you started?

We can even use a method like count to work out how many items in the array.
To use methods they follow the variable after a '.'
So $userdata.count will result in
import-csv2_userdatacount

ForEach Statement and Arrays

Probably the most used statements in PowerCLI and definitely the one I use the most is the ForEach Statement.

If you aren’t familiar with it, this is how it works.

Let’s start with syntax.

ForEach($entry in $userdata){#<do this>}

or in a script, good coding practice would dictate it look more like this

ForEach($row in $userdata){
    #<do this>
}

First comes the ForEach statement itself, followed by parenthesis which declares where you want to get the data. In this case $row is created as part of the ForEach statement, it can be called whatever you like, but will be used in the ForEach statement as the value of the current entry having information run against it.
$userdata is obviously our array.
Then comes the curly brackets {}.
Inside the curly brackets comes all the commands we want to run against each line of the array.
So to return some simple data we could run

ForEach($row in $userdata){ 
    write-host $row.VM  “has” $row.NumCPU “vCPUs and is a“ $row.Description
}

and we would get
import-csv2_formatted

So this is the baic use of a ForEach Statement. In the next part of the series we will use the ForEach statement to do some really useful things in PowerCLI using the information from the CSV

For Statement and Arrays

A for statement is another way to deal with data in an array.

See the basic statement below, it returns each row of our example.

for ($i=0; $i -lt $userdata.count; $i++){
    echo $userdata[$i]
}

The for statement has three separate arguments in the parenthesis.

The first is a variable that has an originating value of the first argument, in our case we want the first in the array which starts at 0 so $i starts at 0.

The second is a boolean condition (true or false), if true the commands in the {} will run until the statement is false.

The third is a repeat, almost always when dealing with arrays, this will be the variable followed by ++, which means increment by one.

This statement will echo each line of the array from 0 to 2($userdata.count will be 3 as there are 3 lines. The –lt means less than)

If we run the code we will get the following
basicFor

This is particularly good for doing things like returning the first 10 records, (obviously this is for bigger data sets than our small example CSV)

for ($i=0; $i –lt 11; $i++){
echo $userdata[$i]}

or say returning items 15-50

for ($i=15; $i –lt 51; $i++){
    echo $userdata[$i]
}

Now you have a taste for making use of array data in ForEach and For statements you are all set for some real life scenarios in part 3.

Part 3 – User Scenarios – coming soon