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 Just download and install Visual Studio Code from https://code.visualstudio.com/download for your platform. If you need installation instructions, see the links below for help.
- Install the PowerShell extension Run Visual Studio Code, select Extensions in the sidebar on the left side and search for "Powershell".
Select the PowerShell extension and install it.
Creating the 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
Param(
[Parameter(Mandatory=$True)]
[string]$userName
)
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.
- 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.
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.
This is incredibly useful. Thank you for taking the time to share this information. Understanding API and there uses is important. I am unfamiliar with PowerShell so I will need to click the link in the beginning of your article, feel it would be good to know :-D
If you have any question don't hesitate to ask 😀 It can be very complicated at first and to fully unterstand pipelines can take some time but it can also be very useful - there are REST-APIs everywhere. And if you have Javascript experience, the syntax is almost the same.
Super nice one!
Proud of you and thanks for contributing such great stuff!
Dear @zalander,
The previous HiveBuzz proposal expired end of December.
Do you mind supporting our proposal for 2022 so our team can continue its work next year?
You can do it on Peakd, ecency,
https://peakd.com/me/proposals/199
Thank you. We wish you a Happy New Year!
Monsterly useful post!!!
Wow very detailed instructions! Looks very useful in the right hands
Looks like a great starting point to refresh some of our coding skills. I can see looking up favorite cards to buy and getting current prices.
!PIZZA
!LUV
PIZZA Holders sent $PIZZA tips in this post's comments:
@ijat(4/5) tipped @zalander (x1)
You can now send $PIZZA tips in Discord via tip.cc!