Introduction

I love Powershell. Over my IT career I have spent a lot of time learning how to use it to automate all kinds of things: batch creation of Active Directory accounts, interfacing with APIs, tracking network congestion, and conducting mass software deployments. During the past year I’ve been focused on learning DevOps practices, where Linux is king and Powershell is rarely mentioned - and I’ve missed using it.

Fortunately, I had a use case recently where Powershell would be a great tool. I needed to update a CSV file. Specifically, I needed to add “tag” attributes to cards for my MTG Cube. This can be done in the web app, but it’s time consuming. There is also no API, but you can export a CSV, then modify and upload it to update your cube. So I wrote a tool to do just that.

Setup

I often start writing a script by interactively working out what needs to happen in a shell. So, After downloading the CSV, I imported it to a variable to see what the format was like

screenshot

Looked good, but I needed a card that had some tags on it in order to match their format, so I ran the command:

$csv | where {$_.tags -ne ""}

and found one that had multiple tags:

screenshot

Now that I knew the format the web app expected, I tried editing a card (row) and saving it to the same CSV file:

screenshot

Then I uploaded it the web app and saw that the tags updated as expected.

Adding Data in Bulk

I had the basics laid out, now I needed to scale it up. The way I would do that would be to use a different web app that essentially let me select cards and export them to a text file. So, I would do that for one tag at a time, using the tag as the filename. Example:

Spells.txt
1 Bazaar of Baghdad
1 Kolaghan's Command
1 Grapple with the Past
1 Bonecrusher Giant
1 Collective Brutality
1 Territorial Kavu
1 Scourge of Nel Toth
1 Oona's Prowler
1 Divest

I had to parse one of these text files, striping the number, and add the name of the file (the tag) to each CSV entry that matched a line in the text file. Along with that I realized I needed to cover the common cases of a card already having the tag. Here’s what I eventually ended up with:

        $cards = Get-Content $DeckFile
        foreach ($card in $cards) {
            $card = $card.Substring(2)
            $csv | ForEach-Object {
                if ($_.name -eq $card) {
                    if (!($_.tags.Contains($tag))) {
                        if ($_.tags -eq "") {
                            $_.tags = $tag
                        }
                        else {
                        $_.tags = $_.tags + ";" + $tag
                        }
                    Write-Output "Adding $($tag) to $($card)"
                    }
                    else {
                        Write-Verbose "$($card) is already tagged with $($tag)"
                    }
                }
            }
        }

It’s not the prettiest of logic, but it worked. The only improvement I wanted to make was to generalize it a bit more into a cmdlet.

Putting it All Together

Creating a cmdlet is pretty easy, but I do it seldom enough that I always forget the details. Luckily I made a gist for it on GitHub. So, a copy/paste and few modifications later and I had the final product:

function Add-TagsToCards {
    [CmdletBinding()]
    Param(
        [Parameter(
            Position=0,
            Mandatory=$True,
            ValueFromPipeline=$True
        )]
        [string] $DeckFile,
        [Parameter(
            Position=1,
            Mandatory=$False
        )]
        [string] $CubeCSV = "./RicksAwesomeCube.csv",
        [string] $OutputCSV = "./RicksAwesomeCube.csv"
    )
    Process {
        $file = Get-ChildItem $DeckFile
        $tag = $file.BaseName

        $csv = Import-Csv $CubeCSV
        
        $cards = Get-Content $DeckFile
        foreach ($card in $cards) {
            $card = $card.Substring(2)
            $csv | ForEach-Object {
                if ($_.name -eq $card) {
                    if (!($_.tags.Contains($tag))) {
                        if ($_.tags -eq "") {
                            $_.tags = $tag
                        }
                        else {
                        $_.tags = $_.tags + ";" + $tag
                        }
                    Write-Output "Adding $($tag) to $($card)"
                    }
                    else {
                        Write-Verbose "$($card) is already tagged with $($tag)"
                    }
                }
            }
        }
        $csv | Export-Csv $OutputCSV
    }
}

Conclusion

Now when I make a new set of selections, I can export the cube (csv) and deck (txt) files and run the command

Add-TagsToCards Spells.txt RicksAwesomeCube.csv

and re-import the modified csv. This is an improvement over the process of tagging cards in the web interface. Of course it would be even better if there was an API, but this works.

It was nice to be able to dust off some old skills and work with Powershell again. I hope to be able to use it in the future as I work towards learning DevOps engineering skills.

Until next time,
Cheers!
Rick