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:
#     https://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")

2 Responses to QuerySQL.ps1

  1. Ram says:

    Hi

    Your script is very very useful to me.

    I am getting the error as below, Can you please send me the updated/fix script.

    Exception calling “InvokeMember” with “6” argument(s): “Exception has been thrown by the target of an
    invocation.”
    At C:\RamMiscUtilities\PowerShellScripts\TSQLQuery_To_Excel.ps1:21 char:36
    + { $m.PSBase.GetType().InvokeMember( <<<< $method, [Reflection.BindingFlags]::InvokeMethod, $null, $
    m, $parameters, [System.Globalization.CultureInfo]”en-US”) }

    Thanks in advance

  2. Fitz says:

    Your blog is truncating the right hand side of the script….

    ################################################################################
    # 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 .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:
    # https://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”)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: