I just recetly started very basic scripting in powershell, I'm wondering if anybocy can help me get this task done. 

Basically we get emailed an excel spreadsheet which contains a few columns, one of the columns (with the header "names") contains a list of names (lastname, firstname).  What I need to do is get that list of names from the spreadsheet and compare it to active directory (specifically, the displayname attribute which is also lastname, firstname), and then I need to email any discrepancies. 

Can anybody help me get started with this, or at least point me in the right direction?


Thanks!

You've got three choices.

1 (Easiest): Export the Excel sheet to a CSV file, which Excel does natively. Then, use PowerShell's import-csv cmdlet to import and parse the CSV file.

2 (Harder): If you have Access installed also, then you can use ADO.NET to open the XLS directly as a database, so that you can get to the column you want.

3 (Harder): You can also use Office's COM Automation component to programatically launch Excel (sort of) and read information from the cells you want.

Once you have the names, I'm assuming you just need to see if they exist in AD. I'm not sure exactly what you want to compare, but start by getting Quest's AD Management cmdlets from www.quest.com/powershell (freebie). You would then use the Get-QADUser cmdlet to attempt to retrieve a user based on whatever comparison criteria you're using (first and last name?). If you get an object back, then the user's in AD. If you don't, they're not.

You can use the Excel COM object in PowerShell to save the file as a CSV file:

$xlCSV=6
$xls="c:\test\testdata.xlsx"
$csv="c:\test\mytest.csv"
$xl=New-Object -com "Excel.Application"
$wb=$xl.workbooks.open($xls)
$wb.SaveAs($csv,$xlCSV)
$xl.displayalerts=$False
$xl.quit()

One thing that seems to be a problem though is that even though you quit Excel, the Excel process doesn't really terminate so you may also need

if (ps excel) { kill -name excel}

Thanks that really helps alot.

What exactly does $xlCSV=6 do in this case? 

Ok so here is what I have so far:

$xlCSV=6
$xls="c:\exceltest.xls"
$csv="c:\exceltest.csv"
$xl=New-Object -com "Excel.Application"
$wb=$xl.workbooks.open($xls)
$wb.SaveAs($csv,$xlCSV)
$xl.displayalerts=$False
$xl.quit()

$data=import-csv $csv
$users=getqaduser

Now I'm trying to compare $data.names (the names column in the csv file) to $users.displayname, I tried something just to test that didn't look like it did anything:

$users | foreach-object {if($data -contains $_.displayname)} {$_}}

So how would I compare those two values, and send an email if there's a discrepency?

Thanks

Guys doesnt this seem super complex for a fairly simple task?

So assuming displayname is firstname.lastname or something similar

Save your excel as a csv

$obj = import-csv c:\test.csv
$obj = $obj | fl "firstname", "lastname"
#now you have only first and last names
foreach ($thing in $obj)
   {
         if (dsquery ("$thing.firstname" + "." + "$thing.lastname") -eq $null)
            {
                  Write-host  "$thing.firstname" + "." + "$thing.lastname" "does not exist"
            }

         else
            {
                    write-host "$thing.firstname" + "." + "$thing.lastname" >> c:\usersexist.log
            }
}


untested and not using all objects, but much shorter and sweeter ?

$xlCSV is a constant for the SaveAs method that tells Excel to save the file in the CSV format. Because it is a constant, technically I probably should have used

new-variable xlCSV 6 -option Constant

That's pretty much what I was suggesting. Jeff was showing how to programatically save the XLS to a CSV - it's possible that portion of the process needs to be automated as well.

Thanks for your guys help...I got it working with the following code (the stuff inside the if statements I'll end up changing to do what I need later):

$xlCSV=6
$xls="c:\exceltest.xls"
$csv="c:\exceltest.csv"
$xl=New-Object -com "Excel.Application"
$wb=$xl.workbooks.open($xls)
$wb.SaveAs($csv,$xlCSV)
$xl.displayalerts=$False
$xl.quit()

$data=Import-Csv $csv | Select-Object name
$users=get-qaduser | Select-Object displayname,name

foreach ($user in $users)
{
$match = 0;
foreach ($dataline in $data)
{
if ($dataline.name -eq $user.displayname)
{
"DisplayName: " + $user.displayname + " Name: " + $user.name + " Matched";
$match = 1;
break;
}
}
if ($match -eq 0)
{
$user.displayname + " no match"
}
}

I've run into another problem with this...the excel spreadsheet that I need to compare to AD doesn't convert to csv very well (it creates blank columns so it doesn't import properly)...so now I'm having a hard time figuring out how to read a single column from the spreadsheet in powershell. The column has a header and a bunch of names under it...how would I load those into a variable without saving it as a csv first??

Now you're back to having to use the Excel COM object which is not the easiest thing in the world to work with. I'd still do the export to CSV, import into PowerShell and then use it from there. You should be able to include or exclude the csv fields you want. The fact that some columns are blank shouldn't matter. Just grab the csv column you want by name.

I tried that...but when I run $test=import-csv c:\csvfile.csv (after converting the excel file to csv, either with powershell or manually from save-as) I get this error:

Import-Csv : Cannot process argument because the value of argument "name" is in
valid. Change the value of the "name" argument and run the operation again.
At line:1 char:19

You need an extra command to strip out the blanks:

$csv="c:\test\exported.csv"
cat $csv | % {$_.replace(",,","")} | out-file c:\test\data.csv

Now you can import data.csv and continue on as before.

I keep coming back to this project...I'm having ANOTHER issue with it now.

Because of the way this spreadsheet is setup, cutting out the blank lines the way sapienscripter recommended screws up the whole spreadsheet, so I resorted to just just using excel as a COM object.

Anyway, the spreadsheet has a bunch of columns, and I need to read a few of them and combine them into one variable, I can read one columns and put it in an array, but I need to read two columns and put them into a single variable, and I also need the first row to become the header, currently it turns the first row into just another object.

Here is what I'm doing now (so in this example I need columns 10 and 11 in the varible, and I need row 1 to be the header for both columns):

$employeestrim=@()
$employees=@()
$employeestrim=$ws.columns.item(10).value()
$employees = $employeestrim | ForEach-Object {($_ = $_.trim())}

How about trying to use ADODB to read the spreadsheet as a database?

$objConnection= New-Object -com "ADODB.Connection"
$file="c:\scripts\pirates.xls"
$strQuery="Select * from [Sheet1$]"

$objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;`
Data Source=$file;Extended Properties=Excel 8.0;")

$objRecordSet=$objConnection.Execute($strQuery)

do {

Write-Host $objRecordSet.Fields.Item("UserName").Value
$objRecordSet.MoveNext()

} Until ($objRecordSet.EOF)


$objConnection.Close()

Thanks for the quick response SAPI...I modified your script a little, the one you wrote gave an error in Write-Host $objRecordSet.Fields.Item("UserName").Value , saying "UserName" not found (I changed it to the actual name of the columns and got the same error).

$objConnection= New-Object -com "ADODB.Connection"
$file="c:\ATT1741595.xls"
$strQuery="Select * from [Sheet1$]"
$users=@()
$objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;`
Data Source=$file;Extended Properties=Excel 8.0;")

$objRecordSet=$objConnection.Execute($strQuery)

do {

$users+=$objRecordSet.Fields.Item(9).Value
$objRecordSet.MoveNext()

} Until ($objRecordSet.EOF)


$objConnection.Close()


Column 9 contains the names...now I have the same problem I did before though. I have all the uusername (including the header row, which I want to be a property). How can I also get column 10 into the variable and make the header (description) into another property?

another words...I want to be able to call $user.username or $user.description. (this is part of a much larger script, I"m just having a hard time with this excel thing)

Make sure that the query string is selecting from the name of the worksheet tab. On mine it is Sheet1. Yours might be different. My approach assumes the first row are your properties or fieldnames, and that each row is a recordset. An advantage to this approach is that if you have blank columns, they can be easily ignored. Or is this not the case with your spreadsheet? Are there no defined column definitions? Or do you not know ahead of time what the column heading will be?

Mine is sheet1 also...I know what the column heading are..however they start are row 2 instead of row 1...would that be the reason it doesn't recognize the name?

Yes, that would cause a problem and actually explains all the issues we've had in trying to get this file converted to a CSV, for example.

Actually, is there something else in the first row? I can still read the spreadsheet as a database, even if if the first row is blank.

First row is blank, it just has some blank colored cells. Here is the error I get after I enter this line ("Last Name, First Name" is the header, which is on row 2):

$users+=$objRecordSet.Fields.Item("Last Name, First Name").Value

Exception getting "Item": "Item cannot be found in the collection corresponding
to the requested name or ordinal."
At line:1 char:34
+ $users+=$objRecordSet.Fields.Item( <<<< "Last Name, First Name").Value

Are "last name" and "first name" two different columns? If so, try this:

$users+= $objRecordSet.Fields.Item("Last Name").Value+","+$objRecordSet.Fields.Item("First Name").Value

No that's just one column...I even tried replacing it with another one of the columns that had no comma (like "Job Desc"), same results.

Change the name of it to .xls...it wouldn't let me upload an .xls file.

What version of Excel created this file? I got errors until I removed the first line and then re-saved it.

They created it with Excel 2003.

It appears the problem is that colored first line. Even though there is no text, the formatting must be throwing things off. On my test file, everything works even with an empty row at the top. But as soon as fill one of the first row cells, reading the spreadsheet breaks. The only way I could get it to work was to delete the first row. I could add back a clear empty row but what's the point.

Weird...I guess I'll see if I can get them to start sending them out without the extra line.

SAPI I really appreciate all your help, thanks a lot. I'm sure I'll run into another snag and come back for more help.

Ok one more thing and I think I'll be good to go.

I ended up deleting the first row of the excel file with the script, re-saving it and then reading the columns with the script you gave me. Now how do I make the username an object with multiple properties?

I need to do something like this:


$users.name=$objRecordSet.Fields.Item("Last Name, First Name").Value
$users.description=$objRecordSet.Fields.Item("descripption").Value

What I'll then end up doing (as an example) would be something like:

$users | foreach-object {if ($_.username -match "something") {dosomething}}

I guess I need all those returned cells from excel to be objects in $users, with multiple properties (which I would choose from the excel column).

I hope I'm explaining it right.