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 the in stock quantity of many items. When you do a stock take you can easily export your product list, update the stock quantities and then import it back into Brightpearl to correct the inventory levels.
Products or inventory?
Products are records of items that you buy and sell. Inventory represents quantities of those items in stock. Inventory can only be added against a product.
To add or edit product records, use the product import at Settings > Data/Import.
To add or correct inventory levels, use the CSV import at Products > Update inventory by CSV. This import requires only 5 columns of data:
It is not possible to import both a product and inventory at the same time. The product must first be created, and then inventory can be added against it.
Importing inventory levels
Inventory levels in Brightpearl can be updated in bulk by importing a CSV file. It's possible to adjust inventory levels via two ways:
- Set inventory level to quantity given will add or remove the required amount to set the total in-stock quantity to the figure in the file. If the file states "5" then the quantity after the import will be 5, whether the quantity before the import was 0 or 100.
- Add/remove quantities given will add or subtract the quantity given in the file from the current in-stock quantity. If the file says "5" and the current in-stock quantity is 2, then that will give 7. If the file says "-5" and current stock is 7, then that will leave 2 in stock.
How to update inventory levels by CSV
- Create the file ready for import. This can be done by first exporting a list of your products and removing unwanted columns. Export your products from Products > List products.
- The import file must be a CSV file, and contain only, and all of, the following columns:
- Save the file as a CSV type file.
- Now import the file at Products > Update inventory by CSV.
Make the following selections:
Product ID: This is the Brightpearl product number assigned by Brightpearl when a product is created. It is displayed in an exported file of products and can be used to match items during an update.
SKU: This is the Brightpearl product SKU. It can be used to match items during an update instead of using the Product ID. If it is blank it cannot be used for updating. SKUs can only be updated using the Product Import, not the inventory level import.
Quantity: The import can be used to adjust the current inventory level by this quantity (increase or decrease by this quantity) or to set it to the quantity in this field.
Location: If warehouse locations is activated you will need to specify into which location the items are being added or from which to be removed. If it is not activated leave the fields blank.
Item cost price: When changing inventory levels accounting entries are made to adjust inventory asset values. When adding stock the asset value used can be taken from a price list or from a value given in the file. When removing stock the actual in stock value of the item is used. Brightpearl will remove the item from the given location, if there are multiple batches in the same location then the oldest one will be removed (FIFO).
Match product by: Specify which column should be used to search for the products.
Quantity: Specify whether the file contains the actual quantity in-stock (e.g. set total quantity to 1), or an amount by which the existing inventory level should be changed by (e.g. 1, or -1, to increase or decrease by 1).
Warehouse: Select the warehouse at which the correction is being made.
Use cost price from: Specify whether the value used for adding stock should be taken from a price list or from the file.
- Browse for and select the CSV file to import.
- Click the Update inventory button to update the inventory levels. This cannot be reversed.
Accounting for imported stock corrections
When items are added or removed from inventory, an accounting journal is automatically posted in order to adjust the asset value of inventory. All stock movements are accounted for in base currency.
When items are added into stock the values posted to accounting are the item price given at point of import, multiplied by the quantity.
When items are removed from stock the actual value of the items removed from stock is used. The items removed are selected by providing the location (if activated), and FIFO.
All stock corrections will affect the default stock nominal code and the chosen stock corrections nominal codes set at Settings > Company > Accounting: Nominal codes.
Stock import correction audit trail and traceability
When a stock correction is made, the journal and the inventory audit trail will record the following information:
- Inventory movement ID
- Product ID
- Date and time
- User name
This makes it easy to traced the accounting journal directly back to a specific stock correction.
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.