Importing Products / Services

This is a very powerful tool that allows you to manage your products, services and miscellaneous items in batch using your own Excel spreadsheets.

Learn what you can do with a product import

Before importing products you should ensure you understand how product items are created and structured with the Introduction to Products.

Here's the process in brief:

1. Export a spreadsheet from your existing system
2. Create a Data Map in Brightpearl
3. Reformat the spreadsheet columns to match your Data Map
4. Import the file!

Next time you import, you use the same Data Map. The data map defines where your Excel information is put in Brightpearl.

You can also use the data manager to update products that are already in your Brightpearl system. Very useful for a number of things:

• Updating prices
• Changing tax rates
• Adding products to a collection
• Switching web enabled on/off
• Updating custom fields

Data Maps

A Data Map is used to enable Brightpearl to read the Excel file full of orders that you are going to upload. Essentially what you are doing is telling Brightpearl what columns are in the spreadsheet and where it should put the data contained in that column. You can define a selection of data maps to import files of different formats.

When you create a Data Map for products, the following fields are required for a successful import:

• Name
• Brand
• Category

Follow these steps to define a Data Map:

1. Go to Setup > Data/Import > Import Products.
2. Click the Add a new data map button.
3. Enter a name for your data map.
4. The available fields that can be imported are listed in the right-hand column. Click and drag items to the left-hand side. You can just as easily remove them by clicking the - icon. Rearrange the fields to be the same as the column order in your file.
5. If you have data in your spreadsheet that doesn't match a field in Brightpearl, you must delete it from your XLS file.
6. Click Save.

Available Data Map Fields

Once you have defined the Data Map it will be available to use time and time again. Click Export sample to view an empty Excel file with the column headers as per your Data Map settings.

Creating the Import File

You will need your product data to be in an Excel file. This means you will need to export it from your existing system. Save the file as an XLS file (not XLSX or CSV).

Once you have your product data in Excel you need it to match the columns to the Data Map. Remember the column headings must match the fields on your Data Map – in order and name, you must also include the product name, brand and category. Compare your product data file with your exported Data Map file and make sure your columns and column names match exactly with your file for import. Or you could just enter your data into the exported Data Map!

Ensure there are no blank cells in your data. Brightpearl can’t import the file if there are any blank cells. If lots of products have missing data you should filter these out and create a second file and Data Map to import them separately.

Note: You can only import a maximum of 5000 lines of data at a time, you will need to create separate spreadsheets to import more.

Updating Existing Products

If you're updating existing products, you need to use something to match your spreadsheet rows to an item in Brightpearl, use this "hook" as the first column on the spreadsheet. You can use the Brightpearl product ID (a number), a barcode or your own product SKU. It's a really good idea to have a solid SKU structure for managing items in bulk. Use the rest of the column options to edit fields in Brightpearl. You can update multiple columns at once if you need to.

If you are only updating products you can tick the checkbox to specify that this is an update and the required columns can be ignored.

Variants

Product options (or variants) are used when you have an item which comes in different colours or sizes for example. These must be listed separately on your spreadsheet. You will need to add the option field as a column on the spreadsheet and in your Data Map. Look at this example list of products:

They all have exactly the same name. This means they will automatically be put into a Product Group containing 8 products.

Read more about variants

Bundles

Bundles can be imported by including them as a line on your spreadsheet like a normal product. In order to convert it into a bundle you will need to include the column Bundle SKUs on your spreadsheet and Data Map. In this column you should list the product SKUs to be included in the bundle, along with the quantity of each. Each SKU should be separated by a coma(,), and the quantity should follow each SKU separated by a colon (:), like this:

Read more about bundles

Updating Prices

Create the data map and include one or more or your price lists as columns. The price in your spreadsheet should be the net price (excluding tax), ideally to 4 decimal places. If you are using price breaks for volume discounts (i.e. 1+ = £10, 5+ = £9) you will not be able to add these with the product import, but you can use the price list import.

How to switch on stock management or web enabled products

To make a product stock managed or web enabled you will need to include this field on your Data Map and include the column on your spreadsheet. Enter a “yes” or “no” in the cell.

Importing Product Type

At the moment it is not possible to specify which product type is assigned to products when they are imported; they will all be given the "Default" product type that exists in every Brightpearl account, you may have renamed it. You can then update your products using the batch process on the product list. To make it easy to filter your product list and update the products in batches, we suggest you use a custom field to idenify which product type each product should be assigned, these can be imported and then used to filter the product list.

Perform the Import to Add/Update Products

Once you have defined the data map and import file, you are ready to import/update your products. Follow these steps to import:

1. Go to Setup > Data/Import > Import Products.
2. Click the Import link next to the appropriate data map.
3. If this is the first time, test your file by leaving the Import values checkbox unticked.
4. Specify how the system should handle data it can't match, missing brands and categories, and whether you want to update products rather than add new ones.
5. To assign one or more Seasons to all the products in the file, select them from the available list.
6. Click the Browse button and locate the file you want to import.
7. Click the Upload XLS file button.

Brightpearl will tell you whether it can successfully read the file. If it can you can repeat the same process but this time tick the Import values checkbox.

Successfully imported products are immediately created in the Products module.

Dealing with 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:

1. 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.
2. Do the column headings match (order 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.
3. Is your spreadsheet saved as the correct file type?
   It must be saved as an XLS file, which is Excel 97-2003.

What can and can't I do with a product import?

What can it do?

• Add/update all your stock tracked products
• Add/update non-stock tracked products (misc items, labour, other charges)
• Create "Product Groups” with 1 or more options and create new options (e.g. small, medium, large, red, green, blue)
• Create bundles of products
• Add/update nominal codes per product (sales, purchases, stock)
• Add/update default tax code per product
• Add/update and create brands, collections, and product categories (top-level categories only, i.e. not sub categories, these are used for menus and menu items on your website)
• Assign products to one or more Season
• Add or move products to categories (products can be within more than one category)
• Update a single price on each price list per product – use the price list import to add price breaks (e.g. 1 product at £10, 5 products at £8, 10 products at £6)
• Specify the warehouse location of each product
• Define minimum stock levels and re-order quantities per product
• Add/update codes – SKU, ISBN, barcode, EAN, UPC
• Populate custom fields per product
• Add a supplier against the product – add more suppliers by updating the spreadsheet and importing again
• Populate custom fields - create your custom fields first

Brightpearl Webstore related stuff:

• Activate products on your Brightpearl Webstore (Web enabled)
• Add website descriptions
• Add/update categories – see above
• Mark the product to allow Webstore checkout when there is no stock
• Add/update product weight (for calculating shipping costs based on delivery weight on online sales)
• Enter Meta Descriptions - a short description that will be shown in the header of the page
• Enter Meta Keywords - a list of keywords that relate to this item and your website. These will be shown in the header tags of the page
• Add a template to each product to define how it’s displayed online
• Define how product options are selected – drop down list, radio buttons, table (e.g. select small, medium, large)

What this import can’t do, but which you can do in Brightpearl:

• Add product images – this is done on a product-by-product basis within the website module, or you can ask us mass import
• There is a limit of 5000 rows per import – you will have to import separate spreadsheets
• Mark products as “Featured” – this is used for adding them to a special featured page on your website or to create a quick add button in EPOS
• Mark a product as “Virtual” – this would be an intangible item, such as “Goodwill” or “Music download”, i.e. not a physical item
• Mark a product as “free shipping”
• Select the product condition, i.e. new, used or refurbished
• Add a sales pop-up message – pops up when you add the item to a sales order
• Assign a product type other than the default - you can batch process your products after importing