If you want to correct the stock of many products at once, the CSV import is the fastest way. You can use it to book stock out as well as in – depending on whether you enter a negative or a positive stock quantity. You can adjust individual products more easily directly in the product (see Adjust stock manually). This guide walks you through the recommended way step by step: the relative stock import.
Relative means: The stock quantity you enter in the file is offset against the current stock – so the stock is corrected by this quantity relative to the existing stock. With the absolute stock import, on the other hand, the stock quantity you enter is the new total stock of the product.
Which stock import do I need?
Both imports write stock, but they handle it differently. This overview helps you choose:
Relative stock import | Absolute stock import |
You know the difference: book out 30 units of breakage, book in 15 units of a delivery, correct shrinkage. | You know the actual stock: quantities counted after a stocktake, initial filling of a new warehouse. |
Stock movements between export and import are retained – your quantity is offset against the then-current stock. | Stock movements between export and import are overwritten. |
Column Change | Column Stock |
When in doubt, use the relative import. Between downloading the template and importing, orders, returns or stock transfers can change the stock. The relative import takes these movements into account, while the absolute import discards them – and the longer you work on the file, the more likely this becomes.
You can find out how the absolute stock import works in How do I overwrite my stock via CSV?.
Before you start: What a CSV file is and what to look out for when saving it (UTF-8 encoding, semicolon as separator) is explained in Tips for working with CSV files. Read it once before you start.
1. Download the template
Go to Warehousing → Stock overview. Click export and select Export without stock (as template for the relative stock import). In the downloaded file, there is one row per product (or per product and bin location) with the column Change, which is set to 0 everywhere.
Tip: Using the menu on the right, you can show/hide columns and filter beforehand. Your template will then contain only the products you care about.
Safety net: Additionally download the Export with stock (as template for the absolute stock import) once and keep this file. It contains the stock your products had before the import. If something goes wrong during the import, you can restore the original state by running an absolute stock import with this file.
2. Enter the stock change
Open the file in your spreadsheet program (e.g. Excel) and enter in the column Change the quantity by which the stock should change. Leave products that should remain unchanged at 0, or delete the entire row.
Book stock out (negative stock quantity)
For the products you want to book out, enter a negative stock quantity – for example -30 to book out 30 units.
Book stock in (positive stock quantity)
For the products you want to book in, enter a positive stock quantity – for example 15 to book in 15 units.
Example
Suppose you want to book out 30 units of the product SW10001 from the bin location A-01-02 and book in 15 units of the product SW10002 at the bin location A-02-01. A valid file then looks like this:
Product number | Warehouse code | Bin location | Change |
SW10001 | HL | A-01-02 | -30 |
SW10002 | HL | A-02-01 | 15 |
or:
Produktnummer;Lagerkürzel;Lagerplatz;Änderung
SW10001;HL;A-01-02;-30
SW10002;HL;A-02-01;15
The columns Product number and Change are mandatory. Using Warehouse code and Bin location, you define where the stock change is booked.
3. Save the file
Save the file again as a CSV file (UTF-8 encoded, with a semicolon as separator). You can find out how to set this in your spreadsheet program in the Tips for working with CSV files.
4. Import the file
Go back to Warehousing → Stock overview and click import. Upload your file and select Relative stock import (requires the column "Change") as the format. Optionally, you can add a comment. Then click import.
Important: Be sure to select the correct format. If you accidentally select Absolute stock import (requires the column "Stock"), the stock quantity you entered is booked as the new total stock (so -30 would result in a stock of -30) – and your stock will no longer be correct.
5. Check the result
Under Settings → Shop → Import/Export (Pickware) you can see all imports and – if something went wrong – the errors that occurred. You can also find the individual stock changes in the product details in the tab Stock under Stock movements (e.g. with the reason Destocking (CSV import)).