VMware Cloud Community
tianhailong666
Enthusiast
Enthusiast

how to alternate read vaule from Excel file and auto pick up data, how to generate log file after deploy vm.

Hi Guys,

I am writing a Script to batch deploy vm from spec XLS file  which pre-manual input parmeter .

below is my script , I want to achive read value from XLS and auto read next data use (while statement). but it seems doesn't work, Could somebady can resolve my issue. also how to generate a log after deploy VM.?

I appreciated you can help me on  that.!

Param([string]$file)

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $False

$workbook = $excel.Workbooks.Open($file)

$i = 4

$sheet = $workbook.Worksheets.Item("PC服务器虚拟机")

$project = $sheet.Cells.Item($i,1).text

$Name = $sheet.Cells.Item($i,3).text

$Temp = $sheet.Cells.Item($i,5).text

$NumCpu = $sheet.Cells.Item($i,6).text

$NumMem = $sheet.Cells.Item($i,7).text

$SizeDisk = $sheet.Cells.Item($i,8).text

$Clustername = $sheet.Cells.Item($i,13).text

$ESXi = $sheet.Cells.Item($i,14).text

$Vlan = $sheet.Cells.Item($i,17).text

$excel.Workbooks.Close()

$excel.Quit()

<#$NumCpus = $NumCpu -replace "[^0-9]"

$NumMems = $NumMem -replace "[^0-9]"

$SizeDisks = $SizeDisk -replace "[^0-9]#>

$value = @{"$project" = "$sheet.Cells.Item($i,1).text";

"$Name" = "$sheet.Cells.Item($i,3).text";

"$Temp" = "$sheet.Cells.Item($i,5).text";

"$NumCpu" = "$sheet.Cells.Item($i,6).text";

"$NumMem" = "$sheet.Cells.Item($i,7).text";

"$SizeDisk" = "$sheet.Cells.Item($i,8).text";

"$Clustername" = "$sheet.Cells.Item($i,13).text";

"$ESXi"  = "$sheet.Cells.Item($i,14).text";

"$Vlan" = "$sheet.Cells.Item($i,17).text"}

$vms = @()

while ( "$Name" -ne "" )

 

{

  $i++

  $vms += $value

  }

 

Foreach( $myvm in $vms) {

$Template = Get-Template $Temp

  

$VMHost = Get-VMHost $ESXi

$Folder = Get-folder $project

$Cluster = Get-Cluster $Clustername

#$Datastore = Get-Datastore  $store

  

#$OSCustomization = Get-OSCustomizationSpec $vm.Customization

if(-Not(Get-Template -name $Temp)) {

    $show = "Template is not exist, please input a valid template name"

   Write-Host $show.toUpper() -ForegroundColor  red -BackgroundColor yellow

  exit 1

}

elseif (Get-VM -Name $Name) {

    $display = "VM $Name is already exist, please input a different name"

  Write-Host $display.toUpper()  -ForegroundColor red -BackgroundColor yellow

  exit 1

}

else {

    New-VM -vmhost $VMHost -Name $Name -Template $Template -ResourcePool  $Cluster  -Location $Folder -RunAsync

  $checkVM = $null

while ($checkVM -eq $null){

      $checkVM = get-vm $Name -erroraction silentlycontinue | get-networkadapter

      sleep 5

  }

  Set-VM -vm $Name -Numcpu $NumCpus -MemoryGB $NumMems -RunAsync -Confirm:$false

  #$disk = Get-VM $Name | Get-HardDisk | ? {$_.Name -eq "Hard disk 1"}

    #Set-HardDisk -harddisk $disk -CapacityGB $SizeDisks -Confirm:$false

  $vmnet = Get-VM $Name | Get-NetworkAdapter | where { $_.Name -eq "Network Adapter 1" }

    $vmnet | Set-NetworkAdapter -NetworkName $Vlan -StartConnected:$true -Confirm:$false

}

}

<# Writes Jobs in to log file

        $Date = Get-Date

        $logfile = "C:\scripts\log\autobuild" + "_" + $Date.Day + "-" + $Date.Month + "-" + $Date.Year + ".txt"

        if (-not (test-path c:\scripts\log\))

            {

            MD c:\scripts\log | Out-Null

            }          

        Receive-Job * | Out-File -Encoding ASCII -FilePath $logfile -Append

        Remove-Job *

       

        Write-Host "Automated VM build is completed." -ForegroundColor Yellow

        Write-Host ""

        Invoke-Item $logfile

        exit

   

#>

Reply
0 Kudos
1 Reply
LucD
Leadership
Leadership

Have you considered Doug's Import-Excel module?

It avoids all this Cell work, and tremendously simplifies importing a worksheet.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos