Monthly Archives: May 2019

This function runs an SQL query and displays results in a new Excel sheet:

function SQLtoXLS {
    param([String]$sql, [String]$server, [String]$db)

    $tbl = Invoke-Sqlcmd -ServerInstance $server -Database $db -Query $sql

    $xl = New-Object -comobject Excel.Application
    $xl.Visible = $False
    $xl.DisplayAlerts = $False

    $wb = $xl.WorkBooks.Add()
    $ws = $wb.Worksheets.Item(1)
    $ws.Name = "SELECT results"

    $r = 0

    if($tbl.Count -gt 0)
    {
        $rowCount = $tbl.Count
        $colCount = $tbl[0].ItemArray.Count

        $grid = New-Object 'string[,]' ($rowCount + 1), $colCount

        $c = 0
        foreach($col in $tbl[0].Table.Columns)
        {
            $grid[$r, $c] = $col.ColumnName
            $c++
        }
        $r++

        foreach($row in $tbl)
        {
            $c = 0
            foreach($i in $row.ItemArray)
            {
                $grid[$r, $c] = [String]$i
                $c++
            }
            $r++
        }

        $ws.Range($ws.Cells.Item(1,1), $ws.Cells.Item($rowCount + 1, $colCount)).Value = $grid
        $ws.Range($ws.Cells.Item(1,1), $ws.Cells.Item(1, $colCount)).Font.FontStyle = "Bold"
    }
    else
    {
        $ws.Cells.Item(1, 1).Value2 = "No results"
    }

    $ws.Columns.AutoFit() | Out-Null
    $ws.Rows.AutoFit() | Out-Null
    $xl.Visible = $True
}

Usage example:

SQLtoXLS -sql "SELECT * FROM sql_table" -server "localhost" -db "my_db"