Merging Excel files using PowerShell, yes it can be done.

Merging Excel files using PowerShell, yes it can be done.

Have you ever worked with Excel files where you wanted to match and compare Columns/Rows? In the past, this has been quite difficult tasks to achieve using the native commands within PowerShell.

So have you heard of the PowerShell module ImportExcel?

It’s is a PowerShell module that is available on the PowerShell Gallery and introduces a number of functions that allow you to work with Excel files using the good old blue background.

From this module we will be working with the following function;

  • Merge-Worksheet

Syntax
Merge-Worksheet [-Referencefile] [-Differencefile] [[-WorksheetName] ] [-Startrow ]
-Headername [[-OutputFile] ] [[-OutputSheetName] ] [-Property ] [-ExcludeProperty ]
[-Key ] [-KeyFontColor ] [-ChangeBackgroundColor ] [-DeleteBackgroundColor ]
[-AddBackgroundColor ] [-HideEqual] [-Passthru] [-Show] [-WhatIf] [-Confirm] []

Example usage of Function

The below shows the Reference and Difference Excel files that are being used in this example. I am going to merge the two excel files based on Column A the EmployeeNumber. During my testing, I have had issues using -HeaderName parameter. In this post I will not be specifying the headings and just modify the output file.


# Variables
$ref = “$env:USERPROFILE\desktop\test\ref.xlsx”
$dif = “$env:USERPROFILE\desktop\test\dif.xlsx”
$out = “$env:USERPROFILE\desktop\test\out.xlsx”

# Script Block
Merge-Worksheet -Referencefile $ref -Differencefile $dif -OutputFile $out -WorksheetName Sheet1 -Startrow 1 -OutputSheetName Sheet1 -NoHeader

As we can see from below, the output field has organised Column A and aligned the rows

Every useful if you are working with Excel files but only annoying thing is the HeaderName parameter not working.

Regards
The Author – Blogabout.Cloud


Counting Exchange/Exchange Online Mailboxes with a specified SMTP Domain

Counting Exchange/Exchange Online Mailboxes with a specified SMTP Domain

When working with large organisations that have multiple SMTP Domains, you may run into a requirement where you need to know. How many mailboxes have blogabout.cloud as their PrimarySMTPAddress or have blogabout.cloud listed as their EmailAddress.

Using the below PowerShell snippet you can find out exactly

# Primary SMTP Address
get-mailbox -resultsize unlimited | where {$_.primarysmtpaddress -like "*@blogabout.cloud"} | Measure-Object


# Email Address
get-mailbox -resultsize unlimited | where {$_.emailaddress -like "*@blogabout.cloud"} | Measure-Object

Regards,

The Author – Blogabout.Cloud