Update – Max Trinidad has written a much nicer way to do this (fixing the obvious problems i had!) check it out here:
http://www.maxtblog.com/2014/06/powershell-extracting-sql-server-data-into-excel/

Ive been doing a lot of work with data ending up in Excel recently, so wanted to make a script that made it a bit quicker to get from A to B.

What i managed to come up with is pretty close to what i wanted. there’s one issue i know about that im not sure how to fix, which is to get the dynamic headers, i read from

Get-Member -Membertype property

which puts the headers in Alphabetical order instead of the order they come out of the query. so you either need to make sure the SELECT statements col are in that order, or comment it out.

Im thinking of adding few extra bits onto it like

  • Login, (I work in a multi domain environment so having some logic to choose which login account to use could be handy)
  • Check the connection is valid before running the query, maybe even check the query runs (I have the displeasure of having far to many SQL 2000 boxes still in production, so instead of having to pick them out before i run the query, making it just carry on would be nice)

Any feedback is welcome and suggestions on how to improve it.

$servers = "DEV-SQL1", "DEV-SQL2"

$query =
@'
SELECT compatibility_level ,
 name ,
 page_verify_option_desc ,
 recovery_model_desc
FROM sys.databases
'@

# Open Excel
$xl = new-object -comobject excel.application
$wb = $xl.Workbooks.Add()
$ExcelWorkSheet = $wb.Worksheets.Add()
$xl.Visible = $true
$rowCount = 2

foreach ($s in $servers)
    {
        $results =  invoke-sqlcmd -ServerInstance $s -Query $query
        foreach ($r in $results)
            {
                # set up col and server info
                $ColCount = 1
                $ExcelWorkSheet.Cells.Item($rowCount,$ColCount).Value2 = $s
                $ColCount = 2
                for ($i=1; $i -le $r.ItemArray.Count; $i ++)
                    {
                        $ExcelWorkSheet.Cells.Item($rowCount,$ColCount).Value2 = $r.ItemArray[$i-1]
                        $ColCount ++
                    }
                $rowCount ++
            }

    }

# Add Header to Sheet
$headers = $results | Get-Member -Membertype property
$ExcelWorkSheet.Cells.Item(1,1).Value2 = "Server"
$h = 2

foreach ($header in $headers)
    {
        $ExcelWorkSheet.Cells.Item(1,$h).Value2 = $header.name
        $h ++
    }
## Format Excel Spreadsheet
$listObject = $ExcelWorkSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $ExcelWorkSheet.UsedRange, $null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null)
$listObject.Name = "User Table"
$listObject.TableStyle = "TableStyleLight10"

## auto size cols
$ExcelWorkSheet.UsedRange.Columns.Autofit() | Out-Null
Advertisements