Customer Import Tool

Customer 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

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

  1. 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.
  2. If there is information in the file that is irrelevant and is not a required field, then you can delete it.
  3. 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.
  4. Your import file must be saved as TSV/TXT file.
  5. 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:

  1. Open a Customer screen (show me how). or
  2. Open a Supplier screen (show me how).
  3. Press F9 or clicking the down arrow immediately to the right of the to open the 'Other Actions' menu.
  4. Select Import / Export > Import > Tab Separated Format.
  5. Select the Customer import file and press OK.
  6. The Import Customer dialog will show the matching options available:
  7. Select the matching options as appropriate.

Preview a file import

To test an import file without making changes to the database:

  1. Open the Import Customer dialog.
  2. Tick Preview Mode and the file will be checked via the import routine for any errors in the file being imported.

Mapping Table


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


Field Name Mandatory Description Field Type Character Limit
COMPANY_CODE red cross The unique reference number for the company record.Free Text30
COMPANY_NAME green tick 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 Text50
COMPCLASS_ID green tick The company classification to be used. If this is blank then all customers will be Retail, and all suppliers will be Supplier.Free Text15
ADDRESS:ADDRESS1 green tick 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 Text150
ADDRESS:ADDRESS2 red cross The second line of the address information.Free Text150
ADDRESS:LOCALITY red cross The third line of the address information, or the locality of the address.Free Text150
ADDRESS:TOWN green tick The town of the address. Leaving this blank will cause the import to fail, therefore please use a full stop for blank towns.Free Text35
ADDRESS:COUNTY red cross 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 Text35
ADDRESS:POSTCODE green tick The address postcode. Leaving this blank will cause the import to fail, therefore please use a full stop for blank postcodes.Free Text10
ADDRESS:COUNTRY2_CODE red cross 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 Text2
ADDRESS:FAX red cross A generic address level fax number.Numeric25
ADDRESS:PREF_DELADDRESS red cross Whether the address is the preferred delivery address.Yes/No4
ADDRESS:TELEPHONE red cross A generic address level telephone number.Numeric25
ADDRESS:EMAIL red cross A generic address level email address.Free Text100
CONTACT:TITLE red cross The title of the contact.Free Text20
CONTACT:FORENAME red cross The forename of the contact.Free Text50
CONTACT:SURNAME green tick The surname of the contact.Free Text50
CONTACT:TEL1 red cross The contact's telephone number.Numeric30
CONTACT:FAX1 red cross The contact's fax number.Numeric30
CONTACT:EMAIL red cross The contact's email address.Free Text100
CONTACT:EMAIL_SUBSCRIBER red cross Is this contact an email list subscriber.Yes/No4
CONTACT:JOB_TITLE red cross The contact's job title.Free Text100
CONTACT:PREF_DELCONTACT red cross Whether the contact is the preferred delivery contact.Yes/No4
CONTACT:PREF_INVCONTACT red cross Whether the contact is the preferred invoice contact.Yes/No4
CONTACT:CONTACT_NOTE red cross A note associated with this contact.Free Text150
CONTACT:DATAPROT_ID red cross Data Protection:

Our use and any 3rd parties.
Our use and selected 3rd parties.
Our use and no 3rd parties.
Our use only.
Do not use.
Deceased.
Note: This should be written exactly how it is written within System Data | Data Protection.

Free Text15
CONTACT:GENDER red cross The gender of the contact.Free Text6
OTHER_REF red cross An additional reference that can be populated for the company record being setup.Free Text50
WEB_SITE red cross The company's website.Free Text50
COMPANY_NOTE red cross A note for this specific company record.Free Text255
COMPANY_STATEMENT_NOTE red cross A note which will associate with the company statements.Free Text255
COMPANY_INVOICE_NOTE red cross A note which will be added to invoices raised for this company.Free Text255
COMPANY_PICKINGLIST_NOTE red cross A note which will added to picking and delivery note reports for this company.Free Text255
BANKACCOUNT_NAME red cross The name of the company's bank.Free Text35
BANKACCOUNT_NUMBER red cross The bank account number for the company.Free Text15
BANKACCOUNT_CODE red cross The sort code for the company.Free Text10
CREDIT_AMOUNT red cross 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.Numeric18
CURRENCY_ID red cross The name of the currency to use.

£ = Pounds Sterling
$ = US Dollar
€ = Euro

Free Text10
DATAPROT_ID red cross Data Protection:

Our use and any 3rd parties.
Our use and selected 3rd parties.
Our use and no 3rd parties.
Our use only.
Do not use.
Deceased.
Note: This should be written exactly how it is written within System Data | Data Protection.

Free Text15
EC_COMPANY red cross 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/No4
PROFORMA red cross Indicates if the customer is a proforma customer. This is a customer only value, it is not available for suppliers.Yes/No4
TAX_REFERENCE red cross The VAT Number for the company.Free Text20
TERMS_ID red cross The name of the Terms to use:

Invoice Date
Month End
Months

Free Text10
TERMS_PERIOD red cross Related to the terms ID. Usually the number of days/months, depending on your ID selection above.Numeric10
TERMSP_ID red cross The name of the Terms to use:

Invoice Date
Month End
Months

1-31
TERMSP_PERIOD red cross Related to the terms ID. Usually the number of days/months, depending on your ID selection above.Numeric10
PTYPE_ID red cross The default payment type expected for the Company record. Should be presented as the description of the payment type.

Cash
Cheque
Card
Account
Voucher
Note: Customers set to Account should have their Credit Terms and Credit Limits assigned, else orders may fall into Terms Hold.

Free Text10
VEN_REFERENCE red cross The company's vendor reference.Free Text20
AGENT_ID red cross The agents name. This has to be setup within [ System Data | Agents ] before importing.Free Text15
SUPPLIER green tick Controls whether Khaos Control should treat this company record as a supplier or not.Yes/No4
UDA:<NAME> red cross 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 Text30


See Also


Did you find this article helpful?