Stock taking: stock import/update inventory by CSV

The CSV import allows you to quickly update the inventory levels of multiple stock tracked 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.

Learn more about stock taking here.

Are you updating products or inventory?

Products are records of items that you buy and sell, while 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:

csv import file

It is not possible to import both a product and its inventory at the same time. The product must first be created, and then inventory can be added against it.

Learn more about importing products here.

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 result in 7 in stock. If the file says "-5" and current stock is 7, then that will leave 2 in stock.

How to update inventory levels by CSV

  1. 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.

    Howto Update inventory by CSV 1

  2. The import file must be a CSV file, and contain only, and all of, the following columns:

    Howto Update inventory by CSV 2
    The column descriptions are as follows:

    Product ID: This is the Brightpearl product number automatically 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: A numeric value representing either the total stock level (if using 'Set inventory level to quantity given' mode) or the number of units to add or remove from the current in stock level (if using 'Add/remove inventory' mode - to remove, use a negative number).

    Location: If multiple concurrent location management is in use, you will need to specify into which location the items are being added or from which location they need to be removed. If using standard location management, or if location management is disabled, this column can be left 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 this column of the file.
    When removing stock, Brightpearl will remove inventory using the cost related to that batch in the system, using first in first out (FIFO) to determine which unit to remove first.

  3. Save the file as a CSV type file.
  4. Now import the file at Products > Update inventory by CSV.

    update-inventory-by-csv.png

    Make the following selections:

    Match product by: Specify which column should be used to search for the products:

    Quantity: Choose how to update the quantity.
    'Set inventory level to quantity given' will update the 'in stock' figure to the level in the file.
    'Add/remove inventory' will add or remove the quantity in the file to the current in stock level.
    For example, if the product has 10 in stock, and the quantity in the file is 12, 'set inventory level to quantity given' will add 2 units, bringing the stock level to 12, while 'add/remove inventory' will add 12 units, bringing the stock level to 22.

    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 the file, or from a price list. If the latter, select which price list to use.

    Allow zero cost prices: Choose whether to allow stock to be added with a cost price of 0.00. If not selected, any rows containing stock that would use a cost price of 0.00 will be skipped.

  5. Browse for and select the CSV file to import.
  6. Click the Update inventory button to update the inventory levels. This cannot be reversed.

Once the file is processed, the screen will refresh and you will see a report at the bottom of the page (which will also be emailed to you).

The report details all the rows in the file, and if there were any errors, displays the reason for the error.

mceclip0.png

Accounting for imported inventory 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.

Nominal codes

All stock corrections will affect the default stock nominal code and the chosen stock corrections nominal codes set at Settings > Company > Accounting: Nominal codes.

Inventory import correction audit trail and traceability

When an inventory correction is made, the journal and the inventory audit trail will record the following information:

  • Inventory movement ID
  • Reason
  • Type
  • Product ID
  • Date and time
  • User name

This makes it easy to trace the accounting journal directly back to a specific stock correction.

GL-stock-correction.png

Inv-audit-stock-movement.png

Stock takes performed using a CSV will always have a "Reason" of "Stock Update".

Learn more about viewing an audit trail for inventory adjustments here.

Video

How to run a stock take using Brightpearl.

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?

This error occurs when the correction you are attempting to make will reduce your "on hand" level to below zero.

Although your "in stock" quantity may still be greater than zero after the correction, some of the units in stock could be allocated to sales orders, resulting in a lower "on hand" quantity.

Reducing the "in stock" quantity may therefore result in attempting to remove allocated units.

Take the following example:

In stock: 10
Allocated: 9
On hand = 10 - 9 = 1

If you tried to import a file to set the stock level to 5, the system will only see 1 unit available to be removed, as the other 4 are allocated to orders.

The import will fail, resulting in the error.

To correct the issue, you will need to unallocate your stock before reattempting the import.

Have more questions? Submit a request