011234: Stock Import Tool
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.
The Import Tool allows the import of all fields, including the following:
- The import requires files to be saved and imported as tab-separated values, which will produce a .txt file,
- The Stock Types must already exist in Khaos Control.
No additional or on-going configuration is required.
- Open a Stock screen .
- Press to open the 'Other Actions' menu. or clicking the down arrow immediately to the right of the
- Select Import Stock Items.
- Select a .txt file containing the stock information to be imported. This file should be in a tab-separated format.
- 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.
- If a stock item already exists, it will be updated according to the values in the import file.
- If a stock item does not already exist, it will be created.
- 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:
|SHORT_DESC||If not set, this will default to (empty) for a new item.|
|STOCK_CODE (Required)||The Stock Code in |
|STOCK_TYPE1 (Required)||The Level 1 Stock Type for the item. Should match the Description in |
|STOCK_TYPE2 (Required)||The Level 2 Stock Type for the item, shown in the Stock Type in |
The following fields can be used when required for the import:
|AVERAGE_WEIGHT||This updates |
|BARCODE:BARCODE||The Barcode in the Stock Barcode dialog, opened using the barcode button in |
|BARCODE:TYPE||The Type in the Stock Barcode dialog, opened using the barcode button in |
|BOXES_PER_PALLET||Outers per Pallet|
|BUY_PRICE||The Buy Price in |
|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 |
|DROP_SHIP||Will update the field |
|INNERS_PER_OUTER||Inners per Outer|
|LONG_DESC||The Long Description in |
|MANUFACTURER||The Manufacturer to set in |
|Option:*||Will update the |
|OTHER_REF||The Other Ref in |
|POSTAGE_WEIGHT||The Postage Weight in the |
|PURCHASE_MULTIPLE||The Reorder Multiple in the |
|SELL_PRICE||The Sell Price in |
|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 |
|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 |
|Supplier:COMPANY_CODE||The URN of the supplier to add to |
|Supplier:COMPANY_NAME||The name of the supplier to add to the |
|Supplier:DEFAULT_SUPPLIER (-1 for on, 0 for off)||The Preferred tickbox for the new line added to |
|Supplier:DISCOUNT||The Discount % for the new line added to |
|Supplier:PURCHASE_COST||The Buy Price for the new line added to |
|Supplier:REORDER_QTY||The Reorder Qty for the new line added to |
|Supplier:SUPPLIER_REF||The Supplier Ref for the new line added to |
|TAX_CODE||The tax code of the tax rate to apply to the item, as specified in the Code column of |
|UDA:*||Allows specification of one or more UDA against the item as per |
|WEB||Will update the field |
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.
|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.
Notes / Caveats
- 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
- 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.)
- 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.
- 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.
- 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.
- 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.
- The import allows both the TAX_RATE and TAX_CODE to be set. When both are used the TAX_CODE will take precedence.
- 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.
- 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.
- 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:
- 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 ].
- 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.)
- 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.
- When Creating an SCS item structure in a text file each individual variation must have its own item line.
- 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.