Home > front end >  Parse Excel variables into powershell
Parse Excel variables into powershell

Time:01-12

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
}

CodePudding user response:

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 enter image description here

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

  •  Tags:  
  • Related