Adding and editing packages and prices to Apicbase in bulk is very efficient with our Excel import file. In this article, you will learn how to fill in this file correctly.
If you want to read up on downloading and uploading the packaging and pricing import file, read this article. If you're new to managing ingredients in Apicbase, you can start here by reading our guide to ingredient management.
Each row in the Excel-file is divided into 4 sections: "Internal", "General information", "Packaging info", and "Supplier info":
- Apicbase ID: This is a unique ID that Apicbase generates when a new ingredient is created. If you use a filled-in template, the Apicbase ID will be here. If you are creating new ingredients and packages then just leave this empty. The ID will be created automatically when you upload the file. You can find more info on the Apicbase ID in this article.
- Name (mandatory): The ingredient's name.
- If an Apicbase ID is given and you change the name in the template, the new ingredient name will be used in Apicbase.
- If no Apicbase ID is given, the importer tries to find an ingredient with the given name and update that one.
- If there is no Apicbase ID and there is no match on the name, a new ingredient will be created with the given name.
Using the "Apicbase ID" and the "Name", the importer has established which ingredient is being created/modified. To do the same for the packages, you need to fill out at least the columns "quantity" and "unit".
- Quantity (mandatory): The quantity of the package. For example, for a bottle of 33 cl, the quantity 33 is filled in. We also support a shorthand notation for combined packages (e.g. 6 x 33 cl). You can find an example of this at the end of this article.
- Unit (mandatory): The unit of the package. It should be one of the units supported by our software (g, mg, kg, ml, cl, l, and piece)
- Gtin (optional): Global Trade Item Number, a global identifier such as EAN or UPC. Generally, this is the barcode and it should be a unique number per package.
- Package type (optional): The type of the package (e.g Can, Bottle, Box, etc.);
- Stockable (optional):
- 1: The package is stockable
- 0: The package is not stockable. (If left empty it will be set as stockable.)
- Piece (optional): Fill with 1 if this package is the one you would refer to us a "piece" when used in a recipe. for more information on the use of the "piece" setting, read this article.
Now we have identified the ingredient and added a package for it. For the packages you can order, you have to enter the supplier and pricing data.
- Supplier (mandatory to create/update supplier package): Name of the supplier. (Be sure to use the exact name of the supplier as entered in Apicbase. Read this article to learn how to check the supplier's name in Apicbase.)
- Supplier article # (optional): The supplier article number. This is the unique number that the supplier uses to identify this article. Although it is optional, we strongly recommend filling out this value, especially when using our ordering module as the supplier article number is the only way the supplier can identify the article.
- Product name: This is the name of the package that the specific supplier gives to this ingredient package.
- Price (optional): Price of the item; This can be either the price for the package (e.g. €5 for a 5kg pack of sugar) or the price per unit (e.g. €20/kg of salmon).
- Price per pack (optional):
- 1: if the price in the former column is the price per pack.
- 0: if the price in the former column is the price per unit.
- Price unit (optional):
- Leave empty if the given price is for the whole pack (1 in the previous cell).
- Otherwise: fill with the corresponding unit. e.g. "kg" if the price is per kilogram.
- Orderable (optional):
- 1: The package is orderable through Apicbase
- 0: If this package is not orderable through Apicbase.
e.g. You buy 6 packs of spaghetti and each pack contains 200g. In this case, you have two packages, the base package of 200g and then a package of 6 x 200g. The 200g package would set "orderable" to 0 and the 6 x 200g package would set it to 1. Because the only package you can order is the 6 x 200g package.
In the sheet above we have a few common examples. Let's assume we are creating those items (ignore the Apicbase ID filled, since normally during a creation that column would be empty)
- In rows 4 to 7 we are referring to the same ingredient with different packages. Note that the 'Supplier Info' section is empty, which means these packages are not linked with any supplier and therefore can not be ordered. Note that in row 4 to 6 there are packages that share the same base package. We call base package the lowest order package, in the case the 33cl can. Ingredient B another base package: the 25 cl can in row 7.
- Ingredient B has the 33cl package on the second row defined as 'piece', so every time a piece of that ingredient is used, the recipe will contain 33cl;
- In row 8 the package has no supplier so the 'Supplier Info' part of the sheet is empty. This package will not be orderable. In rows 9 and 10, the same ingredient has a package linked with two different suppliers.
- Note also that in row 10 the price is given in KG, while in the 9 it is given for the whole pack.
There a few important things you should know before start working with our excel:
- If the entered supplier does not exist in your library, he will be created with the entered name. Be sure to use consistent names for your suppliers. If you use another name or misspell the name, a new supplier will be created. This field is case insensitive so the supplier "Bidfood" will match on supplier "BidFood". Be sure to fill out the rest of the supplier information after you have uploaded the template. Please read this article if you want to learn how.
- There is also a 'shorthand notation' to create packages. In the example above, if you are creating the 10 x 1 kg or the 6 x 33 cl but you don't really want to add information like gtin, package type, etc or link it to a supplier, you could simply add the lines with the 10 x 1 kg and 6 x 33 cl. That will automatically create the packages for 1 kg and 33 cl.
- Changing the name of an ingredient that is shown multiple times in the sheet, can be done by changing the last row of this ingredient. The same works for packages. In the example given above, the package 10 x 1 kg of Ingredient C is shown twice since it is linked with two suppliers. If you want to change its gtin for example, you can change in every row of course but the only one that will actually make difference will be in the last row that it shows up.