Comment on page

Import a list of chemicals

  1. 1.
    Prepare the Excel file, an example might look similar as below. A few important things:
    • Columns that are highlight in blue are important info.
    • You don't need all of the info that show in this example.
    • Empty cells are OK.
    • The amount need to be “xx Y” with xx is the amount and Y is the unit.
    • For unit, it is best to have all in lower case, for example, “ML” should be either “ml” or “mL”. It is best to convert all unit to lower case (use lower() command in excel).
    • Double check the CAS number column, Open Enventory uses CAS number as primary source to look up chemical info from the internet.
    • See step 9 for more info about each column in the excel sheet.
    • Location column: remove all commas ( , ) if there is any. Dashes ( - ) are ok. Update (2020-02-01): for KV's version of OE, if you updated to version after 2020-02-01, this has been fixed.
Update (2020-02-01): for KV's version of OE, if you updated to version after 2020-02-01, steps 2 and 3 below are not necessary any more. KV's version OE (on and after 2020-02-01) now accepts Excel files (*.xlsx and *.xls), comma-separated files (csv) tab-separated files (*.txt). You can find the guide for newer version here.
2. Copy all of the cells in this file (Ctrl-C for Windows, Cmd-C for Mac) and paste (Ctrl-V for Windows, Cmd-V for Mac) them into Notepad (Windows) or TextEdit (Mac)
3. Save this new file as your-file-name.txt. The “.txt” part is important.
Alternatively, instead of step 2 or 3, you can do the following. (Note: in some cases with some chemicals names, Excel has shown to add quotation marks (“”) around the name when exporting to text file. That is the reason why step 2&3 above is preferred.
Save As… the file using Window Formatted Text (for Mac OS)
For Windows, Office 2016 of Office 365, use “Text (Tab delimited) (*.txt)” option:
4. Login into your database using root user or any users with admin permission
5. Go to Settings
6. Go to Import Tab-separated text file (or Import in KV's version)
7. Choose Browse… and choose the exported text file.
Note: in Lines to skip, put 1 if you have a header rows, if not, choose 0
8. Click green check mark
9. Screens as below should show up, you can scroll down to see if they show up correctly (info appear correctly in each row)
10. Choose Column X for each content. If you choose None, that column will not be imported. You can also add a default value for each column that will apply for ALL items being imported.
  • CAS number: most important info, OE will use this to look up other info (structures, MW, safety data, etc) from this cas #
  • Name: not too important
  • Empirical formula: do not import this since OE will use this to find the closest chemicals if CAS# is not present or wrong.
  • Amount: = size of the bottle + unit (format should be #+space+unit, for example: 5 g, 125 ml, 1 kg, etc)
  • Still available: the amount that left in the bottles (OK if not imported)
  • Locations: important. Note: Each location will have its own barcode
  • Compartments: = sub-location. Note: each compartment does NOT have its own barcode
  • Barcode: if you already have an existing barcode system, add it here.
  • Suppliers: = suppliers
  • Date open: could be in European style ( or American style (mm/dd/yy, etc.)
11. Click green check mark. Screen similar as below should show. Note: line X:… will tell you which lines in the text file is being imported.
12. Depend of how many chemical containers are imported, this might take a long time, so please be patient.