Importing customers and vendors

Customers and vendors (suppliers) can be created and updated by importing a spreadsheet of data (Excel 97-2003).

Data maps are created in Brightpearl to match the columns on your import, and you can save different data maps for performing different imports.

Brightpearl checks for existing records using an email address or a contact code. If no match is found, a new record can be created, and if a match is found, the existing record can be updated.

Importing is a versatile tool which can be used to quickly update several contacts at once - for example, you can easily tag contacts in bulk with just a list of email addresses.

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

    One 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 one of the following must be included:

    • First name

    • Last name

    • Company

Available data map 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

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.

Contact ID can also be used to update existing contacts
Email address

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.

Use either Code or Email in the first column to create contacts.
Email 2 Use this for recording an additional email.  
Email 3 Use this for recording an additional email.  
Salutation Specify the contact's salutation (e.g. Mr, Mrs).  
First name Enter the contact's first name. At least one of first, last or company name
Last name Enter the contact's last 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 contact's job title.  
Nominal code Enter the nominal/account code number to set on the contact's order rows.  
Tax code Enter the tax code that is applicable to the contact.  
Bank account Record the contact's bank details.  
Bank sort  
Bank name  
Swift code  
IBAN  
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 either:

  • Net
  • Net EOM

Learn more about credit terms here.

 
Status Enter the contact status that should be assigned. You can create new statuses using the import by checking the "Create missing statuses" checkbox on the import page.  
Customer Type (Trade status)

Choose whether the contact is a Retail or Trade customer to update the "Trade status" on the contact record.

Customers are marked with an Unknown type by default. 

 
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.

Learn more about discounts here.

 
Price group

Assign the contact to a particular price list. Enter the price list "code" (not name).

Price list codes can be found under 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 website address.  
Owner

Assign a staff member as the owner of the contact.

If "Auto-assign sale to contact owner" is set to "Yes" under Settings > Sales > Sales settings, 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

(Vendors only)

Assign a vendor a lead time.

Learn more about lead times here.

 
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, making sure the first column is either email address, contact code or contact ID.
  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, and 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, the import will fail.

  • Multiple details recorded in a single cell

    If multiple 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/Vendors (suppliers).
  2. Click the Import link next to the relevant data map.
  3. Browse for the file you want to import. If you haven't yet built your file, you can click "Export a sample file" to get a template which you can fill out and save. Remember the file must be saved as an XLS (Excel 97-2003) file.
  4. Fill out the import instructions:
    mceclip0.png
    • Choose whether to import the values or whether to treat it as a test run
    • Choose whether to have a report emailed to you to detail how the import ran
    • (Optional) Choose an existing tag to assign to the contacts
    • (Optional) Name a new tag to assign to the contacts
    • (If importing statuses) Choose whether to create any missing statuses or whether to skip the row.
    • Choose whether to create new contacts if no existing contact is found with the same details
    • Choose whether existing contacts should be skipped or updated
    • (Optional) Choose a lead source to assign to the contacts
    • Choose whether the import relates to customers or vendors
  5. 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.

Warning: Brightpearl uses the first column in the file to check against existing contact records, so it must be unique - usually an email address or the contact code. If it's not unique you risk accidentally updating the wrong contact records, which cannot be undone.

Dealing with import errors

On attempting the import you may receive a red error message indicating that something isn't right in your file. The error should give you an idea of what the problem is, but here is a list of things to check for:

  • 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?

    Click the "Edit" link next to your data map or move the columns around in your Excel file until they match. 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.

Video

Have more questions? Submit a request