Use Total Seller with Windows PowerShell to have Total Seller connect to external systems or automate import, export and backup functions.
Note:Total Seller Cmdlets were designed for PowerShell v3 or later. PowerShell v3 is installed automatically with Windows 8, Windows 8.1, Windows 10 and Windows Server 2012. You can download and install PowerShell v3 for older versions of Windows on Microsoft's website. Thereafter, the program must be reinstalled in order to register the snap-in with the new version of PowerShell.
Total Seller and the Total Seller PowerShell Snap-In are implemented as 32 bit applications. In the 64-bit version of Windows you should use "PowerShell (x86)" to develop and execute PowerShell scripts.
In the 64-bit version of Windows you can start "PowerShell (x86)" from the command line or in the Task Scheduler with the following command:
%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe
To create a PowerShell script for Total Seller that runs under the 32 bit and 64 bit version of Windows, use the following script code:
If ([IntPtr]::Size * 8 -ne 32)
{
C:\Windows\SysWOW64\WindowsPowerShell\v1.0\PowerShell.exe -File $MyInvocation.MyCommand.Path
}
Else
{
# Your script code here
}
Note that the use of your own PowerShell scripts in Windows must be activated.Start the (32 bit) PowerShell console as an administrator and enter the following command:
Set-ExecutionPolicy Unrestricted
Total Seller Cmdlets
Use "Get-Help Cmdlet-name" in PowerShell to obtain additional descriptions and help for Cmdlets.
Add-PSSnapin TotalSellerSnapin
Run the command "Add-PSSnapin TotalSellerSnapin" to integrate the Total Seller Cmdlets in PowerShell. You should use this command to start a PowerShell session or run PowerShell scripts.
Connect-TSSession
Parameter: "Login-Password" or "Credentials"
The command "Connect TSSession" connects PowerShell with the Total Seller database and logins a Total Seller user. The user must be either an Total Seller administrator or be in a user group entitled to run scripts.
Tip: Create your own user group that is only entitled to run scripts and assign this group to a new user that you only use with PowerShell.
Disconnect-TSSession
Closes the connection to the Total Seller database.
Write-TSBackup
Parameter: "-Path"
Backup of the database to a Total Seller file.
Important: If Total Seller is connected to Total Seller server, the path is on the computer refers to Total Seller installation server and not to the computer where the PowerShell command is executed. If necessary, enter an UNC path.
Read-TSBackup
Parameter: "-Path"
Restores the database Total Seller from a backup file.
Important: If Total Seller is connected to Total Seller server, the path is on the computer refers to Total Seller installation server and not to the computer where the PowerShell command is executed. If necessary, enter an UNC path.
Import-TSData
Parameter: "-DefinitionName", "-Path" (Alias: "-FullName", "-Url"), "-Variables"
Import product data from text, CSV or Excel files using an Import Definition that you can create in Total Seller. You can specify a path and filename of the import file or a URL. Optionally, set the Variables of the Import Definition using a PowerShell Hash-Table.
Get-TSProduct
Parameter: "-All", -"ID", "-Identity", "-ProductId", "-Sku", "-ItemId", "-Code", "-Wildcard", zusätzliche Filter: "-Available", "-CodeType", "-Location", "-Media", "-PriceCategory", "-Category", "-Condition", "-Supplier"
If the "wildcard" parameter is set, patterns can be used with "Sku", "ItemId" and "Code" parameters. If the "wildcard" parameter is set, patterns can be used with "Sku", "ItemId" and "Code" parameters. In this case, the '*' character stands for any number of characters. (see examples below)
Set-TSProduct
Parameter: "ID", "Identity", "ProductId", fields that should be modified, for testing "WhatIf"
The command "Set-TSProduct" is used to modify the products that were returned by "Get TSProduct".
The following product data is subject to change:
Parameter name |
Description |
Batch |
Batch |
Category |
Product category |
Code |
Code |
CodeType |
Code Type: "ISBN", "EAN", "UPC" |
Condition |
Condition |
CreatedDate |
Creation date |
CreatedUser |
Created by user |
Creators |
Authors / Artists |
EditedDate |
Modified date |
EditedUser |
Edited by user |
Info1 |
Info 1 |
Info2 |
Info 2 |
InStore |
Available at the store |
ListPrice |
List price |
Location |
Storage location |
Manufacturer |
Manufacturer |
Media |
Format |
Name |
Name |
Notes |
Notes |
PriceCategory |
Price category |
ReorderLevel |
Minimum available for reordering |
ReorderQuantity |
Quantity for reordering |
SKU |
SKU |
Storage |
Storage location |
StorePrice |
Store price |
Supplier |
Supplier |
SupplierCode |
Supplier order no. |
UnitsAvailable |
Available quantity |
UnitsOnOrder |
Backordered quantity |
UnitsPending |
Pre-ordered quantity |
UnitsSold |
Sold quantity |
VatRate |
Tax group |
Weight |
Weight |
WorkflowMessage |
Workflow step message |
WorkflowStep |
Workflow step |
Be careful not to change data used internally by Total Seller or Amazon.
Update-TSProduct
Parameter:"ID", "Identity", "ProductId", "Sku", for testing "WhatIf"
The command is similar to "Set-TSProduct" but can be used without "Get-TSProduct" if the product ID or SKU is known and the fields to be modified can pass through the PowerShell pipeline.
Create-TSProduct
Parameter: fields as in "Set-TSProduct", "WhatIf" for testing
A new product is created and returned. Fields to be set can be passed through the PowerShell pipeline.
Select-TSAmazonAccount
Parameter: "Name", "E-Mail", "Domain"
Use the command "Select-TSAmazonAccount" to switch between different Amazon seller accounts or Amazon platforms like Amazon.de, Amazon.fr, Amazon.co.uk, etc.
The following commands are related to the selected Amazon seller account.
This command is unnecessary if you use Total Seller with only one Amazon seller account or one Amazon platform.
Get-TSAmazonListing
Parameter: "All", "ID", "Identity", "ProductId", "Asin", "Sku", "ItemId", "Code", "Wildcard", additional filters: "Available", "CodeType", "Location", "Media", "PriceCategory", "Category", "Condition", "Supplier"
Returns one or more Amazon listings that meet the specified parameters and filters. If the "wildcard" parameter is set, patterns can be used with "Sku", "ItemId" and "Code" parameters. In this case, the '*' character stands for any number of characters. (see examples below)
Set-TSAmazonListing
Parameter: "ID", "Identity", "ProductId", fields that should be modified, for testing "WhatIf"
The command "Set-TSAmazonListing" is used to modify the Amazon listings that were returned by "Get-TSAmazonListing".
The following listing data is subject to change:
Parameter name |
Description |
AmzListingID |
Amazon listing ID |
ASIN |
ASIN |
CreatedDate |
Creation date |
CreatedUser |
Created by user |
DeletedDate |
Deleted date |
EditedDate |
Modified date |
EditedUser |
Edited by user |
FulfillmentChannel |
Amazon sales channel: "DEFAULT", "AMAZON_EU" |
ItemNote |
Listing description |
MaxPrice |
Maximum price |
MinPrice |
Minimum price |
ModifiedDate |
Modified date |
Price |
Listing price |
PriceCheckDate |
Date of last price adjustment change |
RemovedDate |
Removed date |
Shipping |
Shipped |
UploadedDate |
Uploaded date |
Be careful not to change data used internally by Total Seller or Amazon.
Update-TSAmazonListing
Parameter:"ID", "Identity", "ProductId", "Sku", for testing "WhatIf"
The command is similar to "Set-TSAmazonListing" but can be used without "Get-TSAmazonListing" if the product ID or SKU, or Amazon listing ID is known and the fields to be modified can pass through the PowerShell pipeline.
Create-TSAmazonListing
Parameter: "ProductId" and fields as in "Set-TSAmazonListing", "WhatIf" for testing
The parameter "ProductId" with a product ID assigns a product to a new listing. A new Amazon listing is created and returned. Fields to be set can be passed through the PowerShell pipeline.
Download-TSAmazonListings
Parameter: "-Location", "-Storage", "-PriceCategory", "-SetPriceRange", "-MinPricePercentage", "-MaxPricePercentage"
This command downloads your active listings from Amazon and imports them into Total Seller. Optionally, you can specify a storage warehouse, a location, a price category and the minimum and maximum price dependent on the current price. The command returns the newly imported listings. Already available listings will be updated but not returned by the command.
Upload-TSAmazonChanges
Parameter:"All" (Filter - "Available"), "ID", "Identity", "Prices", "Quantities", for testing "WhatIf"
The command transmits changes to the listings in Amazon.
Examples
The following examples can be entered directly into PowerShell.
Before you begin, run the following commands:
> Add-PSSnapin TotalSellerSnapin
> Connect-TSSession loginName password
Replace "login name" and "password" with your Total Seller login name and password.
Alternatively, you can enter:
> Get-Credential | Connect-TSSession
PowerShell prompts you to enter the login name and password and the password must not be visible when entered.
Then, enter:
> Get-TSProduct -Sku abc123
The data of the product with the SKU "abc123" is displayed in PowerShell.
> Get-TSProduct -All -WorkflowStep "Neu / Wartend"
Displays the product data for all products in the workflow step "New / Pending".
> Get-TSProduct -Sku "abc*" -Wildcard
Displays the product data for all products whose SKU starts with "abc".
> Get-TSProduct -Sku abc123 | Set-TSProduct -Info1 "Test 1"
Overwrites the field "info 1" of all products with SKU "abc123" with the value "Test 1".
Scripts
Note: Running your own scripts in PowerShell is disabled by default and must be explicitly enabled in PowerShell. To do this, start PowerShell as an administrator and enter the following PowerShell command:
> Set-ExecutionPolicy Unrestricted
From this point on, the execution of PowerShell scripts is enabled.
Perform a database backup
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Write-TSBackup "c:\Backup\TS Backup.bak"
Disconnect-TSSession
Save a database backup in "My Documents" with date
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
$name = ("TS Backup {0:yyyy-MM-dd}.bak" -f [DateTime]::Now)
$path = (Join-Path ([environment]::GetFolderPath("MyDocuments")) $name)
Write-TSBackup $path
Disconnect-TSSession
Using the Windows function "Schedule tasks", database backups can be automatically and periodically created.
Import product data using an Import Definition
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Import-TSData -DefinitionName "My Import Definition" -Path "e:\Data\ProductData.txt" -Variables @{ defaultPrice = 3.5 }
Disconnect-TSSession
Note: First create an Import Definition in Total Seller and then use the name of the Import Definition in the PowerShell script. You can specifiy a URL instead of a file name and the file will be automatically downloaded before the import starts.
Export all product data
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Get-TSProduct -All | Export-Csv -NoTypeInformation "Produkte.csv"
Disconnect-TSSession
Export minimum and maximum prices of available listings
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Get-TSAmazonListing -All -Available |
Select-Object ID,MinPrice,MaxPrice |
Export-Csv -NoTypeInformation "Offers.csv"
Disconnect-TSSession
Note: The command "Export CSV" exports numerical values formatted as per Windows locale, i.e. in the USA using point as decimal character.
Import minimum and maximum prices of exported listings (USA)
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Import-Csv "Offers.csv" | Update-TSAmazonListing
Disconnect-TSSession
Note: PowerShell converts numeric values that have been imported with "Import-CSV" into numbers according to neutral settings, i.e. always with a point as decimal character. In some countries with decimal comma, therefore, a file that has been exported with "Export-CSV", cannot be imported into Total Seller without converting it back with "Import-CSV". (Solution: see next example)
Import minimum and maximum prices of exported listings (Decimal comma, i.e. Germany)
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Import-Csv "Offers.csv" |
Select-Object ID,@{l="MinPrice"; e={[Single]::Parse($_.MinPrice)}},@{l="MaxPrice"; e={[Single]::Parse($_.MaxPrice)}} |
Update-TSAmazonListing
Disconnect-TSSession
Note: PowerShell converts numeric values that have been imported with "Import-CSV" into numbers according to neutral settings, i.e. always with a point as decimal character. In some countries with decimal comma, therefore, a file that has been exported with "Export-CSV", cannot be imported into Total Seller without converting it back with "Import-CSV". (Solution: see next example)
Import minimum and maximum prices from tab-delimited text file
In this example, a tab-delimited text file with the following columns is imported:
Sku MinPrice MaxPrice
68629 3.95 12.95
68630 4.95 19.95
Column headings match field names and numbers are stored with a point as decimal separator.
Script:
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Import-Csv -Delimiter `t "Offers.csv" | Update-TSAmazonListing
Disconnect-TSSession
Note: PowerShell converts numeric values that have been imported with "Import-CSV" into numbers according to neutral settings, i.e. always with a point as decimal character. If commas are used as decimal separator, the values in the previous example must be converted with "[Single]::Parse ($ _.Column)".
Import a CSV file and calculate minimum and maximum prices
In this example, a CSV file with the following columns is imported:
"Sku","EK"
"68629","5.00"
"68630","8.25"
Numbers are stored with a point as decimal separator.
Script:
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Import-Csv "Offers.csv" |
Select-Object Sku,@{l="MinPrice"; e={[Single]$_.EK * 1.15}},@{l="MaxPrice"; e={[Single]$_.EK * 1.4}} |
Update-TSAmazonListing
Disconnect-TSSession
In this example, the minimum price is set 15% higher than the EK value and the maximum price is 40%.
Note:"Import CSV" reads all fields as strings and the field must be converted to a numeric value for it to be used in calculations. Use "[Single]$_.EK" whenever a point is used as a decimal separator and "[Single]::Parse($_.EK)" if a comma is used as decimal separator.
Create a new product and Amazon listing
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
$product = Create-TSProduct -Sku "abc123" -Condition "New" -WorkflowStep "New / Waiting" -Name "Test Product 1" -UnitsAvailable 1 -ListPrice 19.95 -PriceCategory "Price Hit"
Create-TSAmazonListing -ProductId $product.ID -Asin AB12345678 -Price 14.95 -MinPrice 10.95 -MaxPrice 24.95
Disconnect-TSSession
Upload the new product to Amazon
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Update-TSProduct -Sku "abc123" -WorkflowStep "Available / Online"
Get-TSAmazonListing -Sku "abc123" | Upload-TSAmazonChanges
Disconnect-TSSession
Note: Do not upload changes individually to Amazon but pass to "Upload TSAmazonChanges" a list of modified or new Amazon listing IDs via the PowerShell pipeline. Alternatively, use the command "Upload TSAmazonChanges-All-Available" to transfer changes to Amazon.
Create new products and Amazon listing from a CSV file
In this example, a CSV file with the following columns is imported:
"Sku","Name","UnitsAvailable","Asin","Price","MinPrice","MaxPrice"
"TEST01","Test Product 01","1","AB12345678","4.95","1.95","9.95"
"TEST02","Test Product 02","2","CD12345678","10.95","5.95","19.95"
Numbers are stored with a point as decimal separator.
Script:
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Import-Csv "Offers.csv" |
Foreach-Object { $product = Create-TSProduct -Sku $_.Sku -Name $_.Name -UnitsAvailable $_.UnitsAvailable -Condition "New" -WorkflowStep "Available / Online" -PriceCategory "Price Hit";
Create-TSAmazonListing -ProductId $product.ID -Asin $_.Asin -Price $_.Price -MinPrice $_.MinPrice -MaxPrice $_.MaxPrice } |
Upload-TSAmazonChanges
Disconnect-TSSession
Alternative: (For passing imported values via the PowerShell pipeline - Prerequisite: Column headings correspond to field names and numbers use a point as decimal separator.)
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Import-Csv "Offers.csv" |
Foreach-Object { $line = $_; ($line | Create-TSProduct -Condition "New" -WorkflowStep "Available / Online" -PriceCategory "Price Hit" | %{ $product = $_ } );
($line | Create-TSAmazonListing -ProductId $product.ID) } |
Upload-TSAmazonChanges
Disconnect-TSSession
Note: Products and listings are created and all new products are then transferred together to Amazon. (For this, the output of "Create-TSAmazonListing" is passed to "Upload-TSAmazonChanges".)
Upload all changed prices of available products to Amazon
Add-PSSnapin TotalSellerSnapin
Connect-TSSession loginName password
Upload-TSAmazonChanges -All -Available -Prices
Disconnect-TSSession
Note: The changes to be transferred to Amazon correspond to the changes displayed in the "Update Amazon Listings" dialog box of Total Seller.
Page url: http://www.totalseller.com/help/client/index.html?use_powershell_scripts.htm