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
- 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.
- 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.
- 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.
- 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.
- 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.
- 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, ICN definitions must not exist in [System Setup | System Data | ICN (Intrastat) Codes].
- 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].
- 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).
- 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.
- 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.
- 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.