Customer Import Tool
Overview
In order to import customer and suppliers into Khaos Control, there are specific naming conventions and formats that must be followed so that the system recognises them and therefore the information is imported correctly. If you're not sure what field to use, please email Development.
Preparation
- Make sure that the data you want to import is correct. As this may come from another database you need to ensure that you validate it before attempting to import.
- If there is information in the file that is irrelevant and is not a required field, then you can delete it.
- Excel will strip leading zeros from numeric stock codes and barcodes, so please ensure that you take appropriate formatting steps if you have any leading zeros in these fields.
- Your import file must be saved as TSV/TXT file.
- When creating the import file in Excel, a blank line is added at the bottom. You can open the file with Notepad and amend or import as is. The line will be skipped and the rest of the stock items will be imported.
Usage
Once you have created your import file:
- Open a Customer screen. or
- Open a Supplier screen.
- Press F9 or clicking the down arrow immediately to the right of the
to open the 'Other Actions' menu. - Select Import / Export > Import > Tab Separated Format.
- Select the Customer import file and press OK.
- The Import Customer dialog will show the matching options available:
- Select the matching options as appropriate.
Preview a file import
To test an import file without making changes to the database:
- Open the Import Customer dialog.
- Tick Preview Mode and the file will be checked via the import routine for any errors in the file being imported.
Mapping Table
| Field Name | Mandatory | Description | Field Type | Character Limit |
|---|---|---|---|---|
| COMPANY_CODE |  | The unique reference number for the company record. | Free Text | 30 |
| COMPANY_NAME |  | The name of the company record being setup. For a retail customer this can be left blank and system will create it as: TITLE + FORENAME + SURNAME. | Free Text | 50 |
| COMPCLASS_ID | | The company classification to be used. If this is blank then all customers will be Retail, and all suppliers will be Supplier. | Free Text | 15 |
| ADDRESS:ADDRESS1 | | The first line of the address information. Leaving this blank will cause the import to fail, therefore please use a full stop for blank address line 1. | Free Text | 150 |
| ADDRESS:ADDRESS2 | | The second line of the address information. | Free Text | 150 |
| ADDRESS:LOCALITY | | The third line of the address information, or the locality of the address. | Free Text | 150 |
| ADDRESS:TOWN | | The town of the address. Leaving this blank will cause the import to fail, therefore please use a full stop for blank towns. | Free Text | 35 |
| ADDRESS:COUNTY | | The address county. Note: If your customer is a US customer, please use the 2-character State Code. Please contact KCSL if you require this. | Free Text | 35 |
| ADDRESS:POSTCODE | | The address postcode. Leaving this blank will cause the import to fail, therefore please use a full stop for blank postcodes. | Free Text | 10 |
| ADDRESS:COUNTRY2_CODE | | The 2-character country code for the address. GB for the United Kingdom. If you're using ADDRESS:COUNTRY2_CODE for an address in the United States [i.e. 'US'], you must also include the U.S State in ADDRESS:COUNTY. We recommend specifying the two character state code, e.g. DC, TX, CA, MI etc. | Free Text | 2 |
| ADDRESS:FAX | | A generic address level fax number. | Numeric | 25 |
| ADDRESS:PREF_DELADDRESS | | Whether the address is the preferred delivery address. | Yes/No | 4 |
| ADDRESS:TELEPHONE | | A generic address level telephone number. | Numeric | 25 |
| ADDRESS:EMAIL | | A generic address level email address. | Free Text | 100 |
| CONTACT:TITLE | | The title of the contact. | Free Text | 20 |
| CONTACT:FORENAME | | The forename of the contact. | Free Text | 50 |
| CONTACT:SURNAME | | The surname of the contact. | Free Text | 50 |
| CONTACT:TEL1 | | The contact's telephone number. | Numeric | 30 |
| CONTACT:FAX1 | | The contact's fax number. | Numeric | 30 |
| CONTACT:EMAIL | | The contact's email address. | Free Text | 100 |
| CONTACT:EMAIL_SUBSCRIBER | | Is this contact an email list subscriber. | Yes/No | 4 |
| CONTACT:JOB_TITLE | | The contact's job title. | Free Text | 100 |
| CONTACT:PREF_DELCONTACT | | Whether the contact is the preferred delivery contact. | Yes/No | 4 |
| CONTACT:PREF_INVCONTACT | | Whether the contact is the preferred invoice contact. | Yes/No | 4 |
| CONTACT:CONTACT_NOTE | | A note associated with this contact. | Free Text | 150 |
| CONTACT:DATAPROT_ID | | Data Protection: Our use and any 3rd parties. | Free Text | 15 |
| CONTACT:GENDER | | The gender of the contact. | Free Text | 6 |
| OTHER_REF | | An additional reference that can be populated for the company record being setup. | Free Text | 50 |
| WEB_SITE | | The company's website. | Free Text | 50 |
| COMPANY_NOTE | | A note for this specific company record. | Free Text | 255 |
| COMPANY_STATEMENT_NOTE | | A note which will associate with the company statements. | Free Text | 255 |
| COMPANY_INVOICE_NOTE | | A note which will be added to invoices raised for this company. | Free Text | 255 |
| COMPANY_PICKINGLIST_NOTE | | A note which will added to picking and delivery note reports for this company. | Free Text | 255 |
| BANKACCOUNT_NAME | | The name of the company's bank. | Free Text | 35 |
| BANKACCOUNT_NUMBER | | The bank account number for the company. | Free Text | 15 |
| BANKACCOUNT_CODE | | The sort code for the company. | Free Text | 10 |
| CREDIT_AMOUNT | | The amount of credit on sales to be extended to the company. If a supplier import, this will be your credit limit with this specific supplier. | Numeric | 18 |
| CURRENCY_ID | | The name of the currency to use. £ = Pounds Sterling | Free Text | 10 |
| DATAPROT_ID | | Data Protection: Our use and any 3rd parties. | Free Text | 15 |
| EC_COMPANY | | Companies that are marked as true will have their tax values posted into the EC portion of the tax register and appear in the relevant EC sections on the VAT return. | Yes/No | 4 |
| PROFORMA | | Indicates if the customer is a proforma customer. This is a customer only value, it is not available for suppliers. | Yes/No | 4 |
| TAX_REFERENCE | | The VAT Number for the company. | Free Text | 20 |
| TERMS_ID | | The name of the Terms to use: Invoice Date | Free Text | 10 |
| TERMS_PERIOD | | Related to the terms ID. Usually the number of days/months, depending on your ID selection above. | Numeric | 10 |
| TERMSP_ID | | The name of the Terms to use: Invoice Date | 1-3 | 1 |
| TERMSP_PERIOD | | Related to the terms ID. Usually the number of days/months, depending on your ID selection above. | Numeric | 10 |
| PTYPE_ID | | The default payment type expected for the Company record. Should be presented as the description of the payment type. Cash | Free Text | 10 |
| VEN_REFERENCE | | The company's vendor reference. | Free Text | 20 |
| AGENT_ID | | The agents name. This has to be setup within [ System Data | Agents ] before importing. | Free Text | 15 |
| SUPPLIER | | Controls whether Khaos Control should treat this company record as a supplier or not. | Yes/No | 4 |
| UDA:<NAME> | | Used to define the UDA names and values of a stock item. The <NAME> should be replaced with the name of the UDA, with the values within the stock item rows. Speak to KCSL if you're unsure about this. | Free Text | 30 |
| CREDITREVIEW_ID | | Used to define the review method. The text should match one of the options within [Company | Financial] within the Credit Review Method dropdown. | Free Text | 30 |
| CREDITREVIEW_DATE | | Used to define the date of the last review. This should be in the format of: DD/MM/YYYY. | Date | 10 |
| CREDITREVIEW_USER_ID | | Used to define the Credit Reviewer of the company. This should match the username defined within [System Data | Users] | Free Text | 15 |