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

rgogloza — Tue, 25 May 2021 18:17:39 +0000

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

DEV Community: rgogloza