011234: Stock Import Tool

Stock Import Tool is not enabled by default.

Please email Development for more information.
Note: there will be additional costs involved if this option is enabled.


Overview

The stock import tool allows the import of stock into the system and also allows stock items already in the system to have their options updated.

WARNING icon
WARNING: The Stock Import Tool will enter any values that are in the import file into the relevant fields only validating that the data is of the correct length and type.
Care needs to be taken as data can be imported incorrectly.

Specification

The Import Tool allows the import of all fields, including the following:

  • AVAILABILITY
  • AVERAGE_WEIGHT
  • Barcode:Barcode
  • Barcode:Type
  • BUY_PRICE
  • CONTROLLED
  • DEPTH
  • DISCONTINUED
  • DROP_SHIP
  • EPOS_DESC
  • HEIGHT
  • IMAGE:FILE_NAME
  • IMAGE:IMAGE_NAME
  • IMAGE:IMAGE_DESC
  • IMAGE:IMAGE_ORDER
  • LEADTIME
  • LONG_DESC
  • MANUFACTURER
  • MIN_LEVEL
  • OTHER_REF
  • PICKING_LOCATION
  • POSTAGE_WEIGHT
  • PURCHASE_MULTIPLE
  • REORDER_LEVEL
  • REORDER_QTY
  • REWARD_POINTS
  • RUN_TO_ZERO
  • SELL_PRICE
  • STOCK_CODE
  • SHORT_DESC
  • STOCK_TYPE1
  • STOCK_TYPE2
  • STOCK_TYPE3
  • STOCK_TYPE4
  • Supplier:COMPANY_CODE
  • Supplier:COMPANY_NAME
  • Supplier:DISCOUNT
  • Supplier:DEFAULT_SUPPLIER
  • Supplier:PURCHASE_COST
  • Supplier:REORDER_QTY
  • Supplier:SUPPLIER_REF
  • TAX_CODE
  • TAX_RATE
  • WEB
  • WIDTH

Notes/Caveats

  1. The import requires files to be saved and imported as tab-separated values, which will produce a .txt file,
  2. The Stock Types must already exist in Khaos Control.

Configuration

No additional or on-going configuration is required.

Usage

  1. Open a Stock screen (show me how).
  2. Press F9 or clicking the down arrow immediately to the right of the The green circle with a white cross icon on the button bar opens the "Other Actions" menu to open the 'Other Actions' menu.
  3. Select Import Stock Items.
  4. Select a .txt file containing the stock information to be imported. This file should be in a tab-separated format.
  5. A message will appear detailing how many item lines were imported or updated, how many were skipped, and errors that were encountered during the process.
    1. If a stock item already exists, it will be updated according to the values in the import file.
    2. If a stock item does not already exist, it will be created.

Notes:

  • The Stock Types must already exist.
  • Care needs to be taken when there are spaces in the Stock Types in the file for example:
    • (space)Breakfast(space)table is the same as Breakfast(space)table
    • Breakfast(space)(space)table is seen as unique.
  • When creating the import file using Excel, a blank line is added to the end, you can open the file in Notepad and amend or the system will still import all the good lines and will just report on this line being skipped.

Examples of using the Stock Import Tool

See Stock Import Tool - Examples for example files for importing stock items for the following scenarios:

  • To update just the buy and sell prices.
  • To add a new supplier, if we get a new supplier for a range of our products.
  • To populate the dimensions and weights of a product range.
  • To discontinue a range of products (could also be used with RUN_TO_ZERO should there be active stock).
  • Import a new SCS child item, if we provide a pre-existing SCS parent Stock Code as the STOCK_CODE then we will add a new SCS Child to that Parent's configuration, if the parent doesn't exist it will be created, the parent should not be provided as a separate line within the import.
  • Adding multiple barcode types to a Stock Code.

Guide to a Selection of the Fields

Below is a guide to a selection of fields. The following fields are mandatory or commonly included:

Column name
Description
SHORT_DESC If not set, this will default to (empty) for a new item.
STOCK_CODE  (Required) The Stock Code in [ Stock | Detail | Properties ].
STOCK_TYPE1  (Required) The Level 1 Stock Type for the item. Should match the Description in [ System Data | Stock - Level 1: Types ]. If not set, this will default to System & Misc Types for a new item.
STOCK_TYPE2  (Required) The Level 2 Stock Type for the item, shown in the Stock Type in [ Stock | Detail | Properties ]. Should match the Description in [ System Data | Stock - Level 2: Stock Types ]. If not set, will default to System & Miscellaneous for a new item.


The following fields can be used when required for the import:

Column name
Description
AVERAGE_WEIGHT This updates [ Stock | Detail | Properties ] > Avg Weight
BARCODE:BARCODE The Barcode in the Stock Barcode dialog, opened using the barcode button in [ Stock | Detail | Properties ].
BARCODE:TYPE The Type in the Stock Barcode dialog, opened using the barcode button in [ Stock | Detail | Properties ] . This should match the Description column in [ System Data | Stock - Stock Barcode Type ], or be left blank.
BOXES_PER_PALLET Outers per Pallet
BUY_PRICE The Buy Price in [ Stock | Detail | Properties ].
CARTON_QTY item per Inner
CONTROLLED (-1 for on, 0 for off) Determines whether the item will have the Stock Controlled option ticked in the [ Stock | Options ] tab. If not specified for a new item, this will default to -1 (on).
DEPTH Populates [ Stock | Detail | Properties ] > Dimensions / Other > D.
DROP_SHIP Will update the field [ Stock | Options ] > Drop-ship item.
HEIGHT Populates [ Stock | Detail | Properties ] > Dimensions / Other > H.
IMAGE:FILE_NAME Populates [ Stock | Detail | Detail | Telesales / Internet | Primary ] > Images > File Name.
IMAGE:IMAGE_DESC Populates [ Stock | Detail | Detail | Telesales / Internet | Primary ] > Images > Description.
IMAGE:IMAGE_ORDER Populates [ Stock | Detail | Detail | Telesales / Internet | Primary ] > Images > Ord. Note that due to multi-threading the actual Order may be different.
IMAGE:IMAGE_NAME Populates [ Stock | Detail | Detail | Telesales / Internet | Primary ] > Images > Name.
INNERS_PER_OUTER Inners per Outer
LONG_DESC The Long Description in [ Stock | Detail | Detail | Telesales / Internet | Primary ].
MANUFACTURER The Manufacturer to set in [ Stock | Detail | Properties ] . This must exactly match the Company Name in the [ System Data | Stock - Manufacturers ] grid.
Option:* Will update the [ Stock | Options ] , where the Options and Custom Options have been whitelisted for import. The part after ‘Option:’ indicated by the asterisk, must exactly match, including whitespaces, the name of a Custom Stock Option.
OTHER_REF The Other Ref in [ Stock | Detail | Properties ].
POSTAGE_WEIGHT The Postage Weight in the [ Stock | Detail | Properties ] tab. If this is set, it will be used preferentially over the Avg Weight.
PURCHASE_MULTIPLE The Reorder Multiple in the [ Stock | Detail | Properties ] tab.
REWARD_POINTS Populates [ Stock | Detail | Properties ] > Pricing > Reward Pts
SELL_PRICE The Sell Price in [ Stock | Detail | Properties ].
STOCK_TYPE3 (If set, the STOCK_TYPE2 and STOCK_TYPE1 must be set) The Level 3 Stock Type for the item. Should match the Description in [ System Data | Stock - Level 3: Mid Type ].
STOCK_TYPE4 (If set, the STOCK_TYPE2 and STOCK_TYPE1 must be set) The Level 4 Stock Type for the item shown in the Sub Type in [ Stock | Detail | Properties ]. Should match the Description in [ System Data | Stock - Level 4: Sub Type ].
Supplier:COMPANY_CODE The URN of the supplier to add to [ Stock | Detail | Suppliers ].
Supplier:COMPANY_NAME The name of the supplier to add to the [ Stock | Detail | Suppliers ] grid.
Supplier:DEFAULT_SUPPLIER (-1 for on, 0 for off) The Preferred tickbox for the new line added to [ Stock | Detail | Suppliers ] when the Supplier:COMPANY_CODE is set.
Supplier:DISCOUNT The Discount % for the new line added to [ Stock | Detail | Suppliers ] when the Supplier:COMPANY_CODE is set.
Supplier:PURCHASE_COST The Buy Price for the new line added to [ Stock | Detail | Suppliers ] when the Supplier:COMPANY_CODE is set.
Supplier:REORDER_QTY The Reorder Qty for the new line added to [ Stock | Detail | Suppliers ] when the Supplier:COMPANY_CODE is set.
Supplier:SUPPLIER_REF The Supplier Ref for the new line added to [ Stock | Detail | Suppliers ] when the Supplier:COMPANY_CODE is set.
TAX_CODE The tax code of the tax rate to apply to the item, as specified in the Code column of [ System Data | Accounts - Tax Rates ]. If not specified for a new item, it will default to the Standard tax rate.
UDA:* Allows specification of one or more UDA against the item as per  Stock | Detail | Detail | Telesales / Internet | Secondary  ]. Each UDA should be a separate column with the column name UDA:[Name of the UDA], indicated by the asterisk,.
WEB Will update the field [ Stock | Options ] > Publish on Web
WIDTH Populates [ Stock | Detail | Properties ] > Dimensions / Other > W.

SCS Items

The Import file can also contain the structure for SCS items that can be imported via the same option. If you're importing the SCS children you don't need to import the parent as the system will assume the parent from the children.

Column name
Description
SCS_CAPTION1  (Required) This will populate the name for the first element in the SCS screen. (Figure 3)
SCS_CAPTION2, SCS_CAPTION3, SCS_CAPTION4 These are treated the same as SCS_CAPTION1 but will populate the Second Element, Third Element and Fourth Element based on the number.
SCS_DESC1  (Required) This will populate the Value (description) in the SCS template, but does not auto generate the Child descriptions. (Figure 2)
SCS_DESC2, SCS_DESC3, SCS_DESC4 These are treated the same as SCS_DESC1 but will populate the Second Element, Third Element and Fourth Element based on the number.
SCS_DESC_TEMPLATE This will override the SHORT_DESC and subsequent SCS_DESC# elements, by populating this field as the SCS Item’s description. If it is not included in the file, or left blank, the description will default to the SHORT_DESC and SCS_DESC# elements. If the stock item is not an SCS Item, it will be ignored.
SCS_SK1  (Required) Will populate the first element of the SCS screen. (Figure 1)
SCS_SK2, SCS_SK3, SCS_SK4 These are treated the same as SCS_SK1 but will populate the Second Element, Third Element and Fourth Element based on the number.
SCS_TEMPLATE This will set the delimiter in the Stock Code between the Header Item's STOCK_CODE and subsequent SCS_SK# elements. For example, when set as /, the Stock Code generated would be JACK/MED/BLU/ZIP for Jacket/medium/blue/zipped.
Note: if the column is omitted or left blank, then the default of - will be used.

Figure 1

Stock Import Tool figure 1


Figure 2

Stock Import Tool figure 2

Figure 3

Stock Import Tool figure 3

Notes / Caveats

  1. If any of the Supplier: columns are present, either the Supplier:COMPANY_CODE or Supplier:COMPANY_NAME is required. In this case, the Supplier:COMPANY_CODE or Supplier:COMPANY_NAME must be on the left of any other Supplier: columns
  2. If the STOCK_CODE specified matches an existing item, the system will attempt to update that item. If not, a new item will be created with that stock code. STOCK_CODE must be specified (with the possible exception STOCK_ID is used (as a matching field only.)
  3. The STOCK_CODE for each line must be unique; if the same file contains multiple lines with the same stock code, the second and subsequent lines will be skipped. To import multiple barcodes or suppliers, those sets of columns may be specified multiple times.
  4. It is possible to specify a different Supplier:COMPANY_NAME than the Supplier:COMPANY_CODE would indicate. In this case, any other supplier settings will update only one of the suppliers, depending on the column ordering. It is recommended that if both the Supplier:COMPANY_CODE and Supplier:COMPANY_NAME are specified, these should be for the same company.
  5. Custom Options and UDAs can be imported. The part after ‘Option:’ and ‘UDA:’ must exactly match, including enclosed whitespaces, the name of a Custom Stock Option or UDA in order to be imported successfully; otherwise it will be skipped and a warning will be raised as normal. White spaces will be stripped where they precede or succeed the UDA name.
  6. Where a field has a colon in it (e.g. Supplier:COMPANY_CODE, Barcode:Type, etc) this indicates the field can be entered multiple times within the dataset provided to Khaos Control to import more than one such value. The order of these fields is important. So, for example, if you imported a Barcode:Type field before a Barcode:Barcode field and the type matched a value already against the Stock Code then the Barcode:Barcode value would be updated. If you reversed the order (the Barcode:Barcode field before the Barcode:Type) and the Barcode:Barcode matched a value against the Stock Code then the Type of that Barcode would be updated. Note: UDA:* is an exception to this. A UDA List should be specified by specifying the UDA once, then for the List values, specify any values to be included separating by commas. For instance, column header – UDA:Test_List, with values – OptionA,OptionC,OptionD. If there is a whitespace after the comma, it will be stripped.
  7. The import allows both the TAX_RATE and TAX_CODE to be set. When both are used the TAX_CODE will take precedence.
  8. When STOCK_TYPE2 is not set a default type of System & Miscellaneous will be set. STOCK_TYPE1 should be set as well as STOCK_TYPE2, if used.
  9. Setting a new combination of Level 1 Stock Type / Level 2 Stock Type or Level 3 Stock Type / Level 4 Stock Type, if the combination does not already exist within System Data it will be created. The corresponding values will also be set against the imported/updated stock items.
  10. When importing stock items with ICN information, the system will create new ICN definitions where the combination supplied does not already exist. The fields to use for ICN information are:
    CNCODE_ID
    ICN:SHORT_DESC
    ICN:SUP_UNIT
  11. Some fields need to be set up in Khaos before they can be successfully imported, for example Stock Bar Code Type should be set up in [ System Data | Stock - Stock Barcode Type ].
  12. Manufacturer field will assign a manufacturer from [ System Data | Stock - Manufacturers ]. if a match is found on Company Name, to the field in [ Stock | Detail | Properties ] > Manufacturer. If no match is found, a new manufacturer is created (and assigned.)
  13. If there is a blank line at the end of the file, this will be reported as a skipped line, that does not contain a full set of fields.
  14. When Creating an SCS item structure in a text file each individual variation must have its own item line.
  15. UDAs can be of any of the types in the system. If the UDA doesn't exist the importer will create a new Text type UDA so if other UDAs are required that are not of type Text then these should be setup in the system beforehand. The non-text UDAs require special formatting:
    • Choice: a singular selection from a list, the value MUST be exactly the same as setup in the system.
    • List: multiple selection from a list, the values used MUST be exactly the same as setup in the system, but they MUST be comma separated in the same cell.
    • Date: a specific date, in the format dd/mm/yyyy.
    • Integer: whole number only.
    • Number: number with decimals.
    • Yes/No: Yes (-1)/No (0) toggle.



See Also

Visit KhaosWikiX at https://support.khaoscontrol.com/wiki_kcx/ to see the following:

Contact the Khaos Team