The CSV import allows you to quickly update the stock levels of multiple stock items. For example, after a stock take you may need to update many items, why do it one at a time when this simple import will do it all for you? Not only is it an effective way to manage your stock levels, it will also update your stock accounting, saving you time and mistakes! When you do a stock take you can easily export your product list, update the stock quantities and then import it back into Brightpearl using this routine.
Step 1: Build your CSV file
A CSV file is simply a text file with commas to separate the values on each line. You can create one using Excel, Open Office or just a text editor (make sure you have it in MS-DOS format). Your file MUST include the following columns and this order:
ProductID / SKU / Quantity / Location / Cost Price
You will need to use ALL these columns, even if you don't have any data in them, for example, you may not be using locations, or you might have a SKU missing.
Once you have your data prepared in Excel, save the file as a CSV (Comma Delimited) type file.
Where can I find the ProductID?
The product ID is the reference assigned to each product by Brightpearl. You can see this number below the SKU on your product list, or in the URL when you are editing a product. These numbers will also export with your products.
Step 2: Choose your import option
Brightpearl provides two ways of updating your stock:
- Quantity in Stock - update the stock quantity in Brightpearl to the quantity on the file; useful for correcting quantities after a stock take
- Quantity to Add/Remove - increase or decrease Brightpearl stock quantities by the amount on the file; useful if you assemble a single stocked item out of other stocked parts, e.g. you buy bike parts and then build complete bikes for sale
Subtle differences, but this gives you the flexibility to update your stock as you need to in the many scenarios you encounter.
Note: You cannot remove stock where there is not enough on hand quantity.
Go to Products > Update inventory by CSV and make the following entries and selections:
Match product by:
First, choose whether you want to match your products by SKU or Brightpearl ID (Product ID). It's up to you, but if you know some products don't have a SKU you might opt for Brightpearl ID, otherwise Brightpearl won't be able to match them up and the update will fail for those items.
Quantity:
Specify how you want the quantity updated. You can either:
- Set the inventory level to the quantity given Change the level to the quantity specified in the spreadsheet, or
- Add/remove the quantities given Add or subtract the quantity in the file from the current stock quantity
Warehouse:
You will need to specifiy which warehouse the stock levels are for. If you are not using multi-warehousing you will only have one to choose from.
Use cost price from:
You need to specify a cost price for the item, that's a price that you pay for the item when you purchase it. You can select a price list in Brightpearl or you can choose to use a price entered on your file. You need a cost price so that your accounting is correctly updated with any items added or removed from your stock; the stock value (asset) also needs to be updated.
If no cost is applicable you can tick the checkbox to allow zero cost prices.
Step 3: Import your file!
Click the Browse button to locate and select your CSV file, and then click the Update inventory button. Confirm your method of updating by clicking OK and wait for a message to appear on screen to tell you what has happened.
A green message box will show you whether Brightpearl could read the file and summarise the changes made. If Brightpearl could not read the file, or errors were encountered for some lines, an error message will display. If only some of the lines have failed validation it will not prevent the whole import, the rest will update stock successfully. You will be given a report detailing the successful and failed lines at the bottom of the screen, and vai email. Any line with an error will have details of the error printed next to it on the report.
If you update is successful your stock levels will be immediately updated and the necessary accounting entries made to the nominal codes: Stock (asset) and Stock Corrections (expenses; defined at Setup > Company > Accounting: Nominal codes).
FAQs
I keep getting an error message saying, "Stock correction failed. Not enough on hand availability of product at specified location to perform stock correction". What do I need to do?
It is not possible to reduce your on hand stock quantity to below zero. Although your "in stock" quantity may be remain above zero if you were to reduce it, some of the stock may be allocated to sales orders and therefore your on hand level is less and would fall below zero. Take the following example:
In stock = 10
Allocated to sales orders = 9
On hand = 1
Import attempts to reduce by 5 and fails with the error.
You will need to un-allocate your stock before reattempting the import.
