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 | |
| 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
- Go to Settings > Data / Import, and select the relevant import routine - accounts, products, customers/suppliers or orders (for both sales and purchases).
- Click the Add a new data map button.
- Enter a name for your new data map.
- 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.
- 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
- Go to Settings > Data / Import > Import Customers/Suppliers.
- 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.
- Browse for the file you want to import.
- Specify what tags you want the contacts to be given.
- Tell Brightpearl what to do with contacts that already exist - should they be updated?
- 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