Saturday, September 1, 2012

Extract worksheets from Excel into separate files with PowerShell

I recently had need to dust off an old VB script I'd written years ago to get worksheets out of Excel files. I've also been curious about doing more with PowerShell, and besides feeling guilty about putting a VB script into use in 2012, it seemed like a really good learning opportunity.

So why not just rewrite the script in .NET? Well, you can definitely do that; in fact, the code would look very similar. However, not everyone is a .NET developer. I wrote the original VB script on a team where we were building C++ DLLs for ETL processing; .NET wasn't part of our code base. I also think there are plenty of IT roles - DevOps, DBAs, Network Admininstrators to name a few - that might find a simple PowerShell tool like this a little easier to use and/or modify for their needs.

So that being said, just copy & paste the code below into an empty .ps1 file, and you should be good to go. To use it, simply execute the following command (should work from command-line, batch file, or managed code):

PowerShell.exe -command "C:\ScriptFile.ps1" -filepath "C:\Spreadsheet.xls" -output_type "csv"

I did run into one problem / issue while writing this script - getting it to run the first time! Thanks to this great article by Scott Hanselman, I found out that there are some very tight Windows security restrictions on PowerShell scripts - particularly the ones you didn't write yourself. After reading his article, it seemed easier for me (and for anyone who wants to use my code) to just post the source code rather than a downloadable script with certificates, at least in this instance. Maybe if I write another PowerShell article I'll give the certificate thing a go.

If you get the error message I got - "The file C:\ScriptFile.ps1 cannot be loaded. The execution of scripts is disabled on this system. Please see "Get-Help about_signing" for more details." - you can enable execution of PowerShell scripts you've created by running the following command 'As Administrator':

PowerShell.exe Set-ExecutionPolicy RemoteSigned

Anyway, here's my script: