Stock Data Import Map

Information

This guide contains the file requirements for importing stock data into Khaos Control Cloud (KCC). This article only covers simple SKUs, a separate guide is available for SCS items (variations) here, Stock Data Import (SCS). Detailed notes and caveats can be found at the bottom of the grid below.
All import files must be saved as a Text (Tab Delimited) file in order to import them to KCC. If this is not an option in your spreadsheet application you can usually copy and paste the entire spreadsheet into a text document which can then be imported.

MANDATORY FIELDS

MANDATORY must be filled in or the import will fail.
If you need to run an import more than once then all mandatory fields MUST be completed for each import.

All YES / NO fields should be done as Boolean values. YES = -1, NO = 0


FIELD NAME
(Case Sensitive)
MANDATORY FIELD?
DESCRIPTION 
FIELD TYPE
FIELD LENGTH
CONTROLLED
YES
Is the item stock controlled?
YES / NO
4
STOCK_CODE
YES
The unique stock code of the item.
FREE TEXT
30
SHORT_DESC
YES
The short description of the stock item. This appears on picking sheets, invoices etc.
FREE TEXT
250
STOCK_TYPE1
YES
Is the top level of stock types structure. No physical stock is held under this level.
FREE TEXT
25
STOCK_TYPE2
YES
Main stock type/level - this level is where physical stock items are held and is the one used to set nominal mapping and filter on sales reports. It is the most important level of categorisation.
THIS IS THE MOST IMPORTANT STOCK TYPE.
FREE TEXT
100
BUY_PRICE
YES
The default cost (in the system's base currency) of buying the product from a supplier (can be net or gross, but this decision will affect how your system functions) as held against the Stock Item. The Stock Buy Price is also used as the Stock Value (required for Cost of Sale analysis)
NUMBER
20
SELL_PRICE
YES
The default selling price to the customer (can be net or gross, but this decision will affect how your system functions)
NUMBER
20
TAX_CODE
YES
The integer code representing the tax rate of the product: 1 = Standard 20% (default) / 2 = Zero 0% / 3 = Exempt / 4 = Reduced 5% / 5 = Old Rate (pre 1st Dec 2008) 17.5% / 6 = 2009 Rate (01-Dec-2008 to 31-Dec-2009) 15%
NUMBER
4
ICN:CNCODE_ID
NO
This field is used to attach the ICN (Intrastat) code to the stockcode. Is is essential you follow the guide here, ICN Bulk Update when using this field. This will appear on Customs Invoices.
FREE TEXT
10
Harmonisation:Code
NO
This field is used to attach Harmonisation Codes to the stockcode. Is is essential you follow the guide here, Harmonisation Code Bulk Update when using this field. This will appear on Customs Invoices.
FREE TEXT
10
Harmonisation:SHORT_DESC
NO
The description of the Harmonisation Code (above) used by this stockcode. This field is MANDATORY if importing a Harmonisation Code.
FREE TEXT
255
SUP_UNIT_VALUE
NO
This field is used to specify the supplementary unit value for this stockcode. This will appear on Customs Invoices.
NUMBER
10
COUNTRYMANUF_ID
NO
This is the name of the Country of Manufacture for the product. The value entered MUST match the name of the country as listed in [System Data | Countries]. This will appear on Customs Invoices.
FREE TEXT
50
NET_WEIGHT
NO
This is the NET Weight of the SKU excluding packaging. The net weight is displayed on Customs Invoices as per international trade guidelines.
NUMBER
4
STOCK_TYPE3
NO
Optional further type/level for use in further sales analysis, and product locating (if using 4 levels, this level acts as a mid-type header level an stock cannot be held under this level, if using 3 levels in your stock structure use stock types 1, 2 & 4).
FREE TEXT
100
STOCK_TYPE4
NO
Optional further type/category for use in further sales analysis, and product locating (if using 3 levels in your stock structure use stock_types 1, 2 & 4).
FREE TEXT
100
LONG_DESC
NO
The long description for the product, used as a telesales information and/or web site long description.
FREE TEXT
2000
Image:FILE_NAME
NO
The URL for the image. The image location MUST be a secure URL not a network/local path (https) - if you do not have a secure URL this may not work.
Due to security issues within the browser these images will NOT be stored by KCC but will be shown from the URL provided.
FREE TEXT
255
Image:IMAGE_NAME
NO
Used only as a reference. e.g. "Main Image" or "Thumbnail"
FREE TEXT
200
Image:IMAGE_DESC
NO
Used only as a reference where the user can hold a description of the image. e.g. Front view of <item name> or Side view of <item name>. 
FREE TEXT
200
Image:SORT_ORDER
NO
Controls the order in which the image should show, when there is more than one image for the same product.
NUMBER
4
Supplier:COMPANY_CODE
NO
The URN (company code) for the supplier of this item (if undefined there will be no stock / supplier links). Must match exactly on the supplier URN, which must already exist within the database.
FREE TEXT
50
Supplier:COMPANY_NAME
NO
The Supplier Name of the supplier of this item (if undefined there will be no stock / supplier links). Must match the supplier name exactly and must already exist within the database.

We recommend you use the COMPANY_CODE rather than this field.
FREE TEXT
100
Supplier:SUPPLIER_REF
NO
Having linked a stock item with a supplier, this would be the supplier’s stock code (SKU) for this product.
FREE TEXT
20
Supplier:PURCHASE_COST
NO
Having linked a Stock Item with a Supplier, this would be the cost of buying the product from the supplier in the currency set against the supplier.
NUMBER
20
Supplier:DISCOUNT
NO
Having linked a Stock Item with a Supplier, this would be the discount % applicable to this Stock Item from this Supplier.
NUMBER
20
Supplier:REORDER_QTY
NO
Having linked a Stock Item with a Supplier, this would be the default quantity to reorder of this Stock Item from this Supplier.
NUMBER
20
Supplier:QTY_AVAIL
NO
This field is used to include supplier stock levels on your channels, if enabled. It does not auto update.
NUMBER
20
Supplier:DEFAULT_SUPPLIER 
NO
Indicates whether the supplier is the default / primary supplier of this stock item. This should be -1 for true and 0 for false.
There should only be 1 default supplier per stock item.
YES / NO
4
AVAILABILITY
NO
Free text field that usually denotes a description of why a stock item is not available (e.g. "Dock strike, shipments late"), but it can be used for other purposes.
FREE TEXT
50
PURCHASE_MULTIPLE
NO
When ordering from supplier this "Reorder Multiple" can be used to round quantities to the nearest pack size (e.g. eggs may be sold individually but are purchased in boxes of 6).
NUMBER
20
REORDER_QTY
NO
Re-order Quantity - The base re-order quantity to be used.
NUMBER
20
AVERAGE_WEIGHT
NO
Number only that can be in grams or kilograms (consistently), used to determine the average weight of sales orders.
NUMBER
20
HEIGHT
NO
Height of the stock item.
NUMBER
20
WIDTH
NO
Width of the stock item.
NUMBER
20
DEPTH
NO
Depth of the stock item.
NUMBER
20
REORDER_LEVEL
NO
Quantity - Used to show when a stock item's stock level has fallen below the safe level.
NUMBER
20
MIN_LEVEL
NO
Quantity - Used to show when a stock item's stock level has fallen below the minimum level
NUMBER
20
OTHER_REF
NO
Commonly used to store the bar code, if only referencing one barcode, but could be another reference for the stock item, perhaps a product code that links to your web site.
FREE TEXT
40
MANUFACTURER
NO
Note: this is not the supplier (although it could be). This value is used for reference only on the stock properties form.
FREE TEXT
100
LEAD_TIME
NO
The number of days this item usually takes to come from your supplier.
NUMBER
4
PICKING_LOCATION
NO
This is a free text field to describe the location of the item.
FREE TEXT
30
DROP_SHIP
NO
Is the item drop shipped (i.e. despatched direct from supplier?)
YES / NO
4
Barcode:Type
NO
This is the type of barcode to be used in the barcode table. e.g. EAN13, EAN8, CODE39. Is also used for channel listings to store Amazon SKU and ASIN.
FREE TEXT
100
Barcode:Barcode
NO
The actual barcode, Amazon SKU or ASIN.
FREE TEXT
50
STOCK_VALUE
NO
The value of this stock item
NUMBER
20
PICKING_ORDER
NO
This is a text field which controls the order of items on a picking report. Format example: 000, 001, 002, etc. In the absence of picking order, picking location will be used to order the report.
FREE TEXT
5
DISCONTINUED
NO
This field, when used, can set the stock item as discontinued. To mark an active item as discontinued, enter a -1 in this field. To mark a discontinued as active, enter a 0.
YES / NO
2
RUN_TO_ZERO
NO
Use this field to mark a stock item as 'Run to Zero'. Enter a -1 to set a stock item as Run To Zero.
YES / NO
2
WEB
NO
Will update the Publish on Web field for the stock item. -1 to enable, 0 to disable.
YES / NO
2
UDA:*
NO
Allows specification of one or more UDAs against the item as per [System Setup | UDAs]. Each UDA should be a separate column with the column name UDA:*, replacing the asterisk with the name of the UDA.
FREE TEXT
50
Notes & Caveats
  1. If any of the Supplier: columns are present, only one of the Supplier:COMPANY_CODE or Supplier:COMPANY_NAME fields is required. If used, the Supplier:COMPANY_CODE or Supplier:COMPANY_NAME columns must be to 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.
  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 and reported in the error messages grid at the end of the import. 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 the furthest right of the suppliers on your import file. It is recommended that if both the Supplier:COMPANY_CODE and Supplier:COMPANY_NAME are specified, these should be for the same company.
  5. 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 Cloud to import more than one such value.
  6. 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.
  7. When importing stock items with ICN information, ICN definitions must not exist in [System Setup | System Data | ICN (Intrastat) Codes]
  8. Some fields need to be set up in Khaos Control Cloud before they can be successfully imported, for example, Stock Barcode Types must exist in [System Setup | System Data | Stock Barcode Types].
  9. The Manufacturer field will assign a manufacturer from [System Setup | System Data | Manufacturers]. If no match is found, a new manufacturer will be created (and assigned).
  10. UDAs can be imported. The part after ‘UDA:’ must exactly match, including whitespace, the name of a UDA within [System Setup | UDAs] in order to be imported successfully; otherwise it will be skipped.
  11. 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 list values must exist against the list within [System Setup | UDAs] before import.
  12. 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.

Downloads

Did you find this article helpful?