CODEWITHSUNDEEP
excelazurepowershell
wolfmaster74
wolfmaster74

Reputation: 17

Parse Excel variables into powershell

I am trying to create a powershell that will grab the varibles from an excel sheet and then add them to the powersehll command.

in the excel sheet i have 3 columns i am interested in the data from (Name , resourcegroup, location) And then for each line with this i want it to parse into into the varible field for the powershell I have created the powershell to do what i need but it would be better if it could loop through and pull this as I am just running the command again with different machine info manually added from the excel.

With @Theo Help

I am working with this version of the script now

Import-Csv -Path 'c:\scripts\vmtest.csv' | ForEach-Object {
# combine the VMName with suffix '-Snapshot'
$snapshotName = $vm.name + "-Snapshot" 
$SnapshotStorage = "Azure-Snapshots" 


$vm = Get-AzVM -ResourceGroupName $_.ResourceGroup -Name $_.Name

# using splatting for better readability
$configParams = @{
    SourceUri    = $vm.StorageProfile.OsDisk.ManagedDisk.Id
    Location     = $_.location
    CreateOption = 'copy'
}
$snapshot = New-AzSnapshotConfig @configParams

New-AzSnapshot -Snapshot $snapshot -SnapshotName $snapshotname -ResourceGroupName $snapshotstorage
}

Upvotes: 0

Views: 726

Answers (2)

Theo
Theo

Reputation: 61228

If as you have commented, you now have the data stored in a CSV file that might look something like this:

 Name,ResourceGroup,Location
 PRD-ITM001,SJAVIRTUALMACHINES,uksouth
 TST-GRSSQL001,SJAVIRTUALMACHINES,uksouth

it has become very simple to import that data and loop through the records like below:

Import-Csv -Path 'c:\scripts\vmtest.csv' | ForEach-Object {
    # combine the VMName with suffix '-Snapshot'
    $snapshotName    = '{0}-Snapshot' -f $_.Name 
    $SnapshotStorage = "Azure-Snapshots" 

    $vm = Get-AzVM -ResourceGroupName $_.ResourceGroup -Name $_.Name

    # using splatting for better readability
    $configParams = @{
        SourceUri    = $vm.StorageProfile.OsDisk.ManagedDisk.Id
        Location     = $_.Location
        CreateOption = 'copy'
    }
    $snapshot = New-AzSnapshotConfig @configParams

    New-AzSnapshot -Snapshot $snapshot -SnapshotName $snapshotName -ResourceGroupName $_.ResourceGroup
}

Note that the above code assumes your CSV uses the (default) comma as delimiter character. If in your case this is some other character, append parameter -Delimiter followed by the character the csv uses.

  • Inside a ForEach-Object {..} loop, the $_ automatic variable references the current record from the csv
  • I used Splatting for better readability of the code. This helps on cmdlets that take a long list of parameters and eliminates the use of the backtick.

Upvotes: 1

VenkateshDodda
VenkateshDodda

Reputation: 5546

Based on the above shared requirement, we understood that you want to pull the values of ResourceGroupName, VMName from the excel sheet & also you want to use those values in the script further.

  • Using PSExcel Module, We have written the below PowerShell Script which will pull the ResourceGroupName, VMName from excel & it will run Get-AzVM Cmdlet.
  • Before running the below PowerShell script , run the below cmdlet Save-Azcontext cmdlet it will saves the current authentication information for use in other PowerShell sessions.
   Connect-AzAccount
   Save-AzContext -Path C:\test.json

Here is the PowerShell script:

$currentDir = "C:\Program Files\WindowsPowerShell\Modules" ##pass the path of the PSexcel Module 
    Import-Module $currentDir"\PSExcel"  
    Import-AzContext -Path C:\test.json ##passing the azcontext file path which was saved earlier
    $ExcelFile = "Give here the path of the current folder where scripts are stored"
    $objExcel = New-Excel -Path $ExcelFile  
    $WorkBook = $objExcel|Get-Workbook  
    
    ForEach($Worksheet in @($Workbook.Worksheets)){ 
     
     $totalNoOfRecords = $Worksheet.Dimension.Rows  
     $totalNoOfItems = $totalNoOfRecords-1
     # Declare the starting positions first row and column names 
     $rowNo,$colResourceGroupName = 1,1  
     $rowNo,$colVMName = 1,2  
     if ($totalNoOfRecords -gt 1){
        #Loop to get values from excel file  
        for($i=1;$i -le ($totalNoOfRecords-1);$i++){
            $ResourceGroupName=$Worksheet.Cells.Item($rowNo+$i,$colResourceGroupName).Value
            $VMName=$Worksheet.Cells.Item($rowNo+$i,$colVMName).Value
            Get-AzVM -ResourceGroupName $ResourceGroupName -Name $VMName |select -Property Name,ResourceGroupName,Location
        }    
     }
    }

Here is the sample output for reference:

enter image description here

For more information ,you refer this blog post on How to Read excel file using PSExcel Module in PowerShell.

Upvotes: 0

Related Questions