Expression Samples - Data Import

Overview
Data Import is one of the quickest ways to enter data into Linnworks.net. Expressions can be used to alter or extend the dataset without needing to manually edit the CSV file. This guide will cover pertinent examples of expressions covered in the main expressions guide.

Table of Contents

General

Expressions can be created in data imports, often to manipulate or reformat data given in CSV files. A typical example would be for reformatting barcode entries, as due to features with some spreadsheet packages, they are often prefixed with characters so that they are stored as text.

Examples

Reformatting Barcodes

Often, when creating a CSV file, barcodes are prefixed with characters such as ', so that they are stored as text. When these are imported into Linnworks, the prefix character should be removed, or Linnworks will not recognise scanned barcodes. In the following example, it is assumed that the prefix is a single quote (').

  • Create an inventory import according to our documentation
  • Proceed to the column mapping screen
  • If the file column for barcode numbers has been auto-mapped by Linnworks, un-map it
    • To do this, select a blank entry from the dropdown menu
  • Click the Expression button
  • In the Expression row that was just created, select Barcode Number
  • Click the  icon
  • Enter the following expression and click  OK
    • remove[v{Barcode}, ''']
  • Map any other relevant columns
  • Click  Run Now

The remove[] command accepts 2 arguments, separated by a comma, the first is the file column you wish to work with and the second is the character or string you wish to remove. This second argument must be encapsulated with single quotes, so in the case of wanting to remove the leading single quote, we need to use 3 single quotes.

Setting the Default Postal Service

In this example we will set the Linnworks Postal service based on the items weight. Prepare a CSV file with columns for SKU and weight. Other columns can be added as required. Whilst the Linnworks postal service can be set during CSV preparation, this example can be adapted for other any other LInnworks property that can be imported via CSV files.

  • Create an inventory import according to our documentation
  • Proceed to the column mapping screen
  • Click the Expression button
  • In the Expression row that was just created, select Postal Service
  • Click the  icon
  • Enter the following expression and click  OK
    • iif[v{weight}<1000, "Express 10am", "Express 24hr"]
  • Map any other relevant columns
  • Click  Run Now

The IIF formula can be broken up into three parts; condition, value if true, value if false. In the example given, the condition checks whether SKU weight is under 1kg and sets the postal service to "Express 10am" and if it is heavier than 1kg, the service is set to "Express 24hr".

Settings Stock Levels Based on File Contents

It may be desirable to set a custom stock level based on your suppliers inventory feed. For example, if you are using the Stock Level by Supplier Code, you may not wish to show all of the suppliers stock on your listings, particularly if the stock levels are being updated regularly. In the following example, we want the Linnworks stock level to be the following:

Supplier Stock LevelDesired Linnworks Stock Level
0-2 0
3-200 6
200+ 8
  • Create a Stock Level by Supplier Code import according to our documentation
  • Proceed to the column mapping screen
  • Click the Expression button
  • In the Expression row that was just created, select Stock Level
  • Click the  icon
  • Enter the following expression and click  OK
    • noStock:=0;
      medStock:=6;
      highStock:=8;
      level:=iif[v{OnHand}>2 AND v{OnHand}<201,medStock,highStock];
      level:=iif[v{OnHand}<3,noStock,level];
      level
  • Map any other relevant columns
  • For an Import Now type import, click  Run Now. For a scheduled import either proceed to the schedule or click Save

This expression uses variables to assign the values we want to set in Linnworks and calculate the value which should be displayed. This is done because it is only possible to have a single calculation, such as IIF[v{FileColumn}=0,"Out of Stock", v{FileColumn}], per expression without using variables.

A variable is assigned by using the notation VariableName:=Value;. Several variables can be assigned in the expression. The following table explains the expression line by line:

Expression LineDescription
noStock:=0; The expression is designed to set one of three stock levels, dependent on the level in the file, this is the first value we may want to set.
medStock:=6; This is the second value we may want to set in Linnworks
highStock:=8; This is the third value we may want to set in Linnworks
level:=iif[v{OnHand}>2 AND v{OnHand}<201,medStock,highStock]; This sets a variable called "level". It checks whether the file value is between 3 and 200. If that is true, set the value to 6, if not set the value to 8.
level:=iif[v{OnHand}<3,noStock,level]; This overwrites the "level" variable. If the file value is less than 3, show as 0 stock in Linnworks. If not, keep the level assigned in the previous line
level This line actually sets the Stock Level in Linnworks. In the last 2 lines, we have calculated the value, so no calculation needs to be done on this line.

Further Reading