Hands-on: Batch operations using excel templates

• 30 min
• Medium

Overview

The goal of this hands-on is to understand how to efficiently create and edit large lists of items using excel-based templates.

• Step 1: Batch creation of antibodies using an excel template
• Step 2: Batch export of antibodies
• Step 3: Batch edition of antibodies using an excel template

Walkthrough

You will need a spreadsheet editor to perform this hands-on. In you don't have access to Excel, we advise installing LibreOffice.

If you are on a Mac, Numbers can also be used. In this case, you will need to export the document to Excel and make sure that the Excel Worksheets option is set to One per sheet.

Exporting Excel document from Apple's Numbers app

Make sure the Excel Worksheets option is set to One per sheet

Step 1. Batch creation of antibodies using an excel template

In many situations, one needs to create a lot of items at once. For example, when a new group decides to use LabID, there are usually large lists of antibodies, plasmids, oligos ... that need to be imported into LabID. Another use case is when you procrastinated this task for too long... In such situations, LabID offers to describe your antibodies (or plasmids, oligos...) in an excel spreadsheet and upload them all at once. As you will learn below, you cannot just load any excel spreadsheet but only well-formed templates.

We now put this into practice with this first exercise where we will create 10 antibodies.

• Navigate to the Consumables menu and click the Antibody sub-menu.

• Mouse over the button above the table (boxed in red in below picture) and click on the Download template for batch create link.

  Download a antibody specific excel spreadsheet for batch creation

  

  A template to batch create antibodies can be obtained from the antibody list page

• A pop-up window offers you to customize your excel template with annotations. For example, if you want to specify a Pubmed ID for some of the antibodies, select pubmed_id. When you are ready, click on Download template at the bottom.

  Batch Create Template Customization Options

  

  Each selected annotation type will end up as an extra column in the excel spreadsheet.

• This downloads an excel template to your computer. Open it in excel (alternatively install OpenOffice):

  • The template has three tabs (red arrow), the first is named after the item type this template is specific to, the second holds the controlled vocabulary necessary for the cells with fixed choices and the third holds some help information.

  • Scroll to the right, the last column(s) are the custom annotation columns (here you should have pubmed_id)

  Excel Template for antibodies

  

  Excel templates are specific to the item type, here an antibody. Whenever applicable, cells offer selected choices with options matching those available in the LabID web interface.

• Fill in the name column only for 10 antibodies with specific names like "AB-traineeXX-1".."AB-traineeXX-10" where XX is your trainee number.

  Warning

  It is important to keep both header rows unmodified.

• Navigate to the Antibody list page if necessary and mouse over the button above the table (right to the you used to get the template) and click on the Batch create with spreadsheet link.

  Excel Template Import Page

  

• Drag'n'Drop the excel spreadsheet on the drop zone.

  What happens?

  The upload fails indicating only one error about an Empty value for stocks_antibody_ig_species at Line 3. This is expected as we only filled in the antibody names while other columns are also mandatory (headers in bold in the spreadsheet).

  

  Batch create failing. Only the first error is reported due to the option Abort on first error (boxed in green). You may try to change to turn off this option and click on retry (boxed in blue). To upload a corrected spreadsheet, clean up the form using the cross boxed in yellow

• Re-open the excel and fill in the other mandatory columns for 10 antibodies (some are located on the far right of the template):

  • Ab Type:e.g.PRIMARY
  • Isotype:e.g.IgD
  • Ig Organism:e.g.Chicken
  • Status:e.g.AVAILABLE
  • Maintained:e.g.True

• Clean up the upload page (click the cross boxed in yellow) and Drag'n'Drop the new excel spreadsheet. You should now get a green confirmation message.

• Delete all the antibodies you just created as you learned in the previous hands-on

• Re-open the excel spreadsheet and :

  • Delete all columns that have no value
  • Add a new column "Tube Color" and fill this with the value "red"

• Upload the excel spreadsheet. Check the result.

  What happens with the new column Tube Color

  Open the detail page for an antibody newly created: the Tube Color cannot be found. Indeed, LabID will ignore columns with unknown headers. If you wish to add such an extra Tube Color property, you need to first get in touch with the LabID admin. Depending on the situation (i.e. globally applicable property or not), the Tube Color may be defined as a new property (globally applicable situation i.e. this property makes sense for most people) or as a new annotation type (not globally applicable). We'll come back on this in future trainings.

End.

Rules to fill in the excel template.

Learn more in the LabID documentation

Step 2. Batch export of antibodies

You can also batch export items e.g. antibodies into an excel spreadsheet. This is convenient when you need to get a list of tubes from the freezer, to share a list of items with colleagues or to grab when you leave to another lab.

As a demonstration of the batch export feature, we will export a set of antibodies.

• Navigate to the Consumables menu and click the Antibody sub-menu.

• Set the ownership filter on All

• Select all items on display using the select all checkbox available before the column headers (boxed in red in picture).

  Exporting a list of antibodies to an excel spreadsheet

  

  A template to batch create antibodies can be obtained from the antibody list page

• Mouse over the button above the table (boxed in green in above picture) and click on the Export Items link.

• A pop-up window offers you to customize your export:

  • The first option Items (rows) lets you expand your selection to all matching rows. This is particularly handy when you want to export a lot of items that do not fit all in on page.

  • The second option Attributes of items (columns) lets you export All attributes of the items or only those Visible on display (i.e. on the list page). This option is particularly handy when you want to export few ordered columns e.g. item's name, code and location.

  • The third option Annotations of items (columns) lets you add item's annotations to the export. Here again, you may either let LabID select everything relevant to add (All with value) or restrict to those annotations visible in the list page.

  Export Options

  

• For now, keep the default options and click on Download at the bottom.

• Check the results.

Step 3. Batch edition of antibodies using an excel template

Similar to creating many items at once, you will frequently need to update many items at once. We already learned how to update a given property on many items using the option on the list page but what if you want to set different values on many different properties/options?

Here again, LabID offers an excel-based batch edition features that you will love. The feature is quite similar to the batch create but there are still a few tricks that you should better be aware of.

• Navigate to the Consumables menu and click the Antibody sub-menu.

• Set the ownership filter on Personal

• Select all items on display (but not more than 10 for performance issues).

• Mouse over the button above the table and click on the Download template for batch edit link.

• A pop-up window offers you to customize your export:

  • The first option Items (rows) lets you expand your selection to all matching rows. This is particularly handy when you want to export a lot of items that do not fit all in on page.

  • The second option Attributes of items (columns) lets you export the items' attributes (Yes option, the default) or skip them (very handy when you only wish to modify the name or some annotations).

  • The third option Annotations of items (columns) lets you add item's annotations to the export. Here again, you may either let LabID select everything relevant to add (the default) or customize the list.

• For now, keep the default options and click on Download template at the bottom.

• This downloads an excel template to your computer. Open it in excel (alternatively, install OpenOffice or use Numbers):

  • The spreadsheet is similar to the one used for batch item creation except it is not pre-filled with the selected items

  • The first column is the ID column holding the unique identifier of the existing items; and should not be touched.

  Excel Spreadsheet to batch modify existing antibodies

  

  Excel templates are specific to the item type, here an antibody. Whenever applicable, cells offer selected choices with options matching those available in the LabID web interface.

  Why the ID column should be left untouched?

  The unique identifier is the only way LabID can match a row in the excel spreadhseet to the corresponding item in the database. Only the ID is used for this mapping i.e. no other value is considered (indeed they could all have been changed). Removing or altering the ID will cause the update to fail.

• Modify/Add few values in the spreadsheet:

  • Change the Name for the first row: add modified to the existing name
  • Change the Isotype to IgE for the first 3 rows
  • Set the Clonality to Monoclonal for the all the rows

  • Set the Target Organisms of the first row to Fruit Fly˜˜˜Human˜˜˜Goat. Excel validation will most likely complain (click "Yes" or whatever so excel keeps your value).

  • Set the Origin/Vendor to NEB for the all the rows

  • Get rid of all empty columns except the 5 location-related columns (they are found as a group, one next to each other): Location Name, Container Type, Container Name, Container Position, Location Details
  • Also get rid of the Status, Maintained & Public columns (make sure to remove the whole columns i.e. do not simply erase the values)

• You should have something similar to the below picture

  Modified Excel Spreadsheet for existing antibodies

  

• Navigate to the Antibody list page if necessary and mouse over the button above the table (right to the you used to get the template) and click on the Batch edit with spreadsheet link.

• Drag'n'Drop the excel spreadsheet on the drop zone. Open the detail view of the item that was listed first in the spreadsheet and check that values, including the name, were changed according to the edits.

  Why could we remove the mandatory Status & Maintained columns

  Although flagged mandatory, in the edition context, the only mandatory column is the unique identifier and missing columns are simply ignored (value is left untouched)

We'll now check what happens if we remove some values from a column. We also learn about setting a location.

• Open the spreadsheet again (the one we just used to update the items; alternatively, you can export a fresh one) and:

  • remove the values found in the Clonality column (do not remove the column itself)
  • Adding location(s) means filling all the location-related columns:
    • Set the Location Name to Liebherr-30freezer for all antibodies
    • Set the Container Type to 384-well plate for all antibodies
    • Set the Container Name to Plate TraineeXX for all antibodies (where XX is your trainee number)
    • Set the Container Position to A1..A10 for all antibodies
    • Leave the Location Details empty (feel free to add text if you like)

• Drag'n'Drop the excel spreadsheet on the drop zone. Open the detail view of the item that was listed first in the spreadsheet and check its location.

  What happened to the value in the Clonality property?

  This is now empty. There is indeed a difference between removing a whole column (as we did before with th e.g. Status column) and removing values from a column (with the column still listed in the spreadsheet). In the first case, the property is ignored as the column is not present in the spreadsheet while in the second case, it is interpreted as a delete value. Of course, LabID will complain if a mandatory column would be present but without value.

Congrats! You now have completed this session on item collection management!