Yellow Bricks

by Duncan Epping

Powershell and importing .CSV files

· ·

I’ve been playing around with powershell yesterday. We needed to create over 100 VM’s and there’s no point in doing that all by hand. The customer provided us with a .csv file that contained specific info on these VM’s. It took me a while to figure out how to read the info.csv file and how to actually use it. But as always it’s actually fairly simple and that’s why I decided to write it down:

Before we even start, the CSV should be formatted as follows:

vmname,cluster
VM001,HA-DRS-Yellow-Bricks

Read the complete csv file into a variable:

$csv_info = Import-Csv c:\scripts\info.csv

For every line in the csv variable do something:

foreach ($line in $csv_info) {
write-host " This is virtual machine $($line.vmname) on cluster $($line.cluster)"
}

This line would print something like this:

This is virtual machine VM001 on cluster HA-DRS-Yellow-Bricks

As you noticed in the example above we used $line.vmname to get the name of the VM printed and $line.cluster for the cluster name, cool huh! BTW, Alan’s quick reference guide really helped me out!

Reader Interactions

Comments

  2. Rob Mokkink says

    I have created a powershell that can do all that a long time ago, please see the following doc:
    http://communities.vmware.com/docs/DOC-6972

    I can deploy vm’s on cluster, resource pool or single esx host.
    The script is a specific to my environment, but you can change it very easy.

    Soon i will place an updated version, because i edited the script recently.

  3. Duncan says

    Yes my script does Stuart, but I’m not allowed to post the complete script. However as Rob states he created an awesome script that does about the same.

    @Rob , where’s the fun in copy + paste? 🙂

  4. Rob Mokkink says

    @Duncan

    I was just promoting my script. I am working on it right now, i added some new features and even better error handling. Soon i will have linux support to.

  5. Paul says

    Thank you for this, it was the exact code I needed to get the data out of my CSV and into a useable state. 😀

  6. raf says

    Is it possible to sellect only one system(VMname) from the list(.csv file) and create the corresponding line(cluster) ?

    like if line.VMname = VM005

  7. Totie Bash says

    Thanks for the tip, I was working on PowerCLI script the complies with disa stig and the hardening guide and this gave me the missing piece. Here it is in summary:

    I created d:\vmware stig\stig_vm.txt file with the following input taken from DISA stig:
    isolation.bios.bbs.disable,TRUE
    isolation.device.connectable.disable,TRUE
    isolation.monitor.control.disable,TRUE
    isolation.tools.diskShrink.disable,TRUE
    isolation.tools.diskWiper.disable,TRUE
    log.keepOld,10
    log.rotateSize,100000
    RemoteDisplay.maxConnections,1
    tools.guestlib.enableHostInfo,FALSE
    tools.setInfo.sizeLimit,1048576
    vmci0.unrestricted,FALSE
    isolation.tools.hgfsServerSet.disable,TRUE
    isolation.device.edit.disable,TRUE
    isolation.tools.autoInstall.disable,TRUE
    isolation.tools.copy.disable,TRUE
    isolation.tools.dnd.disable,FALSE
    isolation.tools.setGUIOptions.enable,FALSE
    isolation.tools.paste.disable,TRUE
    isolation.tools.ghi.autologon.disable,TRUE
    isolation.bios.bbs.disable,TRUE
    isolation.tools.getCreds.disable,TRUE
    isolation.tools.ghi.launchmenu.change,TRUE
    isolation.tools.memSchedFakeSampleStats.disable,TRUE
    isolation.tools.ghi.protocolhandler.info.disable,TRUE
    isolation.ghi.host.shellAction.disable,TRUE
    isolation.tools.dispTopoRequest.disable,TRUE
    isolation.tools.trashFolderState.disable,TRUE
    isolation.tools.ghi.trayicon.disable,TRUE
    isolation.tools.unity.disable,TRUE
    isolation.tools.unityInterlockOperation.disable,TRUE
    isolation.tools.unity.push.update.disable,TRUE
    isolation.tools.unity.taskbar.disable,TRUE
    isolation.tools.unityActive.disable,TRUE
    isolation.tools.unity.windowContents.disable,TRUE
    isolation.tools.vmxDnDVersionGet.disable,TRUE
    isolation.tools.guestDnDVersionSet.disable,TRUE
    isolation.tools.vixMessage.disable,TRUE
    tools.setinfo.sizeLimit,1048576

    $stig_vm = Import-Csv ‘D:\VMWARE STIG\stig_vm.txt’ -Header Name,Value

    ::APPLY TO JUST MY_VM1
    foreach ($line in $stig_vm) {
    New-AdvancedSetting -Entity MY_VM1 -Name ($line.Name) -value ($line.value) -Force -Confirm:$false | Select Entity, Name, Value
    }

    ::APPLY TO ALL VM
    foreach ($line in $stig_vm) {
    Get-VM | New-AdvancedSetting -Name ($line.Name) -value ($line.value) -Force -Confirm:$false | Select Entity, Name, Value | Export-Csv $output
    }

    THANKS AGAIN!!

  8. Zak says

    I have 2 questions
    Is there a way to call a certain column the same way you can call a row by using: $($line.vmname)?

    vmname,cluster, field3
    VM001,HA-DRS-Yellow-Bricks, test1
    VM002,HA-DRS-Yellow-Bricks2, test2
    VM003,HA-DRS-Yellow-Bricks3, test3
    VM004,HA-DRS-Yellow-Bricks4, test 4

    For instance:
    foreach ($Column in $csv_info) {
    write-host ” This is virtual machine $($line.VM00(n+1)) on cluster $($line.cluster)”
    }

    In other words, I want to specify which row (VM00X) and ask for data in a column “Cluster” or “Field3”

    Is there a way to substitute the name of csv with a wildcard? Such as:

    $csv_info = Import-Csv c:\scripts\$($line.vmname).csv

    I am looking to bulk add a few hundred distribution groups and add members, so far all I can find is 2 separate scripts. I am trying to combine them. I would like