The Excel import tool can be used to create multiple container records at a time in your inventory. Any information to be associated with your containers, including custom fields, can be imported.
The import tool allows you to upload an Excel file listing information about the containers to be created. If you are setting up a new inventory in ChemInventory this tool can be useful when migrating your data from external tools.
Imports can be run at any time, including after an inventory has already been set up. Imported containers are always appended to your inventory as new container records; if you would like to update or replace existing records, the Bulk Update Tool should be used instead.
During an import, ChemInventory will assign a range of information to your containers automatically. This includes chemical structures, GHS safety information and chemical synonyms.
The import process
Before starting an import, ensure that you have your container data ready to transfer into an Excel file. An Excel template that is compatible with the import tool is available to download at the link below. While the use of the template is not mandatory, we recommend you transfer your existing information into this file to ensure that formatting is correct.
The layout of import-compatible files is simple: each column in the spreadsheet represents a container field (such as name or CAS number), while each row represents one container record to be created.
Tips on data formatting
While the import tool is robust with how it interprets information listed in Excel files, we recommend the points below are followed.
- If you are importing date information, such as an 'Expiry Date' custom field, ensure that the cells are formatted as Excel Date data types. This assists the tool to differentiate between US formatting of dates (month-day-year) and international formatting (day-month-year).
- If you only have a CAS number for a container record, without a name, this is no problem. ChemInventory will automatically assign a container name to the new record using the CAS number.
- For in-house made compounds, or those without a CAS number, leave the CAS number field empty. Any chemical information that has been saved previously in your database for the substance, such as a structure, will be populated automatically.
- If you do not have information for any of the fields, then leave the Excel entry blank. For a record to be created, only one piece of information is required: either Container Name or Substance CAS.
- To differentiate between parent and sub-locations in your inventory, use the greater than character (>) in the Location column. More information about how the import tool processes locations is below.
The locations hierarchy
All containers in your inventory are assigned a 'Location'. During the import process, the system will identify the destination location using the information you have listed in the Excel file under the Location column.
Breaks between parent and sub-locations can be designated using the greater than character (>). For example, a location may be entered as "Building 1 > Room 5 > Cupboard 3 > Tray 2". The system will place it in the "Tray 2" location in the specified hierarchy, or create this as a new location if it doesn't yet exist.
For entries where no value has been entered into the Locations column, the import tool will add it to an "[Unassigned]" location.
Custom fields
The import tool fully supports importing custom field information into your inventory, such as 'Expiry Date', 'Product Number', 'Grant Code', etc. To include this information in an import, add extra columns to the right side of your Excel file; one for each additional field.
Before starting the import, please ensure that you have defined your custom fields in your database. ChemInventory will not import custom field data if the fields have not been created. Fields can be created from the Inventory Management page.
For any custom fields (defined at both container and substance levels) that have been saved with the Tags data type, use the vertical bar character (|) to separate multiple values. For example, a substance with two DG classes can be entered as "3 Flammable Liquid | 8 Corrosive". ChemInventory will ignore character case and spaces when matching against allowable tags.
Substance Fields
Any custom fields which have been defined at a substance level may already have information saved in your database from previous imports or manual edits by users. In these cases, you do not need to enter a value in your Excel file; applicable cells can be left empty.
If a substance field value is encountered during an import which differs from that already saved in your database, the existing value in your database will be replaced.
Data conflicts
If there is a conflict between the container name that has been entered for a container and its CAS number, the CAS number will take precedence when assigning chemical structures, synonyms and GHS information. ChemInventory will not correct any errors in container names.
Starting an import
A new import can be started by users with Group Administrator privileges from the Inventory Tools menu on the Inventory Management page. You will then be prompted to upload your Excel file.
Before the import begins, ChemInventory will try to automatically link each column present in the Excel file with the relevant field. If it is unable to assign any automatically, you can manually link columns by dragging and dropping the column headers as shown in the screenshot below.
