Hi.

I want to learn how to script in Powershell to make my life easier at work.

Now, I'm creating Distribution Lists with cmdlets, and other simple moves, but I'd like understand and use scripts with Excel to make it easier in AD and Exchange.

Can anyone help me find out where I can learn Powershell with those tools? I've searched google and youtube, and bought books, but I'd like to learn especially about AD and Exchange through Excel.

I also have another question, I'm trying to collect the telephonenumbers in one OU in AD, and found this cmdlet:

Get-AdUser -Filter * -Properties OfficePhone | FT OfficePhone,UserPrincipalName

I lined ut the OU path before -Filter, and used * -Properties Telephones Mobile to find the phonenumber in Properties-Telephones-Mobile in Active Directory. But I'm obviously doing something wrong.

Could anyone please help me? 

 

To properly manipulate excel you need to delve quite a bit deeper and start using ODBCs to read and write the data, effectively treating it like a database.

Remember that an excel file can have multiple pages, tables, images, etc. If you just want something "quick and dirty" you're probably better off using CSVs, even if on your end you open them up in excel (for simplicity of manipulating it). What you don't want is to be in a situation where you think a script you have will properly manipulate every excel file thrown at it and then realize it breaks because the file is not in the expected format, someone left a blank column or a blank row at the top or something else like that.

So my advice is if you really want to properly learn how to manipulate excel you'll need to delve into native .NET, I'm sure there are some community created functions for excel out there (I know I have my own) but you really need to start understanding what it does, how and why, specially if you plan on using it with AD and Exchange where a little mistake can be very costly...

Hi Wezcomp,

first of all, you can use Get-Help to learn how to use Cmdlets, by asking them what it is they do:

Get-Help Get-ADUser -Detailed

By Adding the -Detailed Parameter, it will explain each Parameter of Get-ADUser and what it does. Then it will show you examples on how to use the cmdlet. Very useful tool, Get-Help, learning to use it fully ought to be your first step, as this unlocks your access all other aspects of PowerShell. With that tool in hand, you could theoretically do the rest fully autodidactically (would be frustrating though, since you'd like to be able to do something specific right now. Don't worry, we'll gladly help anyone truly trying to learn.).

There are lots of tutorials out there that can be useful in a general way (I added my own here). But regarding Excel:

As Fausto said, CSVs are the simple most way to do this. I'd advice against delving too deep into Excel manipulation right now (lots of frustration, little immediate rewards), Csv is your friend. Notable Commands:

• Import-Csv
• Export-Csv

For example, if you want to create a csv list with user data in it, using your previous example, you can do this:

Get-ADUser -Filter "*" -Properties OfficePhone | Export-Csv "C:\example\users.csv" -NoTypeInformation

By the way: FT (Format-Table) is a treacherous command: It displays objects to the console, but it is a dead end where data is concerned! Only use it when you want to check something quick on the console (like whether you got the right data), before continuing on without using a Format command.

Cheers,
Fred

Thanks a lot for your tips, both of you.

I'll check it out.

Cheers

If I could offer another small advice since you're just learning PowerShell, do not fall into the bad practices of using aliases, this goes for either CmdLets or parameters of a CmdLet/Function.

While they look like they make it easier to read to start with, as you start developing your own advanced functions, importing modules created by users in the community, etc etc, you'll start losing track of what each alias really stands for.

Also, and this is probably even more important, you might create a script today that you leave working perfectly fine, using aliases, and be required to modify it two months down the line and be left scratching your head at what that alias really stands for. Worse even is if someone else needs to look at your code and try to figure out what each of the aliases do.

Your code needs to be accessible to everyone, at any given point in time.

As a small example, because I never use aliases, I couldn't even remember what FT stood for anymore...

Fausto

• Edited by FaustoNascimento 21 hours 18 minutes ago

If I could offer another small advice since you're just learning PowerShell, do not fall into the bad practices of using aliases, this goes for either CmdLets or parameters of a CmdLet/Function.

While they look like they make it easier to read to start with, as you start developing your own advanced functions, importing modules created by users in the community, etc etc, you'll start losing track of what each alias really stands for.

Also, and this is probably even more important, you might create a script today that you leave working perfectly fine, using aliases, and be required to modify it two months down the line and be left scratching your head at what that alias really stands for. Worse even is if someone else needs to look at your code and try to figure out what each of the aliases do.

Your code needs to be accessible to everyone, at any given point in time.

As a small example, because I never use aliases, I couldn't even remember what FT stood for anymore...

Fausto

• Edited by FaustoNascimento Friday, March 27, 2015 9:49 AM

If I could offer another small advice since you're just learning PowerShell, do not fall into the bad practices of using aliases, this goes for either CmdLets or parameters of a CmdLet/Function.

While they look like they make it easier to read to start with, as you start developing your own advanced functions, importing modules created by users in the community, etc etc, you'll start losing track of what each alias really stands for.

Also, and this is probably even more important, you might create a script today that you leave working perfectly fine, using aliases, and be required to modify it two months down the line and be left scratching your head at what that alias really stands for. Worse even is if someone else needs to look at your code and try to figure out what each of the aliases do.

Your code needs to be accessible to everyone, at any given point in time.

As a small example, because I never use aliases, I couldn't even remember what FT stood for anymore...

Fausto

• Edited by FaustoNascimento Friday, March 27, 2015 9:49 AM

Some resources:

#	

Param(
    $excelFile='update_tester.xlsx',
    $csvFile='names.csv'
)

$conBase='Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0;HDR=YES";Persist Security Info=False'
$connectionString=$conBase -f $filename
$conn = new-object System.Data.OleDb.OleDbConnection($connectionString)
$conn.Open()

$cmd = $conn.CreateCommand()
$items=Import-Csv items.csv

# we are going to update phones
foreach ($item in $items) {
    $cmd.CommandText = 'UPDATE [Sheet1$] SET Phone=[{0}] where Name=[{1}]' -f $item.Phone, $item.Name
    $cmd.ExecuteNonQuery()
}

You can also use SELECT, INSERT and DELETE queries.

Here are a number of scripts that update or manipulate Excel easily: http://1drv.ms/1H5S6sS

Great, thanx a million!