Save an Azure Data Studio PowerShell notebook as a ps1 file

January 3, 2021
martha
0 Comments

You may have seen that you can now export a SQL notebook as .sql file (and import a .sql file to a notebook).

But what about our good friend PowerShell? Here’s a script to convert a PowerShell notebook to a PowerShell script file. It will also convert a SQL notebook to a SQL file because I wrote the script before the fancy buttons came out. So if for some reason you have a folder with a bunch of notebooks you could run it on all the files without opening them and do a mass conversion.

Skip to the bottom if you just want the code (I won’t be offended).

Start with a notebook

First, let’s create a (very simple) PowerShell notebook:

Powershell notebook with Text block 'List the modules installed', code block 'Get-Module'

It has a text block that we want commented out and a PowerShell command that we want written as is. So the output should look like this:

<#
List the modules installed
#>

Get-Module

What are .ipynb files?

If we open the .ipynb file in a text editor you can see that it’s just JSON:

{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell",
            "language": "powershell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "List the modules installed"
            ],
            "metadata": {
                "azdata_cell_guid": "2240700e-5d56-4096-bb59-b7ade03e58a3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-Module"
            ],
            "metadata": {
                "azdata_cell_guid": "e26e8a8b-f4fc-440b-b2d8-f6a1b728d76b"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}

Let’s bring it into PowerShell…

It looks like all the text and code blocks are contained in the “cells ” entry. Let’s use ConvertFrom-Json to add everything to a PowerShell object and see what’s in “cells”.

$json = ((Get-Content -Path '.\PSExample.ipynb' -Raw) | ConvertFrom-Json)
$cells = $json.cells
$cells

Hey, this is easy! The values we want are in the source property and the cell_type property determines whether they are text (markdown) or code.

Output of $cells command from above code

If the cell_type value is markdown, we’ll want to comment out the source value and if the cell_type value is code, we’ll just output as is.

Now we can export to .ps1

First we can create the comment variables

$commentopen = '<#'
$commentclose = '#>'

Now we’ll go through each cell, determine the type, add block comments if needed and then write the output to our .ps1 file

If a cell isn’t markdown or code we’ll make a note of that but still output the cell contents.

$filePath = '.\output\PSexample.ps1'
ForEach ($cell in $cells){
    If ($cell.cell_type -eq 'markdown') {
        $commentopen | Out-File -filePath $filePath -append 
        $cell.source| Out-File -filePath $filePath -append  
        $commentclose | Out-File -filePath $filePath -append  
    } ElseIf ($cell.cell_type -eq 'code') {
        $cell.source| Out-File -filePath $filePath -append 
    } Else {
        'unknown cell type ' + $cell.cell_type | Out-File -FilePath $filePath -Append 
        $cell.source| Out-File -filePath $filePath -append 
    }      
}

Et voila! A .ps1 file of our notebook

How do we determine if it’s PowerShell or SQL?

To create a script that will run on both SQL and Powershell notebooks we need to determine the kernel type. There is a metadata object with some key values to determine whether the kernel is SQL or Powershell. This one is SQL:

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    }

And this one is PowerShell:

{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell",
            "language": "powershell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        }
    }

So the value of metadata.kernelspec.language will be PowerShell or SQL depending on the language.

output is 'powershell' from $json.metadata.kernalspec.language

And we’ll add some values for SQL comments

$commentopen = '/*'
$commentclose = '*/'

Putting it all together

Now we have a script that takes a notebook and saves it as a .sql or .ps1 file of the same name.

By design it overwrites an existing file with the same name so make sure that is what you are expecting if you run this as is!

param (
    [parameter(Mandatory=$true)] $notebookPath,
    [parameter(Mandatory=$true)] $outputFolder
)

#get the notebook name without extension
$notebookName = (get-item $notebookPath).BaseName

#get the contents of the json file
$json = ((Get-Content -Path $notebookPath -Raw) | ConvertFrom-Json)

#use the language to determine the file type and comment formatting


If ($json.metadata.kernelspec.language -eq 'sql'){
    $extension = '.sql'
    $commentopen = '/*'
    $commentclose = '*/'
#sql
} ElseIf ($json.metadata.kernelspec.language -eq 'powershell'){
    $extension = '.ps1'
    $commentopen = '<#'
    $commentclose = '#>'
#powershell
} Else {
    Write-Host 'Language is not powershell or SQL, write a new ElseIf block with your preferred comment formatting'
    Exit
#for anything else, exit the script
}
#create the output file path

$filePath = $outputFolder + '\' + $NotebookName + $extension 

#delete the file if it already exists 
If (Test-Path $filePath) {
  Remove-Item $filePath
}

#work through the file and add comment formatting around each markdown block and write each source (code) block as is. Mark any cells that do not fall as markdown or code (but still output the code in the source cell)
$cells = $json.cells
ForEach ($cell in $cells){
    If ($cell.cell_type -eq 'markdown') {
        $commentopen | Out-File -filePath $filePath -append -encoding utf8 
        $cell.source| Out-File -filePath $filePath -append -encoding utf8 
        $commentclose | Out-File -filePath $filePath -append -encoding utf8 
    } ElseIf ($cell.cell_type -eq 'code') {
        $cell.source| Out-File -filePath $filePath -append -encoding utf8
    } Else {
        'unknown cell type ' + $cell.cell_type | Out-File -FilePath $filePath -Append -Encoding utf8
        $cell.source| Out-File -filePath $filePath -append -encoding utf8
    }      
}

To run the script use the full path of the notebook and the path of the folder where you would like the output saved.

.\SaveNotebookAsNotNotebook.ps1 -NotebookPath .\Notebook.ipynb -OutputFolder .\output

Final thoughts

Is this useful? Maybe? Why didn’t it get added with the fancy export to .sql button?

I like being able to save things in a format that I can give to auditors without having them install Azure Data Studio so it’s useful to me. I also quite frequently start down a notebook path and then realize I just want a regular PowerShell script with an output file. This makes it easy to retreat if you change your mind.