How to handle multiple spreadsheets from Excel in Powershell

Background

Most of us working with Powershell has encountered imports and exports of a CSV-file in Powershell, and most of the times this works seamlessly. This is because the Import-CSV function is a great way to get the single Excel sheet imported in to Powershell. However, I got a request to merge several spreadsheets from a xlsx-file and having them exported into one CSV row. My first thought was I should just rename the file from “sheets.xlsx” to “sheet.csv”, but I quickly noticed that CSV-format was unable to handle multiple sheets.

With this information, I was stuck in a crossroad. I could either say that this wasn’t possible, or I could try to find a solution. But the thing that I enjoy by far the most with Powershell is to create a solution to a problem that requires you to create a script that requires logic and that has been thought through. This is why this was a no brainer, and why I really enjoyed coming up with this way to manage .xlsx-sheets. The final result is shown below.

Please read the comment based help for further understanding of the script

After running the script, it should look something like this:

I have tried to make this work in Azure, but without any luck. Most likely because this script uses the ComObject Excel application that comes with the Microsoft Office package required on your workstation or server. If anyone know a way to make this function viable in Azure, feel free to contact me.

If you found this script helpful, have any thoughts, or just want to discuss this, feel free to email me at johan.nilsson@xenit.se , or leave a comment below.

Disclaimer: All information on this blog is offered "as is" with no warranty. It is strongly recommended that you verify all information and validate all scripts in isolated test environments before using them in production environments.