7 tips to automate your daily DWH/BI developer life using PowerShell and Excel

PowerShell is a powerful tool that will make your life easier. You can use it to automate your daily work or make boring taks interesting. It can also save you time.

In this blog post you will see how can you:

• Connect to Excel using PowerShell
• Get a sheet name
• Find a named table
• Display value from the cell
• Loop through table
• Execute Excel Macro from PowerShell
• And finally save an Excel file using PowerShell

1. How to connect to Excel using PowerShell

$excel = New-Object -ComObject Excel.Application 
$Excel.visible = $true
$workbook = $excel.Workbooks.Open($path)

2. How to get Excel sheet name using PowerShell:

Using an index of a sheet:

$structureDefinitionSheet = $workbook.Sheets.Item(1)

Using a sheet name:

$structureDefinitionSheet = $workbook.Sheets['Structure definition']

3. How to find named table in Excel using PowerShell

List objects and use where clause to find matching table name

$tableDefinitions = $structureDefinitionSheet.ListObjects | where-object { $_.DisplayName -eq "TableDefinition" }

From list objects, just specify the name

$tableDefinitions = $structureDefinitionSheet.ListObjects["TableDefinition"]

Additionally you might be interested in getting the list of all objects in the sheet:

$objects = $structureDefinitionSheet.ListObjects

4. How to display value from a cell using PowerShell

#this is not good practice
$configSheet.Range('B2').Value2
$configSheet.Range('C2').Value2

Maybe you can use tables? Or defined ranges. See next point for details.

5. How to loop through table

$rows = $tableDefinitions.ListRows
foreach($row in $rows) {
    $tableName = $row.Range.Columns[1].Value2
    $columnDefinition='  ' + $row.Range.Columns[2].Value2 + ' ' + $row.Range.Columns[3].Value2 + ' COMMENT "' + $row.Range.Columns[4].Value2 + '",'
}

6. How to execute an Excel Macro using PowerShell

$excel = New-Object -ComObject Excel.Application 
$Excel.visible = $true
$workbook = $excel.Workbooks.Open($path)

$macro = 'generateSelect'
$app = $excel.Application
$app.Run(($fileName+"!ThisWorkbook."+$macro)) #generateSelect

7. How to save Excel using PS

To the existing file:

$workbook.save()

To save a file with a different name:

$workbook.SaveAs($folder+"metaprogramming_version2.xlsx")

When you would like to use SaveAs option, but file already exists you will get a warning message. To avoid it you can use:

$Excel.DisplayAlerts = $false

This will hide the warning, override the file and close it.

How to find PowerShell useful

You may ask how PowerShell may be useful in developing DWH or BI solutions? The Simplest answers might be: to automate your work and make tedious task interesting.

For example, in Excel you may have a schema definition or master-data that you need to ingest into DWH. Then you may use PowerShell to automate your work and go through your data easier.

You may not only read data from Excel but, you may also write data to it. Imagine that you have a source definition in SQL Server and your Data Warehouse is on Oracle. It is fairly easy to read SQL Server definition into Excel and generate DWH Oracle code out of it. Or share the code with other team members.

You can also imagine that you have 100 Excels files that contains the same structure, and you would like to extract some information out of it. PowerShell can make your life easier.

Please understand me correctly. This is not the only way. But PowerShell might be useful, when connecting to Excel and knowing this possibility opens a lot of doors.

Please tell me how are you using Excel in your daily professional life. Do you see it handy to connect programmatically using PowerShell and automate your daily work?

Full code on GitHub

You can find full code on GitHub:
Connect to Excel using PowerShell
Run Excel Macro

Comments

[Andrzej Rusin]

Great article! I also find PowerShell very powerful for automating some repetitive or administrative tasks. Sometimes in combination with Excel, when for example I need to build some SQL query for a complex but well structured view. You can easily write a formula that will output the SQL construct for the specific field and then extend it to other rows. Then in PowerShell you can loop through the column and concatenate into single query.
I find it very convenient to loop through the specific column in a name table as follows:

$rng = $wks.Range("queryTable[ColumnName]")
foreach($cell in $rng) {
    # do something on cell
}

Having full list of columns you can easily build a SQL query and then with the use of PowerShell update the view definition in for example SAP PowerDesigner, as automating it with PowerShell is just unbelievably easy :)

$powerDesignerApp = New-Object -ComObject "PowerDesigner.Application"

From there once the object is created you can traverse down the object model to check what is there and what can be used. It is just very quick and efficient to develop a script and over time improve it or add functionalities.
Bottom line is that for me it is one of the most useful tools in my inventory :)
Cheers!

[rgogloza]

Thanks for the comment!

Excel. PowerShell and PowerDesigner is a time saver.

This com object import in PowerShell can be also used to connect to Word, and for example generate documentation out of it.