beelzhere
Enthusiast
Enthusiast

Converting one cell values in excel to array for AD

Jump to solution

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

0 Kudos
1 Solution

Accepted Solutions
beelzhere
Enthusiast
Enthusiast

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 ?

View solution in original post

0 Kudos
8 Replies
Gidrakos
Hot Shot
Hot Shot

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

}

0 Kudos
LucD
Leadership
Leadership

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.


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

0 Kudos
beelzhere
Enthusiast
Enthusiast

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 ?

View solution in original post

0 Kudos
beelzhere
Enthusiast
Enthusiast

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

0 Kudos
Gidrakos
Hot Shot
Hot Shot

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 Smiley Happy

0 Kudos
beelzhere
Enthusiast
Enthusiast

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

0 Kudos
Gidrakos
Hot Shot
Hot Shot

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

0 Kudos
beelzhere
Enthusiast
Enthusiast

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

0 Kudos