Reading and Writing from Excel with PowerShell

My company is in the middle of being bought out, so my manager was asked to supply a report of everyone’s email addresses so they could be created on the exchange server in our new company. No problem, only my boss mentioned that they also want to know every email alias, and they all need to be listed in a single cell within the excel file for each user. He already had a report with everyone names and some other various data, but just needed to fill in the one cell with all the aliases. He’s got till the end of the week, so no problem, I have him forward it over to me and let him know I’ll take care of it. About an hour later I’ve got this script tested and getting the job done for me. Notice it takes two passes at trying to get the addresses, if I’m unable to find them properly in the Rackspace SQL tables, I then go at it again using other tables to try and make sure I’ve got as much of the Excel file populated for me. In order to work with Excel in PowerShell, you just create an Excel COM Object and remember to set $objExcel.Visible = $True if you want to watch the script in action.

Import-Module SQLPS

#Enable/Disable debugging by commenting the next line
#$debug = $true
$SQLServer = "SERVER"
If ($debug) {$SQLDatabase = "Inventory_Dev"}
Else {$SQLDatabase = "Inventory"}

$objExcel = New-Object -com Excel.Application
$objExcel.Visible = $True
$filepath = "C:\original.xlsx"
$UserWorkBook = $objExcel.Workbooks.Open($filepath)
$UserWorksheet = $UserWorkBook.Worksheets.Item(1)

#the sheet had a bunch of junk rows
#data doesn't start until row 12
$intRow = 12

Do {
    $EmpID = $UserWorksheet.Cells.Item($intRow, 1).Value()
    #Write-Output $EmpID
    If ($EmpID -gt 0)
    {
        $emailAddresses = ""
        $SQLQuery = "SELECT vRSExMailboxAddresses.name, vRSExMailboxAddresses.address, vRSExMailboxAddresses.replyTo, vRSExMailbox.customID
            FROM vRSExMailboxAddresses INNER JOIN vRSExMailbox ON vRSExMailboxAddresses.name = vRSExMailbox.name
            WHERE vRSExMailbox.customID = $EmpID
            ORDER BY vRSExMailbox.customID, vRSExMailboxAddresses.name, vRSExMailboxAddresses.replyTo DESC"
        #Write-Output $SQLQuery
        $recordSet = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
        ForEach ($record in $recordSet)
        {
            #for each address found, concatinate them into one variable
            $email = $record.address
            $emailAddresses += "$email, "
        }
        If ($emailAddresses.Length -gt 0) {
            #remove the trailing ", " from #emailAddress
            $emailAddresses = $emailAddresses.Substring(0,$emailAddresses.Length-2)
            $UserWorksheet.Cells.Item($intRow, 27) = $emailAddresses
            }
        Else {
            #if not found set cell null for next pass
            $UserWorksheet.Cells.Item($intRow, 27) = $null
            }
    }
    $intRow++
} While ($EmpID)

$intRow = 12

Do {
    $EmpID = $UserWorksheet.Cells.Item($intRow, 1).Value()
    #Write-Output $EmpID
    If ($EmpID -gt 0)
    {
        $cellAA = $UserWorksheet.Cells.Item($intRow, 27).Value()
        #if the cell is null from first pass, try again with this query
        If ($cellAA -eq $null)
        {
            $emailAddresses = ""
            $SQLQuery = "SELECT ADUserAccounts.Employee_ID, vRSExMailboxAddresses.name, vRSExMailboxAddresses.address, vRSExMailboxAddresses.replyTo
                FROM ADUserAccounts INNER JOIN vRSExMailboxAddresses ON ADUserAccounts.UserID = vRSExMailboxAddresses.name
                WHERE (ADUserAccounts.Employee_Type = 'Employee' OR ADUserAccounts.Employee_Type = 'Contractor') AND (ADUserAccounts.Employee_ID = $EmpID)
                ORDER BY vRSExMailboxAddresses.replyTo DESC"
            #Write-Output $SQLQuery
            $recordSet = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
            ForEach ($record in $recordSet)
            {
                $email = $record.address
                $emailAddresses += "$email, "
            }
            If ($emailAddresses.Length -gt 0) {
                $emailAddresses = $emailAddresses.Substring(0,$emailAddresses.Length-2)
                $UserWorksheet.Cells.Item($intRow, 27) = $emailAddresses
                }
            Else {
                #if still not found, set cell to NULL
                $UserWorksheet.Cells.Item($intRow, 27) = "NULL"
                }
        }
    $intRow++
    }
} While ($EmpID)

$UserWorkBook.SaveAs("C:\final.xlsx")
$objExcel.Quit()
Advertisements
This entry was posted in Enterprise Management, Programming and tagged , , , , . Bookmark the permalink.

One Response to Reading and Writing from Excel with PowerShell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s