How Can We Help?
3.02 Import Inventory Items from Excel
Users with the Lab Manager role can add new items to inventory via bulk upload using our Excel Import Template. This can be used to initially populate an inventory or to add a large quantity of new items to an existing inventory.
The Import Template is an Excel spreadsheet organized by Inventory Type. Each Inventory Type is on a separate tab and each tab includes the standard inventory fields as well as unique detail fields for each Inventory Type. Any modifications made to the default Inventory types, their detail fields, or any custom types added will be reflected in the template.
The Import tool may only be used to add new items to your Inventory. To make bulk modifications to existing inventory items, see our article on the Bulk Update tool.
Do you need to customize the inventory information before using the template?
- Compare your inventory data fields to the standard data fields (used for all inventory types) and the detail fields specific to your relevant inventory types.
- To compare your inventory information to the standard fields: Go to Lab Management (the gear icon) and view the Inventory Types. At the top of the Inventory Types screen, you will see a list of the standard fields.
- To compare your inventory information to the detail fields for a relevant inventory type: Go to Lab Management (gear icon) and if you have inventory that matches one of the default Inventory Types, view the fields and data options.
- Does your inventory have fields that are not represented in either the standard fields or specific inventory type fields? If so, you will want to customize the fields for the existing types or add your own new inventory type and its fields.
Note: The template can be customized by modifying the Inventory Types fields. Before downloading your template, customize the Inventory Types and fields.
How to import Storage Location information:
- The template includes fields that allow you to specific the top-level location, a bottom-level location within a top-level location, and if that bottom location is a freezer box, the freezer box cell information.
- Set up your Storage information first using the Storage tab on the Lab Management screen (gear icon). This allows you to build a hierarchical representation of storage locations. For more information on Storage Locations, Click Here.
- For example, “The -80 Freezer”, could have “Shelf 1”, with “Rack A”, and “Freezer Box 1” which is a 9 x 9 freezer box.
- The top-level storage location in this example is “The –80 Freezer”. The bottom-level storage location is “Freezer Box 1”. Each item would then be assigned to a cell in Freezer Box 1, such as A1, A2, A3, and so forth.
- When you import location information, we will map to the top-level and bottom-level locations to those you set up in Inventory. It will inherit the levels between the top-level and bottom-level location to show the complete location information.
There are several important guidelines for using the template:
- Before downloading the template, be sure to review and modify the template to match your existing inventory information (see above section).
- Set up your Storage locations so that you can import storage location information for each item.
- The only required field on the template is the Name field, unless you have made any of the inventory type fields required.
- The template cannot be modified in any way. If you are not using a column or an Inventory Type, leave them blank—do not delete or re-arrange then columns or tabs. Do not add any columns to any of the tabs.
- If a quantity is not listed, a value of 1 is assumed.
- Vendors will be automatically created if they don’t exist in your list of Vendors.
- The Import Template can only be used to add new items. It cannot be used to update existing inventory items.
Download the Template:
Click on the Inventory link from the top menu bar and select the “Import Items” option and then click on “Next”.
After reviewing the guidelines, click on “Download Template.” This will download the template in .xlsx format.
Using the Template:
Add your existing data to the template by copying it over and pasting it into the appropriate columns. As noted above, the tabs and fields on the downloaded template cannot be modified (do not add, delete, or rename any of the column templates).
You can add new Inventory Types to the spreadsheet by following these steps:
- Scroll through the Inventory Type tabs until you get to “New Type 1”. There are 5 tabs for new inventory types.
- Change the tab name from “New Type #” to the name of your new inventory type.
- The standard fields for all inventory types are displayed.
- Name field is the only required field.
- Custom detail fields cannot be created via the spreadsheet in this manner. Custom detail fields must be created before downloading the template. To learn more about Inventory Types, click here.
Uploading the Template:
Once the template has been completed, upload the template by going to the Inventory page, and clicking on “Import Items”.
Upload the completed template by dragging and dropping the file in the drop area indicated on the screen or by clicking in the drop area to choose the file you wish to upload.
If there is any problem with the template, you will see an error message on the screen listing items that must be addressed before the spreadsheet will be uploaded. Note that all error issues must be addressed before the spreadsheet can be uploaded.
Viewing Uploaded Inventory:
To view your uploaded inventory items, click on Inventory from the top menu bar to view the Inventory page.
Use the Filter menu on the left-hand side to limit the inventory items displayed based on Inventory Type, Location, or Received Date Range.