Tableau via PowerShell, Part 2: Saving Changes

Data

Tableau via PowerShell, Part 2: Saving Changes

This is part 2 of my mini-series on exploring Tableau workbooks with PowerShell. If you missed it, you should read Part 1: Opening Workbooks, before continuing.

Welcome back! Last time I showed you how to open a Tableau workbook (TWB or TWBX) in PowerShell so you could start exploring the raw XML. Today I’ll show the next logical thing, saving the changes you make.

If you are only opening TWB files, then saving your changes couldn’t be easier. Since TWBs are just XML files we can simply write it to a file, like so.

$workbook = Get-TableauWorkbookXml 'MyWorkbook.twb'
$workbook.Save('ModifiedWorkbook.twb')

Cake.

Saving changes back to a TWBX file is a bit more work but still totally doable. Let’s write a new function, Export-TableauPackagedWorkbook. We will spice things up a bit by making this function smart. It’ll take a few parameters to allow us to control exactly how we want the export to happen.

A -Force parameter will let us specify whether we want to overwrite the destination file if it already exists. By default we would be prompted. Note that this isn’t a terribly useful parameter since it would replace the TWBX with an empty TWBX containing only the TWB.

-Update will let us specify that we want to update the TWB inside the destination TWBX file if the destination file exists. This is the cool parameter because it lets us open an existing TWBX, make changes to the TWB inside, and then save those changes back out into the original TWBX.

We will also support the common -WhatIf and -Confirm parameters that are so useful in PowerShell.

Here is our function:

If you find this helpful then check out TableauKit; a full on PowerShell module for working with Tableau files. It contains a new and improved version of the function below and much more.

function Export-TableauPackagedWorkbook {

#>
    [CmdletBinding(
        SupportsShouldProcess=$true
    )]
    param(
        [Parameter(
            Position=0,
            Mandatory=$true
        )]
        [string]$Path,
 
        [Parameter(
            Position=1,
            Mandatory=$true,
            ValueFromPipeline=$true
        )]
        [xml]$WorkbookXml,
 
        [switch]$Update,
        [switch]$Force
    )
 
    begin {
        $originalCurrentDirectory = [System.Environment]::CurrentDirectory
 
        # System.IO.Compression.FileSystem requires at least .NET 4.5
        [System.Reflection.Assembly]::LoadWithPartialName("System.IO.Compression") | Out-Null
    }
 
    process {
        [System.Environment]::CurrentDirectory = (Get-Location).Path
        $entryName = [System.IO.Path]::GetFileNameWithoutExtension($Path) + '.twb'
        $createNewTwbx = $false
       
        if (Test-Path $Path) {
            if ($Update -or $Force -or $PSCmdlet.ShouldContinue('Overwrite existing file?', 'Confirm')) {
                if ($Update) {
                    if ($PSCmdlet.ShouldProcess($Path, 'Update TWB in packaged workbook')) {
 
                        [System.IO.FileStream]$fileStream = $null
                        [System.IO.Compression.ZipArchive]$zip = $null
                        try {
                            $fileStream = New-Object System.IO.FileStream -ArgumentList $Path, ([System.IO.FileMode]::Open), ([System.IO.FileAccess]::ReadWrite), ([System.IO.FileShare]::Read)
                            $zip = New-Object System.IO.Compression.ZipArchive -ArgumentList $fileStream, ([System.IO.Compression.ZipArchiveMode]::Update)
 
                            # Locate the existing TWB entry and remove it.
                            $entry = $zip.Entries |
                                where {
                                    # Look for a .twb file at the root level of the archive.
                                    $_.FullName -eq $_.Name -and ([System.IO.Path]::GetExtension($_.Name)) -eq '.twb'
                                } |
                                select -First 1
                            if ($entry) {
                                $entry.Delete()
                            }
 
                            $entry = $zip.CreateEntry($entryName, ([System.IO.Compression.CompressionLevel]::Optimal))
                            [System.IO.Stream]$entryStream = $null
                            try {
                                $entryStream = $entry.Open()
                                $WorkbookXml.Save($entryStream)
                            }
                            finally {
                                if ($entryStream) {
                                    $entryStream.Dispose()
                                }
                            }
                        }
                        finally {
                            if ($zip) {
                                $zip.Dispose()
                            }
                            if ($fileStream) {
                                $fileStream.Dispose()
                            }
                        }
                    }
                }
                else {
                    if ($PSCmdlet.ShouldProcess($Path, 'Replace existing packaged workbook')) {
                        # delete existing TWBX
                        Remove-Item $Path -ErrorAction Stop #TODO: Figure out how to pass WhatIf and Confirm to this
                        
                        $createNewTwbx = $true
                    }
                }
            }
        }
        else {
            if ($PSCmdlet.ShouldProcess($Path, 'Export packaged workbook')) {
                $createNewTwbx = $true
            }
        }
 
        if ($createNewTwbx) {
            [System.IO.FileStream]$fileStream = $null
            [System.IO.Compression.ZipArchive]$zip = $null
            try {
                $fileStream = New-Object System.IO.FileStream -ArgumentList $Path, ([System.IO.FileMode]::CreateNew), ([System.IO.FileAccess]::ReadWrite), ([System.IO.FileShare]::None)
                $zip = New-Object System.IO.Compression.ZipArchive -ArgumentList $fileStream, ([System.IO.Compression.ZipArchiveMode]::Update)
 
                $entry = $zip.CreateEntry($entryName, ([System.IO.Compression.CompressionLevel]::Optimal))
                [System.IO.Stream]$entryStream = $null
                try {
                    $entryStream = $entry.Open()
                    $WorkbookXml.Save($entryStream)
                }
                finally {
                    if ($entryStream) {
                        $entryStream.Dispose()
                    }
               }
            }
            finally {
                if ($zip) {
                    $zip.Dispose()
                }
                if ($fileStream) {
                    $fileStream.Dispose()
                }
            }
        }
    }
 
    end {
        [System.Environment]::CurrentDirectory = $originalCurrentDirectory
    }
}

Not too bad. Here are some quick usage examples.

Convert a TWB into a TWBX

$workbook = Get-TableauWorkbookXml 'MyWorkbook.twb'
$workbook | Export-TableauPackagedWorkbook 'MyPackagedWorkbook.twbx'

Update an existing TWBX

$workbook = Get-TableauWorkbookXml 'MyPackagedWorkbook.twbx'
# TODO: Make some changes to the workbook XML
$workbook | Export-TableauPackagedWorkbook 'MyPackagedWorkbook.twbx' -Update

Leave me a comment if you have any questions about working with Tableau workbooks in PowerShell. Maybe I’ll cover your topic in the next post!

More About the Author

Joshua Poehls

Software Architect
TableauKit: A PowerShell Module for Tableau If you’ve been following my Tableau via PowerShell series then you already know there are a lot of cool things you can do with Tableau ...
Tableau via PowerShell, Part 2: Saving Changes This is part 2 of my mini-series on exploring Tableau workbooks with PowerShell. If you missed it, you should read Part 1: Opening ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK