Friday, 15 May 2015

Automation Nightmare - Updating Excel Spreadsheet from Powershell

Automation Nightmare - Updating  Excel Spreadsheet from Powershell

The assignment was very simple. All I had to do was to login into SQL Server Management Studio (SSMS), run a query ,  copy and paste the results of the query into an Excel Spreadsheet Template and then save that template using an agreed naming convention. The only problem was that I had to run the query with different parameters and that would mean doing it nearly one hundred times.

So I dutifully began my task and after a few hours I had only extracted the data for about three of the scenarios. The way that I was going about doing the extract would take forever and a day and I did not have that much time.

They say that "If you have a hammer, everything looks like a nail" and so I decided to use my Powershell hammer to nail my data extraction problem, so to speak. As far as I was concerned Powershell was the ideal tool for the job. I could use Powershell to run the queries against the SQL Server database and return the data in a dataset. Once I created the dataset I would then  loop through the data and update each worksheet in the Excel Spreadsheet Template  and then save the spreadsheet in the agreed naming convention.  Life could not be easier.