QuerySQL.ps1

################################################################################
#   QuerySQL.ps1                                   # Frank W Hagen - 2008/07/15
#                                                  #   FWHagen.wordpress.com
#     Powershell script to query a SQL database    #   fwhagen.blog@gmail.com
#      and write the output to an Excel file
#
#     Usage:
#     * Create a SQL query file by putting a valid SQL query in a text file in
#       the subdirectory specified in $SQLQueryPath named <$TaskName>.sql
#     * Set configuration variables in config section below
#     * Run at command line:  powershell -nologo .\QuerySQL.ps1
#         OR right-click this script and Open With -> Powershell.EXE
#
#     If you get a security warning running the script, see the following post:
#     http://fwhagen.wordpress.com/2007/10/29/running-local-powershell-scripts/
################################################################################

# Function used for binding to Excel
function Invoke([object]$m, [string]$method, $parameters)
{  $m.PSBase.GetType().InvokeMember($method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters, [System.Globalization.CultureInfo]"en-US")  } 

######################################################
### Configuration information for specific query #####
$TaskName = "AllCompletedWithCosts"     # Title and name of query file
$SqlServer = "SQLServerName";           # SQL Server
$SqlCatalog = "MyDatabase";             # Database name
$WriteOutXML = $False
$WriteOutCSV = $False
$WriteOutXLS = $True
#
# Environment Configuration
$TaskPath = "C:\DEV\_sql\Reports\"          # Root Directory for creating reports
$SQLQueryPath = "SQLQueries\"               # Subdirectory for finding the queryfile
######################################################

# Timestamp the output folder and files using ISOdate
$OutPath = ($TaskPath + (Get-Date -Format yyyyMMdd) + "-" + $TaskName + "\")
$OutFileName = ( (Get-Date -Format yyyyMMdd) + "-" + $TaskName )

# Create the output folder                                      #TODO: Fix Call to eliminate verbose results from system
if (!$(test-path ($OutPath)))
{
    New-Item -itemType directory -Name ((Get-Date -Format yyyyMMdd) + "-" + $TaskName) > $null

    if ($(test-path ($OutPath)))
    {
        Write-Host ($OutPath + " Created") -ForegroundColor "darkgreen"
    }
    else
    {
        Write-Host ($OutPath + " FAILED") -ForegroundColor "red"
    }
}

# Get the T-SQL Query from .SQL file
$SqlQuery = Get-Content ($TaskPath + $SQLQueryPath + $TaskName + ".sql")
Write-Host ("Executing Queryfile: " + ($TaskName + ".sql") + " ") -ForegroundColor "darkgreen"
#Write-Host ($SqlQuery) -ForegroundColor "gray"

# Setup SQL Connection (using Integrated Security (your workstation login).  Use standard connection string format for other)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataTable = New-Object System.Data.DataTable

# Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)

Write-Host ($nRecs.ToString() + " Records retrieved.") -ForegroundColor "Blue"
$SqlConnection.Close();

if ($nRecs -gt 0)
{
    # Make copy of successful query in output directory for traceability
    if ($(test-path ($OutPath + $OutFileName + ".sql")))
    {
        del ($OutPath + $OutFileName + ".sql")
    }
    Copy-Item ($TaskPath + $SQLQueryPath + $TaskName + ".sql") -destination ($OutPath + $OutFileName + ".sql")

    # Very simple to export XML
    if($WriteOutXML)
    {
        Write-Host "Creating XML File..." -ForegroundColor "darkgreen"
        if ($(test-path ($OutPath + $OutFileName + ".xml")))
        {
            del ($OutPath + $OutFileName + ".xml")
        }

        $DataSet.Tables[0].WriteXML($OutPath + $OutFileName + ".xml");
    }

    # Very simple to export CSV
    if($WriteOutCSV)
    {
        Write-Host "Creating CSV File..." -ForegroundColor "darkgreen"
        if ($(test-path ($OutPath + $OutFileName + ".csv")))
        {
            del ($OutPath + $OutFileName + ".csv")
        }

        $DataSet.Tables[0] | Export-Csv ($OutPath + $OutFileName + ".csv")
    }

    # Very hard to export XSL - This method writes the data to an object array and pastes the array directly into Excel  (Thanks go to a few sources on the Internet for this method)
    if($WriteOutXLS)
    {
        Write-Host "Creating Excel File..." -ForegroundColor "darkgreen"
        if ($(test-path ($OutPath + $OutFileName + ".xls")))
        {
            del ($OutPath + $OutFileName + ".xls")
        }

        $sheetIndex = 0;
        $oExcel = New-Object -COM Excel.Application
        $oExcel.Visible = $false
        $oBooks = $oExcel.Workbooks
        $oCulture= [System.Globalization.CultureInfo]"en-US"
        $oBook=$oBooks.psbase.gettype().InvokeMember("Add",[Reflection.BindingFlags]::InvokeMethod,$null,$oBooks,$null,$oCulture)
        #$oSheet = $oBook.Worksheets.Item(1)

        $DataTable = $DataSet.Tables[0];
        $nDr = $DataTable.Rows.Count + 1
        $nDc = $DataTable.Columns.Count + 1

        # Create the object array
        $rawData = new-object 'object[,]' $nDr,$nDc  

        # Write the field names in the first row
        for ($col = 0; $col -lt $DataTable.Columns.Count; $col++)
        {
            $rawData[0, $col] = $DataTable.Columns[$col].ColumnName;
        }

        # Copy the dataset to the object array
        for ($col = 0; $col -lt $DataTable.Columns.Count; $col++)
        {
            for ($row = 0; $row -lt $DataTable.Rows.Count; $row++)
            {
                $rawData[($row + 1), $col] = $DataTable.Rows[$row][$col];
            }
        }

        # Calculate the final column letter
        $finalColLetter = "";
        $colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        $colCharsetLen = $colCharset.Length;
        if ($DataTable.Columns.Count -gt $colCharsetLen)
        {
            $finalColLetter = $colCharset.Substring((($DataTable.Columns.Count - 1) / ($colCharsetLen - 1)), 1);
        }
        $finalColLetter += $colCharset.Substring(($DataTable.Columns.Count - 1) % $colCharsetLen, 1);

        ### Export it all to Excel #####
        Write-Host "Writing to Excel..." -ForegroundColor "darkgreen"

        # Create a new Sheet
        $excelSheet = $oBook.Worksheets.Item(1)

        #$excelSheet.name = $DataTable.TableName;               #TODO: Be nice to figure out how to make this work (not critical)

        # Create the entire range on the worksheet and dump the data into it
        $excelRange = "A1:" + $finalColLetter + "" + ($DataTable.Rows.Count + 1)
        $excelSheet.Range($excelRange).FormulaLocal = $rawData;

        # Mark the first row as BOLD                            #TODO: Be nice to figure out how to make this work (not critical)
        #$excelSheet.Rows[1].Font.Bold = $True;
        #$excelSheet.Cells.Item(1,1).Font.Bold = $True;

        # Save the Excel file and we're done
        Invoke $oBook SaveAs ($OutPath + $OutFileName + ".xls") > $null
        Invoke $oBook Close 0 >$null
        $oExcel.Quit()
    }
}

Write-Host ("Complete")