sp_BlitzIndex™ ouput to Excel with Powershell

I am impressed with the output from sp_BlitzIndex™ and today I tried to save it to an excel file so that I could pass it on to the developer of the service. When I opened it in Excel and imported it from the csv file it didn’t keep the T-SQL in one column due the commas which bothered me so I decided to use Powershell to output the format to Excel as follows

 

 

Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine

Advertisements

Powershell can read email & insert excel file attachment into a SQL Database

So at our SQL SouthWest User Group session last week we had sessions from Jonathan @fatherjack and Annette @MrsFatherjack on SSRS and SSIS respectively. During Annettes SSIS session a question was asked about reading email attachments and then loading them into a database. No-one had an answer using SSIS but I said it could be done with Powershell . So I have written the following script.

What it does is open an Outlook com object, search for an email with a certain subject and save it in the temp folder and then import it into a SQL database. This needs to be done on a machine with Outlook and Excel installed. It is possible to process the email using EWS in an Exchange environment and other people have written scripts to do so.

It uses two functions Out-Datatable from http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

and Write-Datatable from

http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

The first takes the output from parsing the Excel File and converts it into a datatable object which can then be piped to the second which uses the BulkCopy method. Alternatively if you require it you could add each row of the excel file to an array and then use Invoke-SQLCmd to insert the data row by row.

Here is the script

 

Visit your own User Group – You can find them here
http://www.sqlpass.org/

If you are in the South West UK then come and join our group. Free training and conversation with like minded people once a month and pizza too what could be better!!