Part #2 Create a script to export a card collection to a CSV-File

In the first part I explained some basics with PowerShell 7 and how to get details about a player. So, if you are a newbie to PowerShell, I recommend reading this part before proceeding with this one.

https://peakd.com/dev/@zalander/how-to-use-splinterlands-api-with-powershell-7-part-1-basics

This part is about, how to create a script in Visual Studio Code to get a player's card collection, the estimated value of each card and export the data to a csv-file. The csv file can be e.g. imported into Excel, in order to continue working with the data.

What the heck is "Visual Studio Code"?

Visual Studio Code is a free source-code editor made by Microsoft for Windows, Linux and macOS.

It's like a text editor with many features for developers.

For example, the feature IntelliCode helps you with syntax problems. Which can be very helpful especially, if you are a newbie to PowerShell.

Preparations

Download and install Visual Studio Code

Windows

Linux

macOS

Install the PowerShell extension

Creating the script file

Before we can code the script we have to create a script-file.

The file extension for a PowerShell Script-File is ".ps1"

To do that, execute the following steps:

Select Explorer in the sidebar on the left side
Go to File in menu of the window and select "Open Folder"
Create a new folder "scripts" e.g. in documents and select it
Go to File in menu of the window and select "New File"
Click on "Select a language", search for "Powershell" and select it by pressing enter
Go to File in menu of the window, select "Save as", enter a name e.g. "CardCollectionToCsv.ps1" and save it

After that we are finally ready to code 😀

Coding the script

In order to be able to enter a player name as a parameter when executing the script, the script must start with the following lines. Which at first glance seem much more complicated than they are.

Param(
    [Parameter(Mandatory=$True)]
    [string]$userName
)

Param(...)

Variables within "param" can be transferred to the script when the script is called.

[Parameter(Mandatory=$True)]

Sets the parameter in next line as mandatory. So the script will not execute without it.

[string]$userName

Type and name of the variable. The type is string and the name username. We choose string as type to ensure that entered value is a string. Each variable can be strong typed to ensure that only this type can be set as an value.

If we run the script afterwards, the shell will ask you to enter userName parameter. If a username is not provided, an error will be displayed.

In the next step we will get the player card collection data from the API via the Invoke-RestMethod command, which I adjusted in part 1.

$response = Invoke-RestMethod -Method Get -Uri "https://api2.splinterlands.com/cards/collection/$userName"

We store the return of the Invoke-RestMethod command in the variable $response, which contains the property cards after successful execution.

After that we have to check whether we have received any card from the API using a simple if statement. If not, we'll write an error and exit the script.

if ($response.cards.Count -eq 0)
{
    Write-Error "Card collection from player $userName is emtpy."
    exit;
}

The cards property is of type array and contains the property count, which we can use to check if cards count equals 0.

In order to get only the cards owned by the player, we need to filter out any rented or delegated cards to the player.

$cardCollection = $response.cards | where { $_.player -eq $userName }

So we pipeline all cards to the where function, where we filter out all cards for which the property player does not match the username. And store the result into $cardCollection variable. (Inside the where function each card will be enumerated as "$_")

Then we have to check whether the player owns any card. If not, we'll write an error and exit the script.

if ($cardCollection.Count -eq 0)
{
    Write-Error "Player $userName doesn't own any card."
    exit;
}

Next, we need to use the API to get market information for each card in order to be able to determine the estimated value of each one.

$cardsForSale = Invoke-RestMethod -Method Get -Uri "https://api2.splinterlands.com/market/for_sale_grouped"

Afterwards we initialize a new variable $cardArray as an array type, where we can store new objects with the merged information.

[array]$cardArray = @()

Now we use the foreach loop to enumerate the $cardCollection variable, lookup in $cardsForSale for the estimated value in US-Dollar, the name via API and add an new object to the $cardArray with the merged data.

foreach ($card in $cardCollection)
{
    $responseCardInfo = Invoke-RestMethod -Method Get -Uri ([string]::Format("https://api2.splinterlands.com/cards/find?ids={0}", $card.uid))
    $cardSaleGroup = ($cardsForSale | where { ($_.card_detail_id -eq $card.card_detail_id) -and ($_.gold -eq $card.gold) })
    $cardArray += [PSCustomObject]@{ 
        Id = $card.card_detail_id
        Name = $responseCardInfo.details.name
        "Gold Foil" = $card.gold ? "Yes" : "No"
        "Estimated Value in $" = $cardSaleGroup.low_price_bcx -eq $null ? "unknown" : [string]::Format("{0:0.000}", $cardSaleGroup.low_price_bcx) 
    };
}

$responseCardInfo = Invoke-RestMethod -Method Get -Uri ([string]::Format("https://api2.splinterlands.com/cards/find?ids={0}", $card.uid))

Getting some basic infomation about the card. [string]::Format(...) is bit .Net Core magic and can be very helpful to format a string. https://devblogs.microsoft.com/scripting/understanding-powershell-and-basic-string-formatting/

$cardSaleGroup = ($cardsForSale | where { ($_.card_detail_id -eq $card.card_detail_id) -and ($_.gold -eq $card.gold) })

Lookup for card in the $cardsForSale array.

$cardArray += [PSCustomObject]@{....}

Adding an new item of type PSCustomObject to the $cardArray. Each new item contains some properties to store the values we want to proceed with.

We can do that but it's kind of inefficient, because if the player have the same card more than once in his collection we will call the API again and again for the same card. So it is better to first check if the array already contains the card and then just add it again.

foreach ($card in $cardCollection)
{
    $cardKey = [string]::Format("{0}::{1}", $card.card_detail_id, $card.gold)
    $tempCard = $cardArray | where { $_.Key -eq $cardKey } | select -First 1
    if ($tempCard -eq $null)
    {
        $responseCardInfo = Invoke-RestMethod -Method Get -Uri ([string]::Format("https://api2.splinterlands.com/cards/find?ids={0}", $card.uid))
        $cardSaleGroup = ($cardsForSale | where { ($_.card_detail_id -eq $card.card_detail_id) -and ($_.gold -eq $card.gold) })
        $cardArray += [PSCustomObject]@{ 
            Key = $cardKey
            Id = $card.card_detail_id
            Name = $responseCardInfo.details.name
            "Gold Foil" = $card.gold ? "Yes" : "No"
            "Estimated Value in $" = $cardSaleGroup.low_price_bcx -eq $null ? "unknown" : [string]::Format("{0:0.000}", $cardSaleGroup.low_price_bcx) 
        };
    }
    else 
    {
        $cardArray += $tempCard
    }
}

$cardKey = [string]::Format("{0}::{1}", $card.card_detail_id, $card.gold)

Building a key so we can demeter if the array already contains the card.

$tempCard = $cardArray | where { $_.Key -eq $cardKey } | select -First 1

Lookup for a card with the key in $cardArray.

if ($tempCard -eq $null)

Check if a card with the key exists.

Finally we can export the data without the property Key to an csv-file by using the Export-Csv command.

$cardArray | select -Property Id, Name, "Gold Foil", "Estimated Value in $" | Export-Csv -Path "$PSScriptRoot\card collection($userName).csv" -Delimiter ';' -Force

In the first part of the pipeline we select all properties which should be exported. And in the second part we export the data to an csv-file using the ; as delimiter. (MS Excel just imports csv-files with ; as delimiter correctly) The file will be created in the root path of the script ans will be overriden if it already exists.

After the export, the content in the csv-file should look like this:

If you want to filter out all Gladius Cards in the collection, you can do that with a simple change:

foreach ($card in ($cardCollection | where { $_.edition -ne 6 }))

Or if you want to output the csv-file in another directory rather than the script root path, you can just add an additional not mandatory parameter with script root path as default value.

Param(
    [Parameter(Mandatory=$True)]
    [string]$userName,
    [Parameter(Mandatory=$false)]
    [string]$outputDirectory = $PSScriptRoot
)
..........
$cardArray | select -Property Id, Name, "Gold Foil", "Estimated Value in $" | Export-Csv -Path "$outputDirectory\card collection($userName).csv" -Delimiter ';' -Force

Final script

Param(
    [Parameter(Mandatory=$True)]
    [string]$userName,
    [Parameter(Mandatory=$false)]
    [string]$outputDirectory = $PSScriptRoot
)
$response = Invoke-RestMethod -Method Get -Uri "https://api2.splinterlands.com/cards/collection/$userName"
if ($response.cards.Count -eq 0)
{
    Write-Error "Card collection from player $userName is emtpy."
    exit;
}
$cardCollection = $response.cards | where { $_.player -eq $userName }
if ($cardCollection.Count -eq 0)
{
    Write-Error "Player $userName doesn't own any card."
    exit;
}
$cardsForSale = Invoke-RestMethod -Method Get -Uri "https://api2.splinterlands.com/market/for_sale_grouped"
[array]$cardArray = @()
foreach ($card in ($cardCollection | where { $_.edition -ne 6 }))
{
    $cardKey = [string]::Format("{0}::{1}", $card.card_detail_id, $card.gold);
    $tempCard = $cardArray | where { $_.Key -eq $cardKey } | select -First 1
    if ($tempCard -eq $null)
    {
        $responseCardInfo = Invoke-RestMethod -Method Get -Uri ([string]::Format("https://api2.splinterlands.com/cards/find?ids={0}", $card.uid))
        $cardSaleGroup = ($cardsForSale | where { ($_.card_detail_id -eq $card.card_detail_id) -and ($_.gold -eq $card.gold) })
        $cardArray += [PSCustomObject]@{ 
            Key = $cardKey
            Id = $card.card_detail_id
            Name = $responseCardInfo.details.name
            "Gold Foil" = $card.gold ? "Yes" : "No"
            "Estimated Value in $" = $cardSaleGroup.low_price_bcx -eq $null ? "unknown" : [string]::Format("{0:0.000}", $cardSaleGroup.low_price_bcx) 
        };
    }
    else 
    {
        $cardArray += $tempCard
    }
}
$cardArray | select -Property Id, Name, "Gold Foil", "Estimated Value in $" | Export-Csv -Path "$outputDirectory\card collection($userName).csv" -Delimiter ';' -Force

In the next part I will show how to create a script to get info about the last X rewards(daily and season) and preparing the data for an weekly or monthly financial report in blog post format(html). If you like the post, please consider to vote and/or reblog it.