Importing customers and suppliers

Customers and suppliers can be created and updated by importing a spreadsheet of data (Excel 97-2003). Data maps can be created and saved for performing different imports. Using an email or code Brightpearl will check for existing records, where no match is found a new record can be created, and where a match is found the record can be updated.

Data maps

Data maps are created in Brightpearl to specify the columns in the spreadsheet to be imported. The following fields must be included:

  • Email or Code or Contact ID

    Once of these fields must be used as the first column in the spreadsheet. This is what Brightpearl will use to search and match to any existing records. These details will need to be unique for each contact. Contact ID is a system reference so can only be used when updating existing records.

  • Name

    At least 1 of - Firstname, Lastname or Company must be included.

Available datamap fields:

Field name Description Required?
Code This is used to record the contact account code. This should be unique to each customer. It can be used for matching contacts during an update via import Use either Code or Email in the first column to create contacts. Contact ID can also be used to update existing contacts
Contact ID This is the ID number assigned to a contact by Brightpearl. It cannot be changed. It is unique to each individual so can be used for matching contacts during an update via import
Email This is the main email address for the contact. It must be unique for each individual. It can be used for matching contacts during an update via import
Email 2 Use this for recording an additional email  
Email 3 Use this for recording an additional email  
Salutation Specify whether the contact is Mr, Mrs, Miss, Ms, Dr, Sir, Rev, Mr. and Mrs  
Firstname Enter the contact's first name At least one of first, last or company name
Surname Enter the contact's second name
Company Enter the name of the contact's company. If multiple contacts exist with the same company name they will automatically be linked as colleagues
Telephone Record the contact's main telephone number  
Telephone 2 Record an additional phone number  
Mobile Record a mobile number  
Fax Record a fax number  
Title Enter the job title for the contact  
Nominal code Enter the nominal/account code number to assign to order rows  
Tax code Enter the tax code that is applicable to the contact  
Bank account Record the contact's bank details  
Bank sort Record the contact's bank details  
Bank name Record the contact's bank details  
Swift code Record the contact's bank details  
IBAN Record the contact's bank details  
Newsletter Enter Yes or No to specify whether the contact would like to receive email newsletters  
Credit limit Enter the credit limit amount  
Credit days Enter the number of days credit  
Credit term type Enter "Net" or "Net EOM"  
Status Enter the contact status that should be assigned (statuses must exist in Brightpearl at Settings > Contacts > Statuses)  
Customer Type (Trade status) Choose whether the contact is a Retail or Trade customer. Customers are marked with an Unknown type by default. This will update the Trade Status within the customer info tab  
Memo Enter a short bit of text about the contact  
Currency Enter the currency code to apply a currency other than the base currency to the contact  
Discount Assign a percentage discount for this contact. The price list assigned will be discounted by this percentage for all orders  
Price group Assign the contact to a particular price list. Enter the price list "code" (not name). Price list codes can be found via Settings > Products / Inventory > Price lists. If this detail is not included, the contact will automatically be assigned to the price list set at Settings > Contacts > Defaults  
Note text Add a note to the contact's timeline using this field.  
Note date Assign a date to the "Note text". Use the format YYYY-MM-DD  
Website Record the contact's web address  
Owner Assign a staff member as the owner of the contact. All orders raised against this contact will inherit this owner  
Street

Enter the default address details

Country must be entered as the country name as seen at Settings > Localization > Countries , and not the code

 
Suburb  
City  
State  
Postcode  
Country  
Lead time Assign a supplier a lead time read more about lead times   
PCF_* Any existing custom fields can be added to a data map. When including custom fields, they will always be titled "PCF_EXAMPLE"  

How to create a data map

  1. Go to Settings > Data / Import, and select the relevant import routine - accounts, products, customers/suppliers or orders (for both sales and purchases).
  2. Click the Add a new data map button.
  3. Enter a name for your new data map.
  4. The left-hand panel defines the data map. Chose fields from the right-hand side and add them to the left-hand side. Ensure they are in the same order as they are in the import file.
  5. Scroll to the bottom of the window and save the data map.

You can export a sample of your data map into Excel to check it over; you can even use it to add your data into the correct format for importing.

Formatting the import file

Import data will need to be cleaned and correctly formatted for Brightpearl to correctly read and create the records. We recommend checking for the following:

  • Duplicates

    Remove any duplicate record from the spreadsheet prior to importing.

  • Date/time formats

    Ensure that date fields are correctly formatted and entered. Brightpearl will only accept text type data fields entered as YYYY-MM-DD or YYYY-MM-DD hh:mm:ss

  • File type

    The file type accepted by Brightpearl is XLS (Excel 97-2003)

  • Column headings

    Column headings will need to use the Brightpearl data map field name.

  • First column hook

    The first column is used as the "hook" to match data in the file to records already in the system. Ensure the first column is always email, code or contact ID.

  • Merged cells

    If the spreadsheet has merged cells this will cause the import to fail.

  • Multiple details recorded in a single cell

    If mulitple details are recorded in a single cell they will need to be split out into separate columns. For example, a full address cannot be recorded in a single cell, it will need to be split into columns: Street, Suburb, City, State, Postcode.

How to import contacts

  1. Go to Settings > Data / Import > Import Customers/Suppliers.
  2. To use one of our pre-configured import routines click the relevant link on the right-hand side. If you have designed your own data map click the Import link next to the relevant one.
  3. Browse for the file you want to import.
  4. Specify what tags you want the contacts to be given.
  5. Tell Brightpearl what to do with contacts that already exist - should they be updated?
  6. Click Upload file.

A message will be displayed telling you if the import was successful, how many contacts were created, updated or skipped. If any errors were encountered they will be listed at the top of the screen, use these to go back and amend either your data map or Excel file and try again.

Dealing with import errors

On attempting the import it is quite possible that you receive a red error message box, this should give you some indication of what happened. Don’t worry, this just means that you might need to make some adjustments as something isn’t quite right in your file. Check through the following:

  • Are there any blank cells in your spreadsheet?

    Brightpearl can’t read blank cells and will reject your import. Either enter data or filter these contacts out and place them in a separate spreadsheet for a separate import.

  • Are there any cells below your data that contain spaces?

    If for some reason cells in the rows below your data think they have data in Brightpearl will try to read and import it. Highlight a number of rows below your data and delete it just to make sure.

  • Do the column headings match (sequence and name) to your data map?

    In your data map listing click the Edit link to make any changes or you can move the columns around in your Excel file. Export a sample of your data map to check the column names.

  • Is your spreadsheet saved as the correct file type?

    It must be saved as an XLS file, which is Excel 97-2003.

What can I do with a customer/supplier import?

You can create and update customer and supplier (vendor) details, including:

  • Name and company
  • Contact details – email addresses, phone numbers, address (default only)
  • Nominal code for sales/purchase orders
  • Tax code
  • Bank details
  • Financial details - currency, price group
  • Discount percentage
  • Credit terms
  • Status
  • Lead source
  • Owner
  • Populate custom fields – create your custom fields first
  • Enter an account memo
  • Add notes to the customer/supplier timeline
  • Specify whether they will or won’t receive email newsletters (bulk emails)

What can't I do with a customer/supplier (vendor) import?

Although you can't import the following data, you can add these details to contacts manually in Brightpearl:

  • Import multiple addresses
  • Add popup messages
  • Set the default payment method
  • Add a tax number, accounts email address
  • Record an eBay or Skype alias
  • Assign to a team
  • Swap the primary contact within a company
Have more questions? Submit a request