Powershell Merge Multiple Excel Files To Single File

Recently I had to merge a few excel files. Usually, I would have done the usual copy and past functions to merge them.

Unfortunately, in this situation I had to merge 10 files and the largest file size was 48MB. The copy paste method would have resulted in errors and I would have to do this multiple times.

I decided to see if I could use Powershell to achieve this. After some research I was able to come up with the following script.

The guide is a step by step so that those new to PowerShell will be able to use the script with some minor modifications.

Let’s begin.

1. I prefer using Powershell ISE for this script as its easier to edit and execute. Here is how you open it.
2. You can start Powershell ISE, Windows Start > type “powershell” > right click “Powershell ISE” > Select “Run as Administrator”.

2. A “Windows Powershell ISE” window will open. If you do not see the edit window or “Script Pane” click on the Arrow beside the “script” on the top right corner.

3. The working Powershell ISE window will look like this.

PSExcel is a Powershell module we need to enable Powershell to read Excel ( xlsx) files.

To install PSexcel, Powershell ISE must be Run As Administrator or else an error will be returned.

Type the following in the Powershell Script Pane and press the green play button. You will get a dialog window asking to confirm “Execution Policy Change” click “yes”

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope LocalMachine  Install-Module -Name PSExcel  Import-Module psexcel  Get-module psexcel

Here is a screenshot for your reference.

Once we have the Powershell window open, you now need to download the script and sample files. I have prepared a short script to make this easier. You can download all the files using the script below. This will download and extract the files required.

Copy the contents of this script below and paste it on the Powershell ISE Script Pane.

# This script performs the following  # 1. Checks if the download folder exists, creates if it does not already exist.  # 2. specifies the download file name and location to save file.  # 3. Downloads the file from the bitly URL specified.  # 4. Extracts the zipfile contents.  # ###############################    # specify the folder to download the files.  $downloadfolder="C:Excel-merge-script"  # the following checks if the folder exists. If it does not exist the folder will be created.  If(!(test-path $downloadfolder))  {        New-Item -ItemType Directory -Force -Path $downloadfolder  }    # The following specifies the name and the location the file will be saved in.  $output1 = "$downloadfolderSample-Excel-File-for-merging.zip"  $output2 = "$downloadfolderMerge-excel-files-v1.0.ps1"    # these are the location where the files are stored  $url1 = "https://bit.ly/3xmr1Dc"  $url2 ="https://bit.ly/3CTROaQ"    # the next commands will download the files to the specified destination.    (New-Object System.Net.WebClient).DownloadFile($url1, $output1)  #Sample-Excel-File-for-merging.zip  (New-Object System.Net.WebClient).DownloadFile($url2, $output2)  #Merge-excel-files-v1.0.ps1    # expands the Zipped file "Sample-Excel-File-for-merging.zip"  to the folder specified.    Expand-Archive -Path $output1 -DestinationPath $downloadfolder      # End of script.

This is what it will look like.

We are now ready to execute the script.

From Powershell ISE, click on the “File open” icon, browse to “C:Excel-merge-script” and select the file “Merge-excel-files-v1.0.ps1” and click “Open”

Once open, check if the folder name to ensure it matches the current folder. 

You can execute the script by clicking on the Green play button.

Once the script has completed, you will see 2 new files created.You can browse to view if the new files are created.

Now that you have succesfully executed the script, lets look at the source files.

Here are a few points to take note of.

1. The files have the same columns and in the same sequence. This is critical as the script depends on the column sequence to be the same for all source files.
2. Each file only has one tab with data, this script does not support mulptiple tabs of excel sheets.
3. Each file has the “type” that is different. i.e. “IP Phone”, “Hunt Group” and “Analog Phones”
4. Each file also has different row counts.

Now lets review the merged file “Merged-Excelfile.xlsx”

1. Take note the “type” column now has data from the source files.
2. The number of rows matches the sum of rows of the source files.

As you may have different requirements, lets go thru the parts of the script that you can modify to suit your needs.

There are 3 areas that you will need to change.

1. Declaring the positions of the columns

2. Populating the variables with the data from the source files.

3. Creating a custom object ( i.e. new table) with the populated data

Requirements for this script to work for you.

1. All excel files must only have a single Worksheet with data.
2. All tables must have identical columns in the same sequence

If you hit any errors, please review the scripts and screen shots provided. There could be an accidental edit that could have caused the error.

Powershell will provide the row the error is generated , that is a good starting point to troubleshoot.

Lastly hope this works for you.

If there is interest I will provide more scripts to acheive different objectives using Powershell.