Skip to content
English
Submit a ticket
Apicbase.com
  • There are no suggestions because the search field is empty.

How to count your inventory using an Excel File?

This article will show you how you can count your inventory in Apicbase via the use of an Excel File.

 

We from Apicbase always advise doing your inventory counts by using the Software or the Bar Code Scanner App. Links to the respective articles on how to do this can be found here and here.

 

You can fill in the Excel template yourself, or let Smart Import read a photo, a PDF or a spreadsheet of your own count sheet. See "Counting with Smart Import" at the end of this article.

  • In order to do your count using Excel, you need to start a count first. Go to "Inventory" and select "Count".

    English Count

  • Select the outlet for which you want to start a count.
    Count outlet
  • Click the "Start Count" button in the top right corner of the screen.
    English Start count-1

  • In the following screen, select "Import" > "Download Template".
    English Download Template
  • A download will start that will generate an Excel file for you. In the file, you will see all the packages. To count, simply fill in the number of packages still in stock in the appropriate column. 
  • Once you are done you can upload the Excel file again using the "Upload File" button in the screenshot above.

The template always has an empty count column, even when you have already counted some articles in this partial count. Rows are matched on the APICBASE ID column, so always fill it in. A row with only a GTIN can be refused. Rows with an empty count cell are ignored. A count of 0 is imported. A negative count is imported as it is, so check the file for minus signs before you upload it. Save the file as .xlsx: an old .xls file is not accepted.

On the import page, tick Sum the counted quantities of repeated articles to add the numbers in the file to quantities that are already in the count. It is off by default. When it is off, a row for an article that already has a quantity in this count is not imported. This also applies to articles you counted by hand and to a second row for the same article in the file. When it is on, importing the same file twice doubles the quantities. The import never replaces a quantity.

When the import is done, the page shows how many rows were imported, how many had problems and how many have warnings. Select Download Error File to get the rows with problems.

The Count import page with the Sum the counted quantities of repeated articles checkbox and the upload area

A count cannot remain open while there is ongoing POS activity, even when you are counting by using an Excel File. 

Do not forget to close the count at the end. This can be done in the same way as with a normal count. 

Counting with Smart Import

Smart Import reads a photo, a PDF or a spreadsheet of your own count sheet and matches each line to your stock items. You do not need the template.

Smart Import is in the Import menu of a partial count when AI is switched on for your library. If you do not see it, contact Apicbase support.

Before you start

  • Open a partial count. The main count has no Import menu.
  • You need permission to continue counts.
  • You must be the user who holds the partial count. If another user holds it, continue it from the main count and answer Yes to Another user is currently working on this event. Do you want to claim it? Otherwise Smart Import stops with You need to claim this count before importing into it.

Steps

  1. Open the partial count and select Import > Smart Import.
    The Import menu on a partial count with Download Template, Import and Smart Import, with the Beta badge
  2. In the Smart Count Import window, drop your file or click to browse. You can use a photo (JPG, PNG or WEBP), a PDF, or an Excel or CSV file, up to 20 MB. Scanned PDFs work too. Results can vary, so check them before you apply. The sheet can be in Dutch, English, French or German.
    The Smart Count Import window with the upload area and the notice that AI may make mistakes
  3. Select Analyze with AI and keep the window open. Apicbase reads the rows (what was written, the quantity, the unit and any pack size, barcode or article number), skips headers, totals, prices and par levels, and looks for each row among the stock items of your outlet.
  4. Check the rows. The grid shows Read from document, the matched Article, Already counted and Quantity. The header shows how many rows were read and, in red, how many are without an article. Rows that Apicbase is less sure about are amber. Check these first, including the quantity.
    • To change a matched article, click it and choose another under Or choose, or select Search all articles....
    • For a row without an article, pick one under Did you mean or type in Search article....
    • Correct the Quantity where needed. Use decimals: 2,5 is read as 2.5, but type 0.5 and not 1/2. A row with an invalid quantity is marked and cannot be applied until you correct it. A row without a quantity shows Required.
    • Remove a row with the cross at the end of the row.

    The review grid with a matched row, an amber row and a row without an article
  5. Decide what happens to quantities that are already in the partial count with the switch Add to quantities already counted. It is off every time you open the window. If your sheet has two lines for the same article, Apicbase always adds them together first: five in the kitchen and seven in the storeroom become twelve. After that, with the switch off the total replaces the quantity in the count for that article. With the switch on, the total is added to it.

With Add to quantities already counted off, the quantity from your sheet replaces what the partial count already holds for that article. The Already counted column shows what is there now.

  1. Select Apply N rows to count. Only rows with an article and a valid quantity are written to the count. The other rows are skipped. The window then shows a badge for the rows that were applied, failed or skipped. A badge appears only when there are such rows, and each failed row is listed with its reason. Retry Failed appears only after a row has failed and puts the failed rows back in the grid. Select Import Another File to start again.
    The results step with the applied and skipped badges

Smart Import writes only into the partial count. It does not close the count or change your stock, and articles that are not on your sheet keep their quantity. Close the partial count and the main count as you do after any count.

Smart Import reads up to 200 rows per file. For a spreadsheet, the rows after the 200th are not read and a warning says so. Split the file and run Smart Import once per part. For a photo or PDF, apply the rows you see and run Smart Import again for the rest.