Import Products from Excel
There are two ways to get to the imports products page.
1. Go to products>Import products
2. From the list products page, click the import products button
Either option will bring you directly to the product import page:
From here, you can 1) Download a csv template for the import, 2) Download a template with all existing products, 3) Upload an import template.
Creating products with the import template
To create new products, start by downloading a blank template. There will be one sample product to show you how to format the data. Make sure to delete this before you do the final import. Fill out the template following the step by step guide for each field below. * Indicates a required field.
- Product ID: Leave this blank. The id is there to identify an existing product. If it is blank, OpenBoxes will know that you are creating a new product
- Product Code: Leave blank to autogenerate a code. If you want to create your own code, you can type one in, but this isn't recommended unless you have a good system for keeping codes unique. OpenBoxes will create a short unique code for you if the field is blank.
- Name*: Enter the name for the product
- Product Type: Leave blank unless you have specifically configured custom product types
- Category*: Enter the name for the category the product belongs to. If you enter a category that does not exist. OpenBoxes will create it and add it to the tree. If you have categories with duplicate names, OpenBoxes will select one of the matching names. So be careful about typos here.
- GL Account: This will be required if you have GL and budget code tracking turned on. Choose the GL account code associated with the product. You must choose from a list of existing codes, or leave blank if you do not have budget tracking enabled.
- Description: Enter any other information about the product. Up to 250 characters.
- Unit of Measure: Enter the unit of measure in which this product will be tracked. Please note OpenBoxes does not do automatic conversion for UOMs, so the UOM will always be the same for this product.
- Tags: A way of categorizing products. Read more about tags here. You can enter tags separated by a comma and no space, for example tag1,tag2,tag3. If a tag does not exist, OpenBoxes will create it on import. So look out for typos in this field
Special Property Fields:
- LotandExpiryControl: Type TRUE to require lot and expiry to always be entered for this product. Type FALSE or leave blank for expiry tracking to be optional
- ColdChain: Type TRUE if the product requires cold chain. FALSE or blank means not cold chain
- HazardousMaterial: Type TRUE if the product requires hazmat packing. FALSE or blank means not hazardous
- Reconditioned: Type TRUE if the product has been reconditioned. FALSE or blank means not reconditioned.
Source and pricing fields: All of these are optional free-text fields. Whether you choose to use them will depend on how you choose to configure products.
- Unit Price: Enter the unit price for the item in the default system currency (but do not format as currency, format general). The field will accept up to four decimal places.
- Manufacturer: Enter the name of the manufacturer for the product
- Brand: Enter the brand name for the product
- Manufacturer Code: This is the code/SKU the manufacturer uses for the product
- Manufacturer Name: This is the name the manufacturer calls the product (NOT the name of the manufacturer)
- Vendor: The name of the vendor for the product
- Vendor code: This is the code/SKU the vendor uses for the product
- Vendor name: This is the name the vendor calls the product (NOT the name of the vendor)
- UPC: Enter the universal product code if applicable
- NDC: Enter the national drug code if applicable
- Created date: Leave blank. OpenBoxes will assign a date.
- Updated date: Leave blank. OpenBoxes will assign a date.
Once you have fully filled in the template, press choose file and select your saved document. Then press upload.
The next screen will show you the data you are uploading, and help you recognize potential errors. New data will appear in red. Since we are importing new products, it is normal for everything to appear in red.
When you have reviewed your data, press next.
The final page will show you a summary of what you are doing. In this example, I have made 0 edits to existing products, and I am importing 1 new product. OpenBoxes also applies a tag indicating the import number that will be added to all products in the import If you do not want this, click the x to delete the tag. You can also add other tags you want to apply to the full import.
When you are sure that your data is accurate and ready to be imported, click import. You will receive a message saying you have successfully imported products. You can see them in the product list or export all existing products into excel.
Edit Products using the Import
Editing products is very similar to creating products, so please read that section first and ensure that you understand it. There are a few key differences with editing versus product creation. The first is that you don't start from a blank template. Instead you start from a list of existing products.
- Download the CSV of all products on the first screen of the import workflow. This will give you a list of all products with ids and code.
- Delete the products you DO NOT want to edit: This is not strictly necessary. If you reimport the same products with no changes, nothing bad will happen. But it will make the import much slower, and it will also make it easier to make mistakes. So it is better to delete all products you are not updating from the sheet
- Change the fields you want to update:
- DO NOT change the id field. This is how OpenBoxes identifies the product
- It is possible to change the product code, but it is not recommended as this is a key field for users to search and identify the product. Be sure you have thought it through before you edit a product code.
- Deleting the value in a cell will overwrite that value with a blank. For example if you delete the list of tags from a particular product, the import will delete all tags from that product.
- No need to change the created and updated dates. OpenBoxes will do that on its own.
- Import the final file
- Review the changes: You should only see red in the fields you actually updated. If you see red in other fields, something went wrong.
- Review the final numbers: It will tell you how many products you have edited, and how many you are creating, If you are intending to edit, you should see 0 products created.
- And import! You can see the changes in the product list or by exporting a new product list file