ETL With PowerShell (SQL Server to Excel to CSV)

I've been doing a lot of BI work with PowerShell lately and wanted to demonstrate how easy it is to move data around with PowerShell.  The code below queries a table in SQL Server using the free Microsoft provided SQLPS module, loads the data into a newly created Excel spreadsheet and then exports the spreadsheet to a CSV file.

Note the code at the top that checks before loading SQLPS.  When running multiple times, this can save you a lot of time.

#  Check if the SQL module is loaded and if not, load it.
if(-not(Get-Module -name "sqlps"))
    {
      Import-Module "sqlps"
    }

Actually, if my goal was to get the data to a CSV file, I could do that in a lot less commands.  My goal is to highlight how easy it is to use the Excel COM object to leverage the power of the Excel application and to augment that with access to databases such as SQL Server.  The SQLPS module even returns the results in a format consumable by PowerShell.  You could access a different database platform via ODBC and that will be a future blog.  Dare I say that PowerShell can do ETL and may even challenge SSIS?  Hmmm....

#  Check if the SQL module is loaded and if not, load it.
if(-not(Get-Module -name "sqlps"))
    {
      Import-Module "sqlps"
    }

set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\Databases\Adventureworks\Tables

$territoryrs = Invoke-Sqlcmd -Query "SELECT [StateProvinceID],[TaxType],[TaxRate],[Name] FROM [AdventureWorks].[Sales].[SalesTaxRate];" -QueryTimeout 3

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true

$workbook = $excel.Workbooks.Add()

$sheet = $workbook.ActiveSheet

$counter = 0

foreach ($item in $territoryrs) {

 $counter++

    $sheet.cells.Item($counter,1) = $item.StateProvinceID
    $sheet.cells.Item($counter,2) = $item.TaxType
    $sheet.cells.Item($counter,3) = $item.TaxRate
    $sheet.cells.Item($counter,4) = $item.Name
}

#  Exporting Excel data is as easy as...
$sheet.SaveAs("taxrates.csv", 6)

Thanks,

Bryan