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
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.

The Author – Blogabout.Cloud