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.
