Converting one cell values in excel to array for AD

[beelzhere]

I apologize if this is the wrong place to ask, but I am looking for a way to convert values inside a cell, e.g 1, 2, 3, 4, to an array variable. So that I could use this for adding them in administrators group in a VM . This is what I got so far.

One cell of AGroup in excel contains 12112, 2225, 56689, 32652

    $ar = $vm.AGroup

    for($i = 0; $i -le ($ar.length -1); $i += 1){

    $admin ="Add-LocalGroupMember -Group Administrators -Member $($ar[$i])"

    Invoke-VMScript -VM $VMName -ScriptText $admin -GuestCredential $mycreds

    }

Thank you so much for your help

[Gidrakos]

Powershell has a tool for this called Import-CSV that allows you to take data from any CSV file and work with it.

You can specify which column you'd like to extract from the CSV by using the Select-Object cmdlet.

$myData = Import-CSV .\path-to\file.csv | Select-Object -expandProperty AGroup

foreach ($person in $myData) {

     Do Stuff

}

[LucD]

If this a real XLSX file, and not a CSV, you will need to install something like the ImportExcel module.
That module contains the Import-Excel cmdlet, which allows you to read a XLSX file.

[beelzhere · Accepted solution]

The problem with this is that when I run

$myData = Import-CSV .\path-to\file.csv | Select-Object -expandProperty AGroup

foreach ($person in $myData) {

     $person[0]   # This gives me the answer as '1' instead of 12112

}

Any idea ?

[beelzhere]

It is a CSV file. I am sorry, I should have been more specific.

[Gidrakos]

The [0] isn't necessary, just $person - PowerShell is doing that work for you with that "-expandProperty" since it will just give you the string you're looking for - then the foreach loop does all the array management work for you.

Throw a Write-Host $person in your loop to see what's going on

[beelzhere]

Oh wow, how did I not see that x___x. For some reason I thought it is still showing me the array separated by comma like in Java. Thank you so much

[Gidrakos]

All good! I frequent java and it's simply a different mindset from PowerShell - PowerShell makes things like this super easy.

[beelzhere]

Yea, I see that, it does really make things like this super easy.