Data Export - Column Mapping

Overview
Column mapping is used to set up a link between the values withing Linnworks and the column headers that will be used in the csv file generated using Data Export

Table of Contents

  Define file format

  • Export Headers
    • Export a single row at the top of the file using the values in File Column to act as a header row
  • Delimiter - the character used to separate columns in file
    • comma , (Default)
    • tab \t
    • pipe |
    • colon :
    • semicolon ;
  • Escape
    • quotes " (Default)
    • backslash \

Escape Characters

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

As an example when exporting strings in a csv Linnworks will wrap any strings in "". This is so that the string is read as a single field and not multiple fields when it also contains the delimiter (column seperator). If I set " as my escape character the following will be the result:

  • Original Item title = 12" Wire Pipe Cleaners, Black
  • Exported Item title = "12"" Wire Pipe Cleaners, Black"

If I set \ as my escape character the following will be the result:

  • Original Item title = 12" Wire Pipe Cleaners, Black
  • Exported Item title = "12\" Wire Pipe Cleaners, Black"

If this was not done the sofware reading the file would fail to read the file correctly due to the double quotes after the 12

The result of an example export from Linnworks is as follows:

Order Id,Received date,Item title,Quantity
"200975",2016-01-28 12:18:25,"12"" Wire Pipe Cleaners, Black","1"

Without the double quotes surrounding the strings any software reading the data would have seen 4 header columns and 5 columns for the data due the comma after the word Cleaners in the item title.

 Mapping

The mapping section contains 5 columns as detailed below

Order

  • This defines the sequence the columns will be included in the exported file. Use the up and down arrows to adjust the sequence.

Export

  • This defines if a column is to be included or excluded from the exported file
  • Use the tick box in the header row to either select or deselect all columns to be included or excluded from the exported file
  • Use the tick box on an individual row to either select or deselect the column to be included or excluded from the exported file

Column

  • The name that Linnworks uses to recognise the field internally

File Column

  • The name that will be placed in the header row of the exported file
  • By default this is the same as the Linnworks recognised field name
  • File Column names can be modified by overwriting the value in this text box

Filters

  • Some columns have the option to set a filter
  • Filters can be used to limit / control the data that is included in the export
  • The columns that support filters depend on the selected export Type

Note: Filters can be applied to a column not included (ticked) in the export. Eg: It is possible to filter Stock Location without selecting the column to appear on the export result

Filter Examples

Below are example scenarios showing how filters can be used to control which data is exported from your Linnworks account

ScenarioImplementationResult
We have multiple warehouses, but I need a report that only shows me the stock levels for inventory in my Chichester warehouse
  • Select the filter icon for Stock Location
  • Set the parameter for the filter and click Add
  • A row will be displayed to confirm the filter, click Save
The icon will now be orange to highlight that a filter has been defined along with a description of the filter at the bottom of the column mapping section
We use categories to group our products and are about to have a sale of our products but I need a report to exclude the "Acrylic Paints" and "Miniature" categories as they will not be part of the sale.
  • Select the filter icon for Category
  • Set the parameter to "Acrylic Paint" for the filter and click Add
  • Add additional filtering parameter for "Miniature" and click Save
The icon will now be orange to highlight that a filter has been defined along with a description of the filter at the bottom of the column mapping section
On a Monday morning I need to run a report that shows me the orders that were received the week before last and have now been processed
  • Select the filter icon for Received Date
  • Select Date range from the dropdown menu and select the desired date range
  • Click Add
  • Click Save
The icon will now be orange to highlight that a filter has been defined along with a description of the filter at the bottom of the column mapping section

Please note! Selection of multiple filter parameters needs to be done with care in order to achieve the correct result. When using ambigous text strings, using the Contains operator can have advantages. For instance; using Categories Contains 'Nail' will include any category that has the word nail in it, such as Nail Varnish, Nail Art, Gel Nails etc. This has further implications when using NotContains as it would also exclude unrelated categories. For instance; using Category NotContains 'Nail' to exclude Nail Varnish, Nail Art, Gel Nails would exclude categories such as DIY Screws and Nails.

  Additional Columns

The additional columns button allows you to add other columns to the report with a single click or add expressions to your export

All Extended Properties

When exporting orders (open, processed or cancelled) and inventory, it is possible to automatically add columns for all extended properties, instead of adding each one individually. In order to add these properties, please do the following:

  • Create the import as normal and proceed to the column mapping screen
  • Click the Additional Columns button
  • Select All Extended Properties
  • A message will appear stating how many extended properties will be added. Click Yes to add these
  • When you are happy with the column mapping, either click Next to set a schedule on a recurring export or click Run Now on an ad-hoc export

Expression

Expressions can be added as additional columns designed to output manipulated data based on existing Linnworks information.

Expression Example :

ScenarioImplementationResult
We plan to hold a larger volume of stock in our new warehouse and so would like to know how much space each item would take up to calculate a rough number of units we can hold
  • Click the Expression button
  • Scroll to the botton of the Column Mapping section to see the new row
  • Enter a name that will be used for the Column header in the exported file, eg Volume
  • Select the edit Expression button
  • Use the Columns drop down and the expression editor to enter the required formula, Click Ok when complete
A new row will be added to the bottom of the column mapping section

Single Additional Columns

Under the additional columns menu, there is a list of other columns that can be added to Linnworks. Clicking one of these column headers will open a new screen to customise how the column will be displayed on the report. For instance, clicking Channel Pricing will open a screen requesting a Column Name, which can be anything you wish to identify the column with, the Source and SubSource. The available options will be depend on the option shown.

The following table gives the currently available columns and what reports they are available for:

Column TypeAvailable In
Bin Rack Inventory
Channel Description Inventory
Channel Pricing Inventory
Channel SKU Inventory
Channel Title Inventory
Extended Property Inventory
Open Orders
Processed Orders
Cancelled Orders
Supplier Barcode Inventory
Supplier Code Inventory
Supplier Code 2 Inventory
Supplier known purchase price Inventory
Supplier lead time Inventory
Supplier on hand Inventory
Order Extended property Open Orders
Processed Orders
Cancelled Orders
Order Folder Open Orders
Order Item Option Open Orders
Processed Orders
Cancelled Orders