Run SQL against Excel

run_sql_against_excel.ps1

$ErrorActionPreference = 'Stop'

# Create sheet if not exists
Function CreateSheetIfNotExist($Wb, $SheetName)
{
    foreach($Ws In $Wb.Worksheets) {
        If ($SheetName -ieq $Ws.Name) {
            return $Ws
        }
    }

    # TODO add as last sheet
    $sheet = $Wb.Sheets.Add()
    $sheet.Name = $SheetName
    return $sheet
}

Function ExecuteSqlGetData($Connection, $Sql, $Infile, $OutSheet)
{
    $Recordset = New-Object -ComObject ADODB.Recordset
    $Recordset.Open($Sql, $Connection, 1, 1)

    # Clear previous content
    $OutSheet.Range("1:$($OutSheet.Rows.Count)").Clear() | Out-Null
    # Output filed name to first row
    for ($i = 1; $i -le $Recordset.Fields.Count; $i++) {
        $OutSheet.Cells(1, $i).Value = $Recordset.Fields[$i-1].Name
    }
    # Output record data starting at row 2
    $OutSheet.Range("A2").CopyFromRecordset($Recordset) | Out-Null

    $Recordset.Close()
}

function Get-ConnectString($ExcelVersion, $ExcelFile) {
    # The OLEDB provider are 32-bit and 64-bit aware.
    # If only 32-bit provider is installed (when Office is 32-bit),
    # This script must be run using powershell (x86).
    # To list installed OLEDB, see:
    # http://dbadailystuff.com/list-all-ole-db-providers-in-powershell
    If ($Excel.Version -lt 12) {
        return "Provider=Microsoft.Jet.OLEDB.4.0; " + `
            "Data Source=${Infile}; Extended Properties=""Excel 8.0;HDR=Yes;"";"
    } else {
        return "Provider=Microsoft.ACE.OLEDB.12.0; " + `
            "Data Source=${Infile}; Extended Properties=""Excel 12.0;HDR=Yes;"";"
    }
    return
}

# Get files
$ScriptDir = $PSScriptRoot
$Infile = Join-Path -Path $ScriptDir -ChildPath "file.xlsx"
$OutFile = Join-Path -Path $ScriptDir -ChildPath "file_sql_result.xlsx"
$SqlFiles = Get-ChildItem -Path $ScriptDir -Filter "*.sql" | Sort-Object `
    -Property Name

$Excel = New-Object -ComObject Excel.Application
$Excel.Application.Visible = $true
$Excel.Application.DisplayAlerts = $False
$Conn = New-Object -ComObject ADODB.Connection

# TODO remove default Sheet1 at a proper timing
if ( -not (Test-Path $OutFile)) {
    # Create workbook if not exist
    $OutWb = $Excel.Workbooks.Add()
    $OutWb.SaveAs($OutFile)
}
$OutWb = $Excel.Workbooks.Open($OutFile)

try {
    $connString = Get-ConnectString -ExcelVersion $Excel.Version -ExcelFile $Infile
    $Conn.Open($connString)
    foreach ($SqlFile in $SqlFiles) {
        $OutSheetName = $SqlFile.BaseName
        $OutSheet = CreateSheetIfNotExist -Wb $OutWb -SheetName $OutSheetName
        $Sql = Get-Content -Path $SqlFile.FullName -Raw
        ExecuteSqlGetData -Connection $Conn -Sql $Sql -Infile $Infile `
            -OutSheet $OutSheet
    }
}
finally {
    $OutWb.Save()
    # $OutWb.Close()
    $Conn.Close()
    # $Excel.Quit()
}