Data Import - Column Mapping

Overview
Column mapping is used to set up a link between the column headers provided in the file being imported to Linnworks and the field in Linnworks that will contain the imported values

Table of Contents

Load sample file

For column mapping to be defined Linnworks requires a sample file to be able to extract a list of the column headers that will be available

..csv

Empty Template

Please Note! When using the Import Once function, the file format functionality is located in the Select File screen  

  Define file format

  • Delimiter - the character used to separate columns in file
    • comma , (Default)
    • tab \t
    • pipe |
    • colon :
    • semicolon ;
  • Escape - the character used to instruct Linnworks to ignore the next character in the file
    • quotes " (Default)
    • backslash \
  • Has Headers - does the file contain column headers or should the import use Column1, Column2, etc.

Escape Characters

An escape character is a character which invokes an alternative interpretation on a subsequent character in a character sequence. What this means is that the next character in the string will be treated differently by the software that is reading and interpreting the file.

As an example when importing strings in a CSV file, if an item title contained a quote mark such as; 50" Television. When the CSV file is constructed with most spreadsheet software packages, the entire string is automatically encapsulated with quote marks; "50" Television". These outer quote marks are required as Linnworks uses them as a guide for what data should be grouped. If this is imported without an escape character, Linnworks will show an error as it would be trying to split the title into two strings; 50 and Television.

Setting an escape character, in this case \, the middle quote can be 'escaped', meaning it would be ignored: "50\" Television".

  • Intended Linnworks Title: 50" Television
  • Required Import Title: "50\" Television"

Alternatively, a quote mark can be used as an escape character:

  • Intended Linnworks Title: 50" Television
  • Required Import Title: "50"" Television"

Please remember to select the appropriate escape character for the format of your import file from the options at the top of the column mapping screen.

  Loading the sample file

When you have set your file location, clicking Next will automatically load that file for column mapping as long as the file is available.

  • Load - Use a copy of the live file that was selected in the File Location settings
    • This would normally be the preferred method
  • Local Template - Upload a copy from a location on your Pc / network
    • A browse dialog will be displayed
    • Select the required file and click Open
  • Choose File from Url - Upload a copy from a web URL location
    • A dialog will be displayed
    • Enter the full URL for the file and click Accept

Mapping

Once a sample file is loaded the column headers from the file will be displayed below the heading File Column 

Automatic Mapping

  • In the case of the column header from the file exactly matching the Linnworks field name this will be automatically mapped and this is displayed in the Map to column

Manual Mapping

  • Where there is no automatic mapping or if the mapping needs to change this is possible through manual mapping
  • Select the drop box in the Map to a column for the required File Column
  • The available unmapped Linnworks fields will be displayed
  • Select the required Linnworks field name from the list

Excluding Mapping

  • When no column mapping is set the File Column will be excluded from the import
  • To remove an automatically or previously mapped column from future imports select the drop box in the Map to column for the required File Column
  • Scroll to the top
  • Select the empty row

Default Value

  • Use this option to force a specific value to imported when the CSV file does not contain the data
  • The same value will be imported for all rows included in the CSV file

Example: when running an Order Import the file does not contain a value for SubSource

    • Click the + Default Value button
    • Scroll to the bottom of the screen to see the new row
    • Select SubSource from the drop-down list
    • Enter a value into the field displaying Subsource default value, e.g.: GroupOn
    • All orders imported through this will now have the SubSource of GroupOn

Expressions

  • Use this option to use a calculated value instead of working with the raw value in the CSV file or a Default Value
  • What is imported will depend on the result of the expression used

Example: when running an Inventory Import the file contains Level and Purchase Price, but not Stock Value

  • Click the + Expression button
  • Scroll to the bottom of the screen to see the new row
  • Select Stock Value from the drop-down list
  • Click the Expression Edit button
  • Select Quantity from the Columns drop-down list to add the parameter to the expression
    • The drop-down list contains all column headers from the CSV file along with any Default Values or Expressions already defined
  • Enter an * into the expression editor window
  • Select Purchase Price from the Columns drop-down list to add the parameter to the express
  • Click OK to save the expression

Example: when running an Inventory Import the file contains words (In Stock / Low Stock / Out of Stock) describing the status of the stock level instead of providing an actual stock level

  • This example shows a more complex scenario and makes use of Variables within the Expression and Functions
  • There is no fixed method for inserting a Variable into the Expression, and they are manually entered
  • Functions are inserted in the same way as Column parameters, just using the Functions drop down instead
  • Below is the example Expression so that the result will equate to
    • In Stock = 50
    • Low Stock = 40
    • Out of Stock = 0
Expression 
low:=40;
instock:=50;
level:= iif[v{Stock Level}="Low stock",low,-1];
level:= iif[v{Stock Level}="Out of stock",0,level];
iif[level=-1,instock,level]

Expression Explained 
Expression Description
low:=40; Define a variable called low and set the value = 40
instock:=50; Define a variable called instock and set the value = 50
level:= iif[v{Stock Level}="Low stock",low,-1]; Define a variable called level and set the value to the variable low if the file contains "Low Stock", else set the value = -1
level:= iif[v{Stock Level}="Out of stock",0,level]; Update the variable level to 0 if the file contains "Out of Stock", else keep the value as level
iif[level=-1,instock,level] If level = -1 import the value from the variable instock, else import the value from the variable level